Thread: Count distinct using multiple criteria

Hello,

I am trying to count unique accounts in a worksheet based on multiple criteria.

ACCT ORDER APPROVED INVOICED AMT
acct a order 1 1 1 100
acct a order 2 1 1 200
acct a order 3 1 1 300
acct a order 4 1 0 400
acct b order 1 1 1 100
acct b order 2 1 1 200
acct b order 3 1 1 300
acct b order 4 1 0 400
acct c order 1 0 1 100
acct c order 2 1 1 10
acct c order 3 0 1 300
acct c order 4 1 0 400


The criteria are: APPROVED must equal 1, INVOICED must equal 1, and AMT must be greater than 25. If those conditions are met, I would like the account to be counted, but only once (i.e. not each time the ORDER meets that criteria). In this example, I should end up with 2.

Can someone help me with this formula? I think it involves nesting IF statements within an array, but I haven't been able to get it.

Thanks in advance for the assistance!

Your profile states 2003 yet this is posted in 2007 forum.

You can perhaps use something like:

Code:

=SUMPRODUCT(--(C2:C13=1),--(D2:D13=1),--(E2:E13>25),--(MATCH(A2:A13&C2:C13&D2:D13&--(E2:E13>25),A2:A13&C2:C13&D2:D13&--(E2:E13>25),0)=ROW(A2:A13)-ROW(A2)+1))