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 A1:D2 and A6:A32 (my
sales reps.) as criteria.

/Sune

"Bob Phillips" wrote:

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" wrote in message
...
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