Re: Count distinct based on criteria
- From: Sune Fibaek <SuneFibaek@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 28 Apr 2007 04:58:00 -0700
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
"Bob Phillips" wrote:
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" <SuneFibaek@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DC34A883-5908-4C5C-A22C-934E731F2F74@xxxxxxxxxxxxxxxx
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" <SuneFibaek@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B81FA1FC-FD28-46FA-B60B-A3E5E974BCFC@xxxxxxxxxxxxxxxx
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
- Follow-Ups:
- Re: Count distinct based on criteria
- From: Sune Fibaek
- Re: Count distinct based on criteria
- References:
- Re: Count distinct based on criteria
- From: Bob Phillips
- Re: Count distinct based on criteria
- From: Bob Phillips
- Re: Count distinct based on criteria
- Prev by Date: Need formula to lookup data in row
- Next by Date: Re: Finding Named Field in Formulas
- Previous by thread: Re: Count distinct based on criteria
- Next by thread: Re: Count distinct based on criteria
- Index(es):
Relevant Pages
|
