Count if unique

G

Gareth.Evans

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
 
J

Jarek Kujawa

shouldn't the formula in your example return 1? as there are two 10's

if so try:

=SUMPRODUCT((COUNTIF($A$22:$A$29,$A$22:$A$29)=1)*($B$22:$B$29="yes"))

OR
=SUM(IF((COUNTIF($A$2:$A$9,$A$2:$A$9)=1)*($B$2:$B$9="yes"),1,))
(CTRL+SHIFT+ENTER this formula as it is an array-formula (instead of
simply entering it). If you insert it correctly curly braces { } will
show)

pls click YES if this post helped you
 
R

Ron Coderre

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)
 
G

Gareth.Evans

shouldn't the formula in your example return 1? as there are two 10's

if so try:

=SUMPRODUCT((COUNTIF($A$22:$A$29,$A$22:$A$29)=1)*($B$22:$B$29="yes"))

OR
=SUM(IF((COUNTIF($A$2:$A$9,$A$2:$A$9)=1)*($B$2:$B$9="yes"),1,))
(CTRL+SHIFT+ENTER this formula as it is an array-formula (instead of
simply entering it). If you insert it correctly curly braces { } will
show)

pls click YES if this post helped you












- Show quoted text -

No, it would return 2 because both invoice 10 and invoice 12 passed.
 
G

Gareth.Evans

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)















- Show quoted text -

Sorry Rin i do not understand what you mean for cell E2?
 
G

Gareth.Evans

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)















- Show quoted text -

Sorry ignore my last post, i am playing witht he formula now
 
G

Gareth.Evans

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)















- Show quoted text -

Ron:

i have a question regarding part of this equation you gave me.

=SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0)-
1+ROW(A2))=ROW(A2:A10))))

What is the 1+ROW(A2) bit about?
 
J

Jarek Kujawa

this array-entered formula

=SUM(IF((COUNTIF($A$2:$A$9,$A$2:$A$9)=1)*($B$22:$B$29="yes"),1,))+SUM
(IF(($B$2:$B$9="yes")*(COUNTIF($A$2:$A$9,$A$23:$A$29)>1),1,))/SUM(IF
(($B$2:$B$9="yes")*(COUNTIF($A$2:$A$9,$A$2:$A$29)>1),1,))

will bring required result unless you have blanks in your range
 
R

Ron Coderre

Hi, Gareth

Regarding the "-1+ROW(A2)" section of this formula:
=SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0)
-1+ROW(A2))=ROW(A2:A10))))

The MATCH function is returning a ordinal position in the list
1st, 2nd, etc.

We are comparing that value to the Row Numbers of the range.

Consequently, we need to either adjust the ordinal values
to align with the Row Numbers...OR...adjust the Row Numbers
to align with the ordinal values. For example, the MATCH
value of the 1st item is 1...but, the value resides on
Row_2. Since I chose to adjust the ordinal values:
1-1+ROW(A2) = 1-1+2 = 2...to match the row number: 2.

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)
 
T

Teethless mama

=SUM(N(FREQUENCY(IF(B1:B10="yes",MATCH(A1:A10&"",A1:A10&"",0)),MATCH(A1:A10&"",A1:A10&"",0))>0))

ctrl+shift+enter, not just enter
 
G

Gareth.Evans

=SUM(N(FREQUENCY(IF(B1:B10="yes",MATCH(A1:A10&"",A1:A10&"",0)),MATCH(A1:A10­&"",A1:A10&"",0))>0))

ctrl+shift+enter, not just enter














- Show quoted text -

Perfect! Thanks Teethless mama, your answer is simple and elegent, and
works like a charm. You made me day.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top