Count unique text values based on condition in another column

H

himihai

New Member
Joined
Oct 8, 2009
Messages
5
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?
 
Sort by date Sort by votes
Aladin Akyurek said:
The definition for Agency should be:

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

So I am using this formula in Sheet 1 and in another tab named "Singapore" in the same workbook, the number is in column G and the Agency is in column M. Now if I use this formula for Agency in Sheet 1, I am using the following:

=Singapore!$M$2:INDEX(Singapore!$G:$G,MATCH(9.99999999999999E+307,Singapore!$G:$G))

It still shows #VALUE! as error. Am I doing something wrong?

Akshat
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
akshatku said:
So I am using this formula in Sheet 1 and in another tab named "Singapore" in the same workbook, the number is in column G and the Agency is in column M. Now if I use this formula for Agency in Sheet 1, I am using the following:

=Singapore!$M$2:INDEX(Singapore!$G:$G,MATCH(9.99999999999999E+307,Singapore!$G:$G))

It still shows #VALUE! as error. Am I doing something wrong?

Akshat
Click to expand...

akshatku said:
Also, how would these formulae change, if instead of number 1, I wanted to get results for number 2? Thank you for all the help!
Click to expand...

You must follow the instructions literally...

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

=Singapore!$G$2:INDEX(Singapore!$G:$G,MATCH(9.99999999999999E+307,Singapore!$G:$G))

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

=Singapore!$M$2:INDEX(Sheet1!$M:$M,MATCH(9.99999999999999E+307,Singapore!$G:$G))

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

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

The foregoing must take place in Formula > Name Manager, not in a sheet.

4. Let's say that Sheet1 is the sheet where you want to implement the unique count formulas...

In A2 of Sheet1 enter: 1

Excluding HUMA...

In B2 of Sheet1 control+shift+enter, not just enter:

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

5. If you want to exclude SOAP as well as HUMA...

In C2 of Sheet1 control+shift+enter:

=SUM(IF(FREQUENCY(IF(Number=$A2,IF(1-(Agency=""),IF(ISNA(MATCH(Agency,{"HUMA";"SOAP},0)),MATCH(Agency,Agency,0)))),Ivec),1))
 
Upvote 0
Aladin Akyurek said:
You must follow the instructions literally...

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

=Singapore!$G$2:INDEX(Singapore!$G:$G,MATCH(9.99999999999999E+307,Singapore!$G:$G))

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

=Singapore!$M$2:INDEX(Sheet1!$M:$M,MATCH(9.99999999999999E+307,Singapore!$G:$G))

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

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

The foregoing must take place in Formula > Name Manager, not in a sheet.

4. Let's say that Sheet1 is the sheet where you want to implement the unique count formulas...

In A2 of Sheet1 enter: 1

Excluding HUMA...

In B2 of Sheet1 control+shift+enter, not just enter:

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

5. If you want to exclude SOAP as well as HUMA...

In C2 of Sheet1 control+shift+enter:

=SUM(IF(FREQUENCY(IF(Number=$A2,IF(1-(Agency=""),IF(ISNA(MATCH(Agency,{"HUMA";"SOAP},0)),MATCH(Agency,Agency,0)))),Ivec),1))
Click to expand...

YES GOT IT!!! FINALLY! Just one more question: How would the formula change if instead of number 1, I wanted to get the results for number 2?
 
Upvote 0
Aladin Akyurek said:
You must follow the instructions literally...

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

=Singapore!$G$2:INDEX(Singapore!$G:$G,MATCH(9.99999999999999E+307,Singapore!$G:$G))

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

=Singapore!$M$2:INDEX(Sheet1!$M:$M,MATCH(9.99999999999999E+307,Singapore!$G:$G))

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

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

The foregoing must take place in Formula > Name Manager, not in a sheet.

4. Let's say that Sheet1 is the sheet where you want to implement the unique count formulas...

In A2 of Sheet1 enter: 1

Excluding HUMA...

In B2 of Sheet1 control+shift+enter, not just enter:

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

5. If you want to exclude SOAP as well as HUMA...

In C2 of Sheet1 control+shift+enter:

=SUM(IF(FREQUENCY(IF(Number=$A2,IF(1-(Agency=""),IF(ISNA(MATCH(Agency,{"HUMA";"SOAP},0)),MATCH(Agency,Agency,0)))),Ivec),1))
Click to expand...

One last question:

If for the following table, I have to count only the participating agencies (i.e for which Participate=Y) which are UNIQUE and exclude HUMA and SOAP, what formula should be used? Thank you so much!!!

[TABLE="width: 134"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Agency[/TD]
[TD]Participate[/TD]
[/TR]
[TR]
[TD]HUMA[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]SOAP[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]JUNG[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]POID[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]HIKA[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]HUMA[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]HAJI[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]REBC[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]HJUI[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]SOAP[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
akshatku said:
One last question:

If for the following table, I have to count only the participating agencies (i.e for which Participate=Y) which are UNIQUE and exclude HUMA and SOAP, what formula should be used? Thank you so much!!!

[TABLE="width: 134"]
<tbody>[TR]
[TD]Agency[/TD]
[TD]Participate[/TD]
[/TR]
[TR]
[TD]HUMA[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]SOAP[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]JUNG[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]POID[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]HIKA[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]HUMA[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]HAJI[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]REBC[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]HJUI[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]SOAP[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Define Participation in the Name Manager the same way as we did with Number and Agency.

Control+shift+enter...

=SUM(IF(FREQUENCY(IF(1-(Agency=""),IF(ISNA(MATCH(Agency,{"HUMA";"SOAP},0)),IF(Participation="Y",MATCH(Agency,Agency,0)))),Ivec),1))
 
Upvote 0
Aladin Akyurek said:
Define Participation in the Name Manager the same way as we did with Number and Agency.

Control+shift+enter...

=SUM(IF(FREQUENCY(IF(1-(Agency=""),IF(ISNA(MATCH(Agency,{"HUMA";"SOAP},0)),IF(Participation="Y",MATCH(Agency,Agency,0)))),Ivec),1))
Click to expand...

But this question isn't dependent on column G. Should we still incorporate column G in the formulae?
 
Upvote 0
akshatku said:
But this question isn't dependent on column G. Should we still incorporate column G in the formulae?
Click to expand...

What do you mean by "But"? You should by now talk in terms of the range we have defined, not in terms of the columns. The last formula does not test the Number range as it was not a reqyuirement in your last request.
 
Upvote 0
Aladin Akyurek said:
Define Participation in the Name Manager the same way as we did with Number and Agency.

Control+shift+enter...

=SUM(IF(FREQUENCY(IF(1-(Agency=""),IF(ISNA(MATCH(Agency,{"HUMA";"SOAP},0)),IF(Participation="Y",MATCH(Agency,Agency,0)))),Ivec),1))
Click to expand...

I defined 3 more things in Name Manager as follows(AC is the column containing Participate=Y/N in Singapore tab)

Participationsp=Singapore!$AC$2:INDEX(Singapore!$AC:$AC,MATCH(9.99999999999999E+307,Singapore!$AC:$AC))

Agencysp=Singapore!$M$2:INDEX(Singapore!$M:$M,MATCH(9.99999999999999E+307,Singapore!$AC:$AC))

Ivecsp=ROW(Agencysp)-ROW(INDEX(Agencysp,1,1))+1

Then used the following formula to calculate count:

=SUM(IF(FREQUENCY(IF(1-(Agencysp=""),IF(ISNA(MATCH(Agencysp,{"HUMA";"SOAP"},0)),IF(Participationsp="Y",MATCH(Agencysp,Agencysp,0)))),Ivecsp),1))

Got an error through this, any idea what I am doing wrong?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
574
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
538
mumps
mumps
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
92
Fluff
Fluff

Forum statistics

Threads
1,223,956
Messages
6,175,614
Members
452,661
Latest member
Nonhle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top