Count if unique
On Apr 21, 1:57*pm, "Ron Coderre" wrote:
With your posted data in A2:C10
and
E2: (the invoice status to find...eg Yes)
This regular formula returns the count of unique invoices numbers
with a status of E2 (yes)
E1: =SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0)-
1+ROW(A2))=ROW(A2:A10))))
Using your data, the formula returns: 2
Is that something you can work with?
Regards,
Ron Coderre
Microsoft MVP (Excel)
"Gareth.Evans" wrote in message
...
Hi all, thank you in advance for your help ,and help in past.
I am trying to count the number of "yes" in column C if Column A has a
unique value, see below:
| * * * * A * * * * * | * * * * * B * * * * | * * * * C * * * *|
* * * * *10 * * * * * * * * * *N/A * * * * * * * * yes
* * * * *10 * * * * * * * * * *N/A * * * * * * * * yes
* * * * *11 * * * * * * * * * *N/A * * * * * * * * no
* * * * *11 * * * * * * * * * *N/A * * * * * * * * no
* * * * *12 * * * * * * * * * *N/A * * * * * * * * yes
* * * * *13 * * * * * * * * * *N/A * * * * * * * * no
* * * * *13 * * * * * * * * * *N/A * * * * * * * * no
* * * * *13 * * * * * * * * * *N/A * * * * * * * * no
Basically this is part of verifying invoices, A being the invoice
number, each invoice can have mnore then 1 entry thus there being two
10's.
Column C represtent weather that invoice entry has been a success or
not.
What i need to know is the number of invoices that were a success
(Column c)
So.... in the example above the formula would return 2, as invoice 10
and 12 were a success but not he others.
I hope i have made sense, i have been trying to figure this out for a
few days noe, i have got as far as working out the number of unique
entriers in column a by useing array formulas, but get stuck.
Hope someone can help
Many Thanks- Hide quoted text -
- Show quoted text -
Sorry Rin i do not understand what you mean for cell E2?
|