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
When I typed in the formula I get "#NAME?" in the cell. I did not use the workbook defined names in my equation. Am I suppose to?
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Aladin Akyurek said:
@CycleGirl

Is the expected result for Nancy's Sewing Shop 2?
Click to expand...

CycleGirl said:
Yes
Click to expand...

Row\Col
A​
B​
C​
D​
E​
1​
Vendor Item IdManufacturer NameManufacturer NameUnique Ciunt
2​
335​
NestleNestle
3​
3​
41​
MonsantoMonsanto
2​
4​
B75453Texas InstrumentsTexas Instruments
2​
5​
2345​
Shell IncShell Inc
1​
6​
87643​
Nancy's Sewing ShopNancy's Sewing Shop
2​
7​
689043​
Tree Farm NurseryTree Farm Nursery
3​
8​
M5678Nestle
9​
41​
Monsanto
10​
87643​
Nancy's Sewing Shop
11​
689043​
Tree Farm Nursery
12​
N983762Nancy's Sewing Shop
13​
v987473Tree Farm Nursery
14​
34982​
Nestle
15​
598734​
Monsanto
16​
9087543​
Texas Instruments
17​
N983762Nancy's Sewing Shop
18​
v765498Tree Farm Nursery

E2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=SUM(IF(FREQUENCY(IF($A$2:$A$18<>"",
    IF($B$2:$B$18=D2,MATCH("~"&$A$2:$A$18,$A$2:$A$18&"",0))),
    ROW($A$2:$A$18)-ROW($A$2)+1),1))

See the workbook which implements this conditional unique count formula:
https://dl.dropboxusercontent.com/u...ues based on condition in another column.xlsx
 
Upvote 0
Aladin Akyurek said:
Row\Col
A​
B​
C​
D​
E​
1​
Vendor Item IdManufacturer NameManufacturer NameUnique Ciunt
2​
335​
NestleNestle
3​
3​
41​
MonsantoMonsanto
2​
4​
B75453Texas InstrumentsTexas Instruments
2​
5​
2345​
Shell IncShell Inc
1​
6​
87643​
Nancy's Sewing ShopNancy's Sewing Shop
2​
7​
689043​
Tree Farm NurseryTree Farm Nursery
3​
8​
M5678Nestle
9​
41​
Monsanto
10​
87643​
Nancy's Sewing Shop
11​
689043​
Tree Farm Nursery
12​
N983762Nancy's Sewing Shop
13​
v987473Tree Farm Nursery
14​
34982​
Nestle
15​
598734​
Monsanto
16​
9087543​
Texas Instruments
17​
N983762Nancy's Sewing Shop
18​
v765498Tree Farm Nursery

<tbody>
</tbody>


E2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=SUM(IF(FREQUENCY(IF($A$2:$A$18<>"",
    IF($B$2:$B$18=D2,MATCH("~"&$A$2:$A$18,$A$2:$A$18&"",0))),
    ROW($A$2:$A$18)-ROW($A$2)+1),1))

See the workbook which implements this conditional unique count formula:
https://dl.dropboxusercontent.com/u...ues based on condition in another column.xlsx
Click to expand...

This has given me a return of #N/A. I cannot get to the dropbox contents with my current connection. I will need to try to get it with different connection in a couple of hours. I will let you know if I can get to it. Thank you for your help.
 
Upvote 0
Aladin Akyurek said:
@CycleGirl

Is the expected result for Nancy's Sewing Shop 2?
Click to expand...

CycleGirl said:
This has given me a return of #N/A. I cannot get to the dropbox contents with my current connection. I will need to try to get it with different connection in a couple of hours. I will let you know if I can get to it. Thank you for your help.
Click to expand...

Perhaps one point... Control+shift+enter means: Press down the control and the shift keys at the same time, while you hit the enter key.
 
Upvote 0
@Aladin!
Your formula is very similar to my formula (I learned from you) post #19, but I don't understand this sign "~" and this &"", see belowe the formula and also in my Italian KeyBoard I don't have thi charater "~" however you call it
can you help me!


=SUM(IF(FREQUENCY(IF($A$2:$A$18<>"",
IF($B$2:$B$18=D2,MATCH("~"&$A$2:$A$18,$A$2:$A$18&"",0))),
ROW($A$2:$A$18)-ROW($A$2)+1),1))
 
Upvote 0
himihai said:
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?
Click to expand...

I have a similar problem, but a little different. I have to count the number of times a value appears in a column, if the value in another column is unique. Any suggestions?
 
Upvote 0
I have a similar problem, but a little different. I have to count the number of times a value appears in a column, but only once for every time that the value in another column appears. E.g. I have order numbers in one column, but they are used every time an item is ordered against that number, therefore a single project could have the same order number appear multiple times. In another column I have the type of work. I need to count the number of times a type of work appears in the data, but do not want to double-count it if the same order number is used again.
 
Upvote 0
Natashavo said:
I have a similar problem, but a little different. I have to count the number of times a value appears in a column, if the value in another column is unique. Any suggestions?
Click to expand...

Natashavo said:
I have a similar problem, but a little different. I have to count the number of times a value appears in a column, but only once for every time that the value in another column appears. E.g. I have order numbers in one column, but they are used every time an item is ordered against that number, therefore a single project could have the same order number appear multiple times. In another column I have the type of work. I need to count the number of times a type of work appears in the data, but do not want to double-count it if the same order number is used again.
Click to expand...

Row\Col
A​
B​
C​
D​
2​
2​
xx
3​
2​
y
2​
4​
3​
x
5​
2​
x
6​
3​
w

In D2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(A2:A6=""),IF(1-(B2:B6=""),IF(B2:B6="x",MATCH(A2:A6&"|"&B2:B6,A2:A6&"|"&B2:B6,0)))),ROW(A2:A6)-ROW(A2)+1),1))

is this what you are after?
 
Upvote 0
I also have a similar issue but am running into an issue with the way array 'sorts' the data.

I'm using the formula "=SUM(IF(FREQUENCY(IF(B42:B140=B42,IF(B42:B140<>"",MATCH("~"&A42:A140,A42:A140&"",0))),ROW(A42:A140)-ROW(A42)+1),1))" and it is working well but I notice when it hits a row with multiple values for my given criteria, it only counts down and thus only the first iteration is correct.

For Example:

BAB7808605
BAB7808605
BAB7808605
BAB7808605
BAB7808605
BAB7808605
HGE7808604
HGE7808604
HGE7808604
HGE7808604
HGE7808604
HGE7808604
LAA7808603
LAA7808603
LAA7808603
HME7808602
HME7808602
HME7808602
BMMG7808601
BMMG7808601
BMMG7808601
BMMG7808601
LAA7808661
TBMRES7808674
TBLPCRRES7808673
TBLPCRRES7808673
TBL_CAP_MGA7808672
TBHRES7808671

<colgroup><col><col><col></colgroup><tbody>
</tbody>


I would expect it to return the result of '5' for all rows with '780860' in column B but it appears to only look 'down' due to the array. Is there an alternative way of calculating this without the array issue?
 
Upvote 0
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))

huskerjhansen said:
I also have a similar issue but am running into an issue with the way array 'sorts' the data.

I'm using the formula "=SUM(IF(FREQUENCY(IF(B42:B140=B42,IF(B42:B140<>"",MATCH("~"&A42:A140,A42:A140&"",0))),ROW(A42:A140)-ROW(A42)+1),1))" and it is working well but I notice when it hits a row with multiple values for my given criteria, it only counts down and thus only the first iteration is correct.

For Example:

BAB7808605
BAB7808605
BAB7808605
BAB7808605
BAB7808605
BAB7808605
HGE7808604
HGE7808604
HGE7808604
HGE7808604
HGE7808604
HGE7808604
LAA7808603
LAA7808603
LAA7808603
HME7808602
HME7808602
HME7808602
BMMG7808601
BMMG7808601
BMMG7808601
BMMG7808601
LAA7808661
TBMRES7808674
TBLPCRRES7808673
TBLPCRRES7808673
TBL_CAP_MGA7808672
TBHRES7808671

<tbody>
</tbody>


I would expect it to return the result of '5' for all rows with '780860' in column B but it appears to only look 'down' due to the array. Is there an alternative way of calculating this without the array issue?
Click to expand...
 
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
834
gvsumich
G
R
  • Question
Macro not working after adding two new items
Replies
4
Views
108
myall_blues
myall_blues
E
  • Question
VBA for Web Scraping help
Replies
0
Views
593
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,020
Messages
6,122,712
Members
449,093
Latest member
Mnur

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