Count Unique Entries in a Month for Pivot Table

Question

Good afternoon,

I'm currently working on setting up a pivot table for chart that outlines an Average Revenue per Person by Line Type. Using the pivot table, I can sort everything out, but when I try to get a count of person, it adds all instances of that person's name up. What I want is that if the person's name appears once in a month, all other instances in that month of the person's name are not counted. But during a new month, the person's name will appear once more. So, it is somewhat like a unique count, but only unique per month.

My excel table looks something like:

A             B             C                D
Date          Person        Revenue          Line Type
1/1/2015      John          $100             Toy
1/6/2015      Phil          $200             Toy
1/6/2015      Jane          $25              Garden
1/7/2015      John          $50              Electronics
1/25/2015     John          $10              Electronics
2/1/2015      John          $10              Toy
2/17/2015     Phil          $30              Garden
2/20/2015     Bob           $500             Electronics
2/21/2015     Jane          $100             Garden

So, as you can see, a person's name can occur more than one time in a month, and in more than one month. Currently, the code I am using for my helper column (E) is:

=1/COUNTIF($B:$B,B2)

This has only been giving me a count of patients throughout the entire year, not taking new months into account. I also have attempted this formula:

=IF(SUMPRODUCT(($B$2:$B2=B2)*($A$2:$A2=A2))>1,0,1

This only counts values that occur on the same day. I've tried adding in MONTH() checks and such, and am only getting syntax errors. I'm not sure where to turn for this one. Thank you!

I would just set up another helper column containing =MONTH(A2)&B2 to get all the month/person pairs, then go on from there. — Tom Sharpe
@TomSharpe This worked perfectly! Thanks! Adding the answer. — user4406723

Unknown Unknown · Accepted Answer · 2016-03-03T15:36:54

Thanks to @TomSharpe's advice, I added a helper column to concatenate Month/Date and the Patient's name like so:

=MONTH(A2)&"-"&B2

Then used the original formula (below) to COUNTIF divided by 1.

=1/COUNTIF($B:$B,B2)

This gave me exactly what I needed.

Thanks again!

Count Unique Entries in a Month for Pivot Table

1 Answers