Thread: Count if unique
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gareth.Evans Gareth.Evans is offline
external usenet poster
 
Posts: 15
Default 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?