counting multiple criteria

[Katrina]

Hello,

I have 3 columns with the potential of more being added. (A)-SalesRep
(B)-Acct#, (C)-Status
In column A, the same salesrep will be listed many times
In column B, the same acct# will be listed many times but always wit
the same rep
In column C, the same status, such as open, could be listed many time
for the same acct#.

In English, need to find the number of acct's under each salesrep tha
are open, without counting the duplicates.

Using info below, Jane would show an answer as 2. (ap2 and ap5 bot
have at least one open status)

I have been able to find the number of acct with Jane, and the numbe
of open with Jane, just can not get the right combination to combin
the 3 reasons.

A B c
Bill ap1 open
Jane ap2 open
Jane ap2 open
Jane ap2 closed
Jane ap5 open
George ap3
Bill ap4


Thanks a bunch!
Katrin

 

[Aladin Akyurek]

What follows is an attempt to invoke a formula similar to one by Harlan in
http://tinyurl.com/5eyf9...

Let A2:C8 house the data you provided.

=COUNT(1/FREQUENCY(IF(($A$2:$A$8=E2)*($C$2:$C$8=E3),MATCH($B$2:$B$8,$B$2:$B$8,0)),MATCH($B$2:$B$8,$B$2:$B$8,0)))

which you need to commit with control+shift+enter instead of just with
enter.

Another possibility is to create a unique sorted list of Acct# that looks
like:

ap1 1
ap2 2
ap3 3
ap4 4
ap5 5


If we refer to the foregoing 2-column list as Table, we could also use:

=COUNT(1/FREQUENCY(IF(($A$2:$A$8=E2)*($C$2:$C$8=E3),LOOKUP($B$2:$B$8,Table)),LOOKUP($B$2:$B$8,Table)))

Also to be committed with control+shift+enter.

Note that the formulas above are conditioned on the fact that your Acct# are
not numbers. If they were true numbers, you can just refer to the Acct#
range, B2:B8, instead of having a MATCH or LOOKUP expression.

 

[Aladin Akyurek]

BTW, E2 is supposed to house a name like Jane and E3 a status like Open.