Excel 2010 Count Unique records from a list

I have a list of records as follows:

1/24/2014  James Madison
1/24/2014  James Madison
1/24/2014  Abe Lincoln
1/24/2014  James Madison
1/24/2014  Abigail Adams
1/24/2014  Abigail Adams

I want the result in the Pivot Table  to be 1/24/2014      3      Unique name records.  What is the formula or summarize by values choice

Who is Participating?
Steven HarrisConnect With a Mentor PresidentCommented:
In the same way, just changing Pivot Table Options.  I have added some highlighting to help distinguish what is going on:
multiple days
pivot as created
pivot - modified
Notice the changes made to the Report Fields (see arrows).
Steven HarrisPresidentCommented:
1) Date is in Column A (with Header Date)
2) Name is in Column B (with Header Name)
3) Create a new Column C (with Header Unique Values)
4) In the new column, use the following formula and copy down:

Open in new window

This will return the following:
using formula
Create your pivot table and you will get:
eyes59Author Commented:
What if I have multiple days.   I get the over unique count but not the unique count by each date.
Steven HarrisPresidentCommented:
And just to help explain further how we are getting the info:

The formula is finding the unique values for a given condition.  In this instance, per date.  The number one (1) shows the first unique value for a given record. Zero (0) represents that a record with that value(s) has already been found and marked as one (1).

When you build the Pivot table and select all fields, this gives us the full base of the data:

pivot base data
In this case, 0 (null) is not a valid value, so it is not displayed.

We see all values with a 1, and each instance is added (Sum) to give us a total (which is the value you are looking for).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.