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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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!!!

AgencyParticipate
HUMAY
SOAPN
JUNGY
POIDY
HIKAY
HUMAN
HAJIY
REBCN
HJUIY
SOAPY

<colgroup><col><col></colgroup><tbody>
</tbody>
 
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!!!

AgencyParticipate
HUMAY
SOAPN
JUNGY
POIDY
HIKAY
HUMAN
HAJIY
REBCN
HJUIY
SOAPY

<tbody>
</tbody>
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

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
839
gvsumich
G
R
  • Question
Macro not working after adding two new items
Replies
4
Views
120
myall_blues
myall_blues
E
  • Question
VBA for Web Scraping help
Replies
0
Views
594
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,215,093
Messages
6,123,067
Members
449,090
Latest member
fragment

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