[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Excel 2010 Count Unique records from a list

Posted on 2014-01-26
Medium Priority
472 Views
I have a list of records as follows:

1/24/2014  Abe Lincoln

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

THANKS
0
Question by:eyes59
• 3

LVL 18

Expert Comment

ID: 39810422
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:
``````=IF(SUMPRODUCT((\$A\$2:\$A2=A2)*(\$B\$2:\$B2=B2))>1,0,1)
``````

This will return the following:

Create your pivot table and you will get:
0

Author Comment

ID: 39810455
What if I have multiple days.   I get the over unique count but not the unique count by each date.
0

LVL 18

Accepted Solution

Steven Harris earned 2000 total points
ID: 39810472
In the same way, just changing Pivot Table Options.  I have added some highlighting to help distinguish what is going on:

Notice the changes made to the Report Fields (see arrows).
0

LVL 18

Expert Comment

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

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).
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
###### Suggested Courses
Course of the Month19 days, left to enroll