Count Unique Values with Specific Criteria

[jfish1288]

I have data in the following type

<table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:48pt" height="17" width="64">A</td> <td class="xl24" style="width:48pt" width="64">Criteria</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">B</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">A</td> <td class="xl24">Criteria</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">C</td> <td class="xl24">Criteria</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">A</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">C</td> <td class="xl24">Criteria</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">C</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">D</td> <td class="xl24">Criteria</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">2</td> <td class="xl24">Criteria</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">Criteria</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">2</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Criteria</td> </tr> </tbody></table>

I need a formula that will count the number of unique values in column A that have "Criteria" in column B. In this case the desired output would be 5 (A, C, D, 2, and 3), a blank cell in column A does not need to be counted.

 

[T. Valko]

I have data in the following type

<TABLE cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64></COLGROUP><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="WIDTH: 48pt; HEIGHT: 12.75pt" width=64 height=17>A</TD><TD class=xl24 style="WIDTH: 48pt" width=64>Criteria</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>B</TD><TD class=xl24>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>A</TD><TD class=xl24>Criteria</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>C</TD><TD class=xl24>Criteria</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>A</TD><TD class=xl24>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>C</TD><TD class=xl24>Criteria</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>C</TD><TD class=xl24>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>D</TD><TD class=xl24>Criteria</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>1</TD><TD class=xl24>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>2</TD><TD class=xl24>Criteria</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>3</TD><TD class=xl24>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl24>Criteria</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>2</TD><TD class=xl24>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="HEIGHT: 12.75pt" height=17>3</TD><TD class=xl24>Criteria</TD></TR></TBODY></TABLE>

I need a formula that will count the number of unique values in column A that have "Criteria" in column B. In this case the desired output would be 5 (A, C, D, 2, and 3), a blank cell in column A does not need to be counted.

Try this...

Book1
	A	B	C
2	A	Criteria	5
3	B	_	_
4	A	Criteria	_
5	C	Criteria	_
6	A	_	_
7	C	Criteria	_
8	C	_	_
9	D	Criteria	_
10	1	_	_
11	2	Criteria	_
12	3	_	_
13	_	Criteria	_
14	2	_	_
15	3	Criteria	_
Sheet1

This array formula** entered in C2:

=SUM(IF(FREQUENCY(IF(B2:B15="criteria",IF(A2:A15<>"",MATCH(A2:A15,A2:A15,0))),ROW(A2:A15)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[jfish1288]

like a charm. thanks

 

[T. Valko]

like a charm. thanks

You're welcome. Thanks for the feedback!

 

[jfish1288]

I can not completely wrap my head around the logic of the formula, what would you need to do to add a second criteria, say "Criteria2" in column c? So that it would count unique values in column A that have "Criteria" column B AND "Criteria2" in column C

 

[jfish1288]

I suppose that might be asking a little much and I could probably get away with adding a new column checking if both criteria are met. Then I could do a count based on the new column

 

[Aladin Akyurek]

I can not completely wrap my head around the logic of the formula, what would you need to do to add a second criteria, say "Criteria2" in column c? So that it would count unique values in column A that have "Criteria" column B AND "Criteria2" in column C

G2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A15<>"",IF(B2:B15=E2,IF(C2:C15=F2,
  MATCH("~"&A2:A15,A2:A15&"",0)))),ROW(A2:A15)-ROW(A2)+1),1))

If there are no special meaning chars around the entries in the A range...

=SUM(IF(FREQUENCY(IF(A2:A15<>"",IF(B2:B15=E2,IF(C2:C15=F2,
  MATCH(A2:A15,A2:A15,0)))),ROW(A2:A15)-ROW(A2)+1),1))

E2 and F2 house the conditions of interest regarding the ranges in column B and in column C.

 

[T. Valko]

I can not completely wrap my head around the logic of the formula, what would you need to do to add a second criteria, say "Criteria2" in column c? So that it would count unique values in column A that have "Criteria" column B AND "Criteria2" in column C

I suppose that might be asking a little much and I could probably get away with adding a new column checking if both criteria are met. Then I could do a count based on the new column

Just add another nested IF:

=SUM(IF(FREQUENCY(IF(B2:B15="criteria",IF(C2:C15="criteria2",IF(A2:A15<>"",MATCH(A2:A15,A2:A15,0)))),ROW(A2:A15)-ROW(A2)+1),1))

Don't forget to array enter!

 

[jfish1288]

Both suggestions work great. Thanks guys.

 

[T. Valko]

Both suggestions work great. Thanks guys.

You're welcome!