Count unique data that meets certain condition

[tekobayashi]

For example, I have two column of data:

Name Code
ABC 10
DEF 9
DEF 9
ABC 10
ABC 9
MNO 10
ABC 11

I want to count the number of Names when Code is 10. In the above example, it should return 2. (ABC an MNO)

I am using =SUMPRODUCT((A1:A7<>"")/COUNTIF(A1:A7,A1:A7&"")) to count the number of unique data, which would return a 3 in the above example. However, it does not take into account of the Code value.

I'd sincerely appreciate your help!

[Fotis1991]

Hi

One way, as per your example.

In C1 and copy down.

=SUMPRODUCT((A1:$A$7<>"")*($B1:$B$7=10))

Then use.

=COUNTIF(C1:C7,1)

[Kevin UK]

Got me stumped on this one.

So the value you are after is:

ABC 10
MNO 10
= 2

Or is it me!

[Fotis1991]

Kewin, as OP is gone, i believe YES. This is what is he(she) looking for.

I want to count the number of Names when Code is 10. In the above example, it should return 2. (ABC an MNO)

I am using =SUMPRODUCT((A1:A7<>"")/COUNTIF(A1:A7,A1:A7&"")) to count the number of unique data, which would return a 3 in the above example. However, it does not take into account of the Code value.

[NBVC]

How about?

=SUMPRODUCT((A1:A7<>"")*(B1:B7=10)/COUNTIFS(B1:B7,B1:B7&"",A1:A7,A1:A7&""))

[tekobayashi]

How about?

=SUMPRODUCT((A1:A7<>"")*(B1:B7=10)/COUNTIFS(B1:B7,B1:B7&"",A1:A7,A1:A7&""))

This worked like magic! To be frank I am also having difficulty understanding why &"" works (even though I'm using it), but the post below explained it well. Thank you so much!

[Fotis1991]

=SUMPRODUCT((A1:A7<>"")*(B1:B7=10)/COUNTIFS(B1:B7,B1:B7&"",A1:A7,A1:A7&""))

If you have time, can you explain how the red part of the formula, works?

[NBVC]

If you take those out, and the data in the respective columns contain blanks, you will get errors... this prevents the error.

[Fotis1991]

Thanks teacher!

[tekobayashi]

Thanks everyone!

=SUMPRODUCT((A1:A7<>"")*(B1:B7=10)/COUNTIFS(B1:B7,B1:B7&"",A1:A7,A1:A7&""))

This worked well for me. I was also having difficulty understanding why &"" works (despite using it), but NBVC explained it clear and simple. I am still learning about how to use SUMPRODUCT properly, so thanks!

[tekobayashi]

i know i marked this as SOLVED, but i was wondering if anyone can help me with this topic again...

the suggested formula worked when i only have 2 conditions, but how about if i have more?

=SUMPRODUCT((A1:A7<>"")*(B1:B7=10)/COUNTIFS(B1:B7,B1:B7&"",A1:A7,A1:A7&""))

I want to add 2 more conditions, such as C1:C7 <= E1 and C1:C7 >= E2, where C1:C7 have date entries and E1 and E2 are dates.

Name Code Dates
ABC 10 2012/11/3
DEF 9 2012/11/4
DEF 9 2012/11/5
ABC 10 2012/11/5
ABC 9 2012/11/6
MNO 10 2012/11/6
ABC 11 2012/11/9

for instance, if E1 is 2012/11/5 and E2 is 2012/11/6, i want to count the number of unique NAMES for a given CODE between those 2 dates.

[icestationzbra]

update to NBVC's formula:

=SUMPRODUCT((A1:A7<>"")*(B1:B7=10)*(C1:C7>=E1)*(C1:C7<=E2)/COUNTIFS(A1:A7,A1:A7&"",B1:B7,B1:B7&"",C1:C7,C1:C7&""))

other options for regular formulae:

=COUNT(INDEX(1/((A1:A7<>"")*(B1:B7=10)*(C1:C7>=E1)*(C1:C7<=E2)),0))

and for array formulae:

=SUM(IF(FREQUENCY(IF((A1:A7<>"")*(B1:B7=10)*(C1:C7>=E1)*(C1:C7<=E2),ROW(A1:A7)-MIN(ROW(A1:A7))+1),ROW(A1:A7)-MIN(ROW(A1:A7))+1),1))

[Fotis1991]

As from the beginning, I did, my suggestion is using a helper and hidden column.

In D1 and copy down.

=SUMPRODUCT(($A$1:A1<>"")*($A$1:A1=A1)*($B$1:B1=10)*($C$1:C1>=$E$1)*($C$1:C1<=$E$2))

Then

=SUMPRODUCT((A1:A7<>"")*(B1:B7=10)*(D1:D7=1))

does the job.