Hello, Need pivot table to count how many unique names entered per month

JENESIS9777

New Member
Joined
May 19, 2010
Messages
14
Hello,
I need a pivot table to sort how many unique names were entered in Column E per Month (month is Column C). How do I do this I can count unique names? I have only had total number of occurences.

For Example
Column C= Month (litterally the month name not a whole date) or should I use a date range instead? I have actual dates in column B.
Column E= Names
I entered 10 Names In June
I entered ? Names in May, etc.

But I need how many unique names, because many of the names repeat over the months.

Thanks using excel 2003.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,073
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can't do that in a pivot table directly - you need to add a formula column to the underlying data that uses a formula like:
=1/sumproduct(($C$2:$C$100=C2)*($E$2:$E$100=E2))
and copy down, then sum that column in the pivot table.
 

JENESIS9777

New Member
Joined
May 19, 2010
Messages
14
THank you Rory,
I was wondering, do I paste the formula into the pivot table somehow, or into the data before I create it? Not sure the steps to do that.
Thank you!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,073
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The formula has to go into a new column in the underlying data sheet. You cannot do it directly in the pivot table.
 

JENESIS9777

New Member
Joined
May 19, 2010
Messages
14

ADVERTISEMENT

Hi Again,
I entereed the formula in the data sheet in a new cloumn and dragged the formula down all the rows, then created a pivot chart with names in Rows and Months as columns. The good news is the formula works but it works to count how many times the names show up per month, but I need to count how many unique names were entered per month, not how many times total per name. Did I do something wrong or is it the wrong type of formula?The table below should only count as 1 each, the name is written 4 times that month is what is showing in my table.

<TABLE style="WIDTH: 203pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=269 border=0 x:str><COLGROUP><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" span=3 width=65><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=65 height=17>APRIL</TD><TD class=xl26 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #e1e0d2; WIDTH: 49pt; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" width=65>MAY</TD><TD class=xl26 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #e1e0d2; WIDTH: 49pt; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" width=65>JUNE</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" width=74>Grand Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl26 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e1e0d2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e1e0d2; BORDER-TOP: #e1e0d2; BORDER-LEFT: #e1e0d2; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e1e0d2; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e1e0d2; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR></TBODY></TABLE>

So each unique name counts as 1. Not how many times it shows per month.
 

JENESIS9777

New Member
Joined
May 19, 2010
Messages
14
I also noticed on the data sheet the formula that I dragged down, only worked up to row 100. 101 on has a #DIV/0! Error. But the formats are the same from what I can tell. The data sheet will be over 2000 rows long.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,073
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I'd have to guess that:
1. The formula is entered wrongly; or
2. There are slight variations in the names (such as trailing spaces); or
3. You have set the field to count, not sum

What the formula does for each row is count how many times that name appears for that month, then divide one by that number. Do, for example, if Smith appears 4 times in January, the formula calculates 1/4 for each row. Then when the pivot table sums that field, you get 1/4 * 4 = 1 unique count.

PS You will need to alter the $100 in the formula to match however many rows you have.
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,863
Messages
5,766,814
Members
425,379
Latest member
thedoctor00

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
Top