Count unique text values based on condition in another column

[himihai]

Hello,

I need to count uniques text values in a column that contains names.
But I only need to count the unique values that are satisfying a condition in another column.

Example:

<TABLE style="WIDTH: 166pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=222 border=0><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: black" width=103 height=20>Group</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=119>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>b</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>b</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>b</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>c</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 4</TD></TR></TBODY></TABLE>

So I need to count unique names from group "a"

I already got the formula for counting the unique values from the whole list, and I just need to add the condition that would restrict the search only to one group (condition).

Any ideas?

 

[huskerjhansen]

You need to lock the relevant ranges in the formula, i.e.

In C42 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$42:$B$140=$B42,IF($B$42:$B$140<>"",MATCH("~"&$A$42:$A$140,$A$42:$A$140&"",0))),ROW($A$42:$A$140)-ROW($A$42)+1),1))

Worked like a champ! This formula would be used each month and the number of rows will change each time. Is there something similar that would have the same effect as the $B$42:$B$140 but cover all of column B?

 

[Aladin Akyurek]

Worked like a champ! This formula would be used each month and the number of rows will change each time. Is there something similar that would have the same effect as the $B$42:$B$140 but cover all of column B?

Define Arange in Formulas | Name Manager as referring to:

=Sheet1!$A$42:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1$A:$A))

Define Brange in Formulas | Name Manager as referring to:

=Sheet1!$B$42:INDEX(Sheet1!$B:$B,MATCH(REPT("z",255),Sheet1$A:$A))

Adjust the sheet name ("Sheet1" above) to suit.

Now in C42 control+shift+enter and copy down:

=SUM(IF(FREQUENCY(IF(Brange=$B42,IF(Brange<>"",MATCH("~"&Arange,Arange&"",0))),ROW(Arange)-ROW(INDEX(Arange,1,1))+1),1))

 

[nnklem]

Try...

=SUM(IF(FREQUENCY(IF(A2:A100="a",IF(B2:B100<>"",MATCH("~"&B2:B100,B2:B100&"",0))),ROW(B2:B100)-ROW(B2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the ranges, accordingly.

This works great for me. However, my data set has 150,000 records so this runs very slowly. Is there a better, more efficient way to accomplish the same thing with a large data set?

 

[Aladin Akyurek]

This works great for me. However, my data set has 150,000 records so this runs very slowly. Is there a better, more efficient way to accomplish the same thing with a large data set?

Care to post the formula as it is implemented in your workbook?

 

[akshatku]

Care to post the formula as it is implemented in your workbook?

Hi Aladin, I need some urgent help. I need to find the number of unique agencies below for number 1, but I need to exclude the agency named "HUMA" from the results. In this case, the answer would be 1(ie. Agency SOAP). What's the formula to do so? Assume the column "Number" is column A and column "Agency" is column B.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Number[/TD]
[TD="class: xl64, width: 64"]Agency[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]SOAP[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]BALL[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]HUMA[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]NIJA[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]POIS[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]SOAP[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Hi Aladin, I need some urgent help. I need to find the number of unique agencies below for number 1, but I need to exclude the agency named "HUMA" from the results. In this case, the answer would be 1(ie. Agency SOAP). What's the formula to do so? Assume the column "Number" is column A and column "Agency" is column B.

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl64, width: 64"]Number[/TD]
[TD="class: xl64, width: 64"]Agency[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]SOAP[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]BALL[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]HUMA[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]NIJA[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]POIS[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]SOAP[/TD]
[/TR]
</tbody>[/TABLE]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$7=1,IF(1-($B$2:$B$7=""),IF(1-($B$2:$B$7="HUMA",MATCH($B$2:$B$7,$B$2:$B$7,0)))),ROW($B$2:$B$7)-ROW($B$2)+1),1))

 

[akshatku]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$7=1,IF(1-($B$2:$B$7=""),IF(1-($B$2:$B$7="HUMA",MATCH($B$2:$B$7,$B$2:$B$7,0)))),ROW($B$2:$B$7)-ROW($B$2)+1),1))

I tried using this but Excel keeps showing the following error:

"There's a problem with this formula. No trying to type a formula? When the first character is an equal (=) or minus(-) sign, Excel thinks it's a formula..."

Maybe I am not using Ctrl+Shift+Enter correctly. I copied and pasted the formula you sent and pressed "Ctrl+Shift+Enter". It still showed the same error.

Also, a couple more questions:

1. If there are 1000 values in both column and A and B, can I just use $A:$A and $B:$B everywhere in the formula instead of $A$2:$A$7 and $B$2:$B$7 above?
2. If I have to exclude "SOAP" as well including "HUMA", how would the formula change?

Thanks for all the help!
Akshat

 

[Aladin Akyurek]

I tried using this but Excel keeps showing the following error:

"There's a problem with this formula. No trying to type a formula? When the first character is an equal (=) or minus(-) sign, Excel thinks it's a formula..."

Maybe I am not using Ctrl+Shift+Enter correctly. I copied and pasted the formula you sent and pressed "Ctrl+Shift+Enter". It still showed the same error.

Also, a couple more questions:

1. If there are 1000 values in both column and A and B, can I just use $A:$A and $B:$B everywhere in the formula instead of $A$2:$A$7 and $B$2:$B$7 above?
2. If I have to exclude "SOAP" as well including "HUMA", how would the formula change?

Thanks for all the help!
Akshat

1. Define Number in the Name Manager as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))

Adjust the sheet name to suit.

2. Define Agency in the Name Manager as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$B:$B))

3. Define Ivec in the Name Manager as referring to:

=ROW(Agency)-ROW(INDEX(Agency,1,1))+1

4. Excluding HUMA...

Control+shift+enter, not just enter: (the previous formula re-written in terms of the foregoing definitions & coorected for a parenthesis error)

=SUM(IF(FREQUENCY(IF(Number=1,IF(1-(Agency=""),IF(1-(Agency="HUMA"),MATCH(Agency,Agency,0)))),Ivec),1))

5. If you want to exclude SOAP as well as HUMA... Again, control+shift+enter:

=SUM(IF(FREQUENCY(IF(Number=1,IF(1-(Agency=""),IF(ISNA(MATCH(Agency,{"HUMA";"SOAP},0)),MATCH(Agency,Agency,0)))),Ivec),1))

 

[akshatku]

1. Define Number in the Name Manager as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))

Adjust the sheet name to suit.

2. Define Agency in the Name Manager as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$B:$B))

3. Define Ivec in the Name Manager as referring to:

=ROW(Agency)-ROW(INDEX(Agency,1,1))+1

4. Excluding HUMA...

Control+shift+enter, not just enter: (the previous formula re-written in terms of the foregoing definitions & coorected for a parenthesis error)

=SUM(IF(FREQUENCY(IF(Number=1,IF(1-(Agency=""),IF(1-(Agency="HUMA"),MATCH(Agency,Agency,0)))),Ivec),1))

5. If you want to exclude SOAP as well as HUMA... Again, control+shift+enter:

=SUM(IF(FREQUENCY(IF(Number=1,IF(1-(Agency=""),IF(ISNA(MATCH(Agency,{"HUMA";"SOAP},0)),MATCH(Agency,Agency,0)))),Ivec),1))

The formula for Agency yields an error. Can you please let me know what's the purpose of Number, Agency and Ivec?

Thanks!
Akshat

 

[Aladin Akyurek]

The formula for Agency yields an error. Can you please let me know what's the purpose of Number, Agency and Ivec?

Thanks!
Akshat

The definition for Agency should be:

=Sheet1!$B$2:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))