Count if unique

[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

 

[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

 

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

 

[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.

 

[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?

 

[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

 

[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?

 

[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

 

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

 

[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

 

[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.

 

[Bernd P]

Hello,

Another solution which requires VBA, though:
=SUMPRODUCT(--(INDEX(Pfreq(A1:A8,C1:C8),,2)="yes"))

Pfreq you can get here:
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd

 

[Teethless mama]

You're Welcome!

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

 

[Jarek Kujawa]

forget this one
I might have thought of sth. better than that
;-)