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

[JENESIS9777]

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.

 

[RoryA]

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]

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]

The formula has to go into a new column in the underlying data sheet. You cannot do it directly in the pivot table.

 

[JENESIS9777]

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]

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]

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.