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
I have a similar question, however I wanted to count a column if it is in a specific month.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Date (DD/MM/YYYY)
[/TD]
[TD] Link:
[/TD]
[/TR]
[TR]
[TD]01/1/2014
[/TD]
[TD]www.abc.com
[/TD]
[/TR]
[TR]
[TD]06/3/2014
[/TD]
[TD]www.123.com
[/TD]
[/TR]
[TR]
[TD]06/5/2014
[/TD]
[TD]www.1234.com
[/TD]
[/TR]
[TR]
[TD]25/5/2014
[/TD]
[TD]www.xyz.com
[/TD]
[/TR]
</tbody>[/TABLE]

I wanted to count the number of links that appear in May. Which in the example is 2.

Thank you!
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Toots said:
I have a similar question, however I wanted to count a column if it is in a specific month.

[TABLE="class: outer_border, width: 500"]
<TBODY>[TR]
[TD]Date (DD/MM/YYYY)
[/TD]
[TD]Link:
[/TD]
[/TR]
[TR]
[TD]01/1/2014
[/TD]
[TD]www.abc.com
[/TD]
[/TR]
[TR]
[TD]06/3/2014
[/TD]
[TD]www.123.com
[/TD]
[/TR]
[TR]
[TD]06/5/2014
[/TD]
[TD]www.1234.com
[/TD]
[/TR]
[TR]
[TD]25/5/2014
[/TD]
[TD]www.xyz.com
[/TD]
[/TR]
</TBODY>[/TABLE]

I wanted to count the number of links that appear in May. Which in the example is 2.

Thank you!
Click to expand...

If we had another entry where Date = 27-May-2014 and Link = www.xyz.com, would the count become 3 or stay 2?
 
Upvote 0
Toots said:
yes, it if there is another May, it will count it and add it to 3.
Click to expand...

It's not a distinct or unique count issue... That said:

=COUNTIFS(A:A,">="&E2,A:A,"<="&EOMONTH(E2,0),B:B,"?*")

where E2 houses 1-May-2014, the first day date of May in 2014.
 
Upvote 0
I also have a similar question. I would like to count the times a unique entry appears in one column based upon the manufacture name in another column. In this example, Nestle would be 3, Monsanto is 2, Texas instruments is 2, and Shell is 1. Thanks!
[TABLE="width: 243"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Vendor Item Id[/TD]
[TD]Manufacturer Name[/TD]
[/TR]
[TR]
[TD]335[/TD]
[TD]Nestle[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]Monsanto[/TD]
[/TR]
[TR]
[TD]B75453[/TD]
[TD]Texas Instruments[/TD]
[/TR]
[TR]
[TD]2345[/TD]
[TD]Shell Inc[/TD]
[/TR]
[TR]
[TD]87643[/TD]
[TD]Nancy's Sewing Shop[/TD]
[/TR]
[TR]
[TD]689043[/TD]
[TD]Tree Farm Nursery[/TD]
[/TR]
[TR]
[TD]M5678[/TD]
[TD]Nestle[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]Monsanto[/TD]
[/TR]
[TR]
[TD]87643[/TD]
[TD]Nancy's Sewing Shop[/TD]
[/TR]
[TR]
[TD]689043[/TD]
[TD]Tree Farm Nursery[/TD]
[/TR]
[TR]
[TD]N983762[/TD]
[TD]Nancy's Sewing Shop[/TD]
[/TR]
[TR]
[TD]v987473[/TD]
[TD]Tree Farm Nursery[/TD]
[/TR]
[TR]
[TD]34982[/TD]
[TD]Nestle[/TD]
[/TR]
[TR]
[TD]598734[/TD]
[TD]Monsanto[/TD]
[/TR]
[TR]
[TD]9087543[/TD]
[TD]Texas Instruments[/TD]
[/TR]
[TR]
[TD]N983762[/TD]
[TD]Nancy's Sewing Shop[/TD]
[/TR]
[TR]
[TD]v765498[/TD]
[TD]Tree Farm Nursery

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
maybe:

Book1
ABCDE
1Vendor Item IdManufacturer Nameunique listsum unique
2335NestleMonsanto2
341MonsantoNancy's Sewing Shop2
4B75453Texas InstrumentsNestle3
52345Shell IncShell Inc1
687643Nancy's Sewing ShopTexas Instruments2
7689043Tree Farm NurseryTree Farm Nursery3
8M5678Nestle
941Monsanto
1087643Nancy's Sewing Shop
11689043Tree Farm Nursery
12N983762Nancy's Sewing Shop
13v987473Tree Farm Nursery
1434982Nestle
15598734Monsanto
169087543Texas Instruments
17N983762Nancy's Sewing Shop
18v765498Tree Farm Nursery
19
Foglio3
Cell Formulas
RangeFormula
E2{=SUM(IF(FREQUENCY(IF(Manufacturer_Name<>"",IF(Manufacturer_Name=D2,MATCH(Vendor_Item_Id,Vendor_Item_Id,0))),ROW(Manufacturer_Name)-ROW($B$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Manufacturer_Name=Foglio3!$B$2:$B$18
Vendor_Item_Id=Foglio3!$A$2:$A$18
 
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,954
Messages
6,175,603
Members
452,658
Latest member
GStorm

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