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

#### JENESIS9777

##### New Member
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
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
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
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

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
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
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:

Replies
2
Views
229
Replies
7
Views
190
Replies
1
Views
99
Replies
0
Views
95
Replies
4
Views
189

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.

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.

### Which adblocker are you using?

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

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