Counting Unique Values If Value in Another Column is True

[DanielThal]

I'm trying to find the sum of unique values in column A but only if the corresponding value in column C is 'true'.

Right now I can find the number of unique values in column A but can't seem to figure out how to make it only count certain cells.

I have:

=SUMPRODUCT((A4:A9999<>"")/COUNTIF(A4:A9999,A4:A9999&""))

which gives me the number of unique values in column A.

I have names in column C, let's say one is 'Bob' and ID codes in column A, some of which could be '15-12345' and '15-12346', etc (with duplicates)

How can I find the number of unique values in column A for when the name in column C is 'Bob'?

Can I just add SUMIF to the equation somehow?

Thank you for your help, I really appreciate it.

 

[jarjarbingie]

=SUMPRODUCT(--(A4:A9<>""),--($C$4:$C$9="Bob")/COUNTIF(A4:A9,A4:A9&""))

I'm trying to find the sum of unique values in column A but only if the corresponding value in column C is 'true'.

Right now I can find the number of unique values in column A but can't seem to figure out how to make it only count certain cells.

I have:

=SUMPRODUCT((A4:A9999<>"")/COUNTIF(A4:A9999,A4:A9999&""))

which gives me the number of unique values in column A.

I have names in column C, let's say one is 'Bob' and ID codes in column A, some of which could be '15-12345' and '15-12346', etc (with duplicates)

How can I find the number of unique values in column A for when the name in column C is 'Bob'?

Can I just add SUMIF to the equation somehow?

Thank you for your help, I really appreciate it.

 

[Aladin Akyurek]

I'm trying to find the sum of unique values in column A but only if the corresponding value in column C is 'true'.

Right now I can find the number of unique values in column A but can't seem to figure out how to make it only count certain cells.

I have:

=SUMPRODUCT((A4:A9999<>"")/COUNTIF(A4:A9999,A4:A9999&""))

which gives me the number of unique values in column A.

I have names in column C, let's say one is 'Bob' and ID codes in column A, some of which could be '15-12345' and '15-12346', etc (with duplicates)

How can I find the number of unique values in column A for when the name in column C is 'Bob'?

Can I just add SUMIF to the equation somehow?

Thank you for your help, I really appreciate it.

1) Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A4:A9999<>"",IF(C4:C9999,MATCH(A4:A9999,A4:A9999,0))),ROW(A4:A9999)-ROW(A4)+1),1))

2) Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A4:A9999<>"",IF(C4:C9999="Bob",MATCH(A4:A9999,A4:A9999,0))),ROW(A4:A9999)-ROW(A4)+1),1))

http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

 

[bosco_yip]

=SUMPRODUCT(--(A4:A9<>""),--($C$4:$C$9="Bob")/COUNTIF(A4:A9,A4:A9&""))

If you testing, this formula will give wrong result,

Try this :

=SUMPRODUCT((A4:A9<>"")*(C4:C9="Bob")/COUNTIFS(C4:C9,C4:C9,A4:A9,A4:A9&""))

 

[jarjarbingie]

actually, this evaluates to #DIV/0!..

@daniel, use 2nd formula by Aladin, tried it and it worked..

If you testing, this formula will give wrong result,

Try this :

=SUMPRODUCT((A4:A9<>"")*(C4:C9="Bob")/COUNTIFS(C4:C9,C4:C9,A4:A9,A4:A9&""))

 

[bosco_yip]

actually, this evaluates to #DIV/0!..

My mistake, missing one of the &"" :

=SUMPRODUCT((A4:A9<>"")*(C4:C9="Bob")/COUNTIFS(C4:C9,C4:C9&"",A4:A9,A4:A9&""))

 

[DanielThal]

Thank you all so much, I really appreciate it!