Count distinct based on criteria

[Sune Fibaek]

Hi,

I have 30,000+ rows of order lines (extract from database). I have a report
setup that takes four criteria (retailer chain, order type, product group,
and brand) based on validation in B1:B4 from this I use an array formula with
SUMPRODUCT and four IF's (used since the criteria fields may be emty to allow
for alle variations of one or more of the criteria) to calculate a COGS,
GCBM, etc. for each of 26 sales reps. The criteria in B1:B4 are used much
like the page fields in a pivot table. In addition to this I would very much
like to have a count of distinct orders based on the four criteria. One order
can obviously have more than one orderline, so I can't just do a simple
count. How would I go about counting every unique order no. based on whatever
criteria are used?

Any thoughts? Does this even make sence to anyone but my good self? Thanks
you!

/Sune

 

[Bob Phillips]

Why don't you use a pivot table, it seems ideal for you purposes?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sune Fibaek]

Hi Bob,

Thank you for your suggestion.

A pivot table is almost what I need, but then not. I had a pivot table set
up with my DB as external source. But as I need to have information such as
number of retailers visited (given the four criteria) and number of visits
made to retailers (againg the infamous four), I got stuck on the database
design to cope with this (I'm not saying it can't be done - I'm saying I
can't crack it). I thought about using a pivot table for datasource through
GETPIVOTDATA but again I got stuck on using my criteria as arguments to that
function as the criteria may or may no be blank.

So now I'm looking at DCOUNT with a transposed A1:B4 after a bit of
googleing. I almost got it, but I won't accept both A12 and A6:A32 (my
sales reps.) as criteria.

/Sune

 

[Bob Phillips]

Okay, pursuing this. If there are four criteria, why A1:B4 (that makes 8 in
my book)? And where does the A6:A32 sales reps figure into it?

Also, are the criteria on the same field or different? For instance, are you
looking to count where Region = X AND Month = y say, or Region = X or Y?

What does the database column layout look like?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sune Fibaek]

A1:A4 is used for headers, criteria values are in B1:B4. These four are
optional - either a value is selected or the criteria is blank. The layout in
the report is sales reps. in rows and values in columns - sales reps. are
fixed. I.e. they cannot be toggeled. Rough draft.

1 A B C D
....
6 Rep1. Visits Retailers COGS etc.
7 Rep2. etc.

For the criteria each is optional, but those selected are included in the
calculation as AND. The formula is this rather long array formula (this one
is for the sales rep in A6):

{=SUMPRODUCT((Ordre!$U$2:$U$31754)*(Rapport!$A6=Ordre!$N$2:$N$31754)*(IF($B$1="";1;Rapport!$B$1=Ordre!$B$2:$B$31754))*(IF($B$2="";1;Rapport!$B$2=Ordre!$E$2:$E$31754))*(IF($B$3="";1;Rapport!$B$3=Ordre!$Q$2:$Q$31754))*(IF($B$4="";1;Rapport!$B$4=Ordre!$S$2:$S$31754)))}

I'm no sure what you mean by columns layout? There is one row for each
orderline. For each line I have order no., order line no., product no.,
...more columns.., product group no., product group text, brand no., brand
text, sales rep no., sales rep name, COGS, sales price, GCB, GCBM and a few
more.

I could take out the line no., and group them by product group and brand and
sum COGS etc. on database level, but that wouldn't help much as far as I can
see (except it would be rather more efficient, but that is not my main
concern at the moment).

Thank you for helping me out here!

/Sune

 

[Sune Fibaek]

Solved it! It is a rather ugly and highly inefficient hack, but it works and
it will have to do for now.

I did a few (26 to be exact) named ranges containing my criteria incl. the
rep_id and a header-row for each range. The ranges are now used as input for
DCOUNT() in the report.

Still if someone comes up with a nicer solution I'm all ears

Bob, thank you very much for helping me out today!

/Sune

 

[Bob Phillips]

Sorry I wasn't around to help. I had to go out before you responded, and you
cracked it before I got back.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)