Simple Pivot Table to Count Unique Values

Question

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on.

For instance, I have this:

ABC   123
ABC   123
ABC   123
DEF   456
DEF   567
DEF   456
DEF   456

What I want is a pivot table that shows me this:

ABC   1
DEF   2

The simple pivot table that I create just gives me this (a count of how many rows):

ABC   3
DEF   4

But I want the number of unique values instead.

What I'm really trying to do is find out which values in the first column don't have the same value in the second column for all rows. In other words, "ABC" is "good", "DEF" is "bad"

I'm sure there is an easier way to do it but thought I'd give pivot table a try...

Note that for recent versions of excel, [this answer](http://stackoverflow.com/a/21552440/983722) is most convenient. — Dennis Jaheruddin, Nov 21 '14 at 15:41

score 252 · Answer 1 · answered Feb 04 '14 at 12:21

252

UPDATE: You can do this now automatically with Excel 2013. I've created this as a new answer because my previous answer actually solves a slightly different problem.

If you have that version, then select your data to create a pivot table, and when you create your table, make sure the option 'Add this data to the Data Model' tickbox is check (see below).

Tick the box next to 'Add this data to the Data Model'

Then, when your pivot table opens, create your rows, columns and values normally. Then click the field you want to calculate the distinct count of and edit the Field Value Settings: Edit field value settings

Finally, scroll down to the very last option and choose 'Distinct Count.' Choose the option 'Distinct Count'

This should update your pivot table values to show the data you're looking for.

answered Feb 04 '14 at 12:21

Meaghan Fitzgerald

2,829
2
16
22

1

Does anybody know if this works in LibreOffice also? There doesn't seem to be a similar option but maybe it is hidden somewhere? – Stockfisch May 05 '14 at 14:59
This helped me a lot. Thank you. Anyone knows what happens if I open an excel file with distinct count summarization in an Excel 2010 for example? Perhaps the pivot table get all messed up? – Sawd Jul 27 '15 at 19:35
2

@Sawd If you try and save a file that uses the Data Model function as an older Excel format, you get a warning that 'Some pivot table functions will not be saved' - I assume anyone trying to open this file as Excel 2010 will not be able to see it. If you're trying to transfer the data, you could always copy the pivot table and paste the values in a new sheet. Not ideal, but always compatible! :) – Meaghan Fitzgerald Jul 27 '15 at 20:06
5

@MichaelK its much better, if you have Excel 2013 – jrharshath Sep 18 '15 at 15:08
3

Can this also be done to existing pivot tables, so we don't need to recreate 200+ tables to get access to the distinct count functionality? – Louisa Nov 25 '15 at 08:36
I have this feature in 2010 as well not just 2013 – Antony D'Andrea Feb 24 '16 at 07:02
12

Just an FYI: if you haven't yet saved your file as an Excel (.xlsx) file yet (eg: you opened a .csv file), the option to "Add this data to the Data Model" is disabled/greyed out. The simple solution is to save the file as an Excel file. – PonyEars Mar 09 '16 at 00:18
If you add the data to the Data Model, you will **NOT** be able to Group fields in your pivot, i.e. you will not be able to group a datetime field by month/quarter/year/etc. – blobdon Apr 18 '16 at 14:35
9

Is this not supported on Mac? This option does not appear for me. I'm on version 15.27. – jkupczak Jan 30 '17 at 22:30
1

If you add the data to Data Model, then I also seem to not be able to add a *calculated field*. (additional info to what @blobdon commented above) – Kalin Mar 11 '17 at 00:41
5

This option indeed doesn't exist on a Mac, as Data Models in general are a Windows-only feature. – Tomty Jan 27 '18 at 19:27
Well darn... need to get on Windoze. – Abram Feb 15 '18 at 23:49
While this solution gives you Distinct Count it disables the ability to make custom subtotals (among other things commented above) – Gabe G May 29 '18 at 13:48
Hi, I've used this solution to get a distinct count and it works. However, I have a pivot table filter that is month name. It is now ordering the months in alphabetical order instead of chronological order. How can I rectify this please? – user3603308 Jun 01 '18 at 01:58

score 110 · Accepted Answer · answered Aug 09 '12 at 03:18

110

Insert a 3rd column and in Cell C2 paste this formula

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

and copy it down. Now create your pivot based on 1st and 3rd column. See snapshot

enter image description here

answered Aug 09 '12 at 03:18

Siddharth Rout

147,039
17
206
250

+1 I think this is slightly easier than my solution because it doesn't require a special value for the first row – lc. Aug 09 '12 at 03:23
2

Nice technique. I didn't know about this one. You can do the same thing with an array function `=IF(SUM((A$2:A2=A2)*(B$2:B2=B2)) > 1, 0, 1)` (press Ctrl-Shift-Enter when entering the formula so it acquires `{}` around it). – ErikE Feb 10 '13 at 08:07
Universal answer, not requiring any specific feature. Just good plain formulas. – Alberto De Caro May 11 '15 at 11:49
Any idea on how to extend this to a situation with three columns? – tumultous_rooster Jun 06 '15 at 01:36
@MattO'Brien: In that case the current header3 moves to 4th column and the formula there becomes `=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,1)` – Siddharth Rout Jun 06 '15 at 06:32
13

Note that this answer will NOT give the correct solution if you filter out some of the rows using the Pivot Table options. Let's say the first row is filtered out. The sum of ABCs will then appear to be 0! – jarlemag Jul 05 '15 at 17:49
Excellent! Could you just explain the solution plainly? – Fighter Jet Dec 12 '17 at 04:59
@FighterJet: You need to first understand how Sumproduct works :) – Siddharth Rout Dec 13 '17 at 04:51
@SiddharthRout Oh yeah, I know it! – Fighter Jet Dec 13 '17 at 06:21
This is OK for small datasets, but the formulas have a nasty N^2 behavior which brings Excel to its knees with larger datasets (500k rows). There must be a trick that doesn't go all the way to the top for every line (assuming data starts out sorted) – Floris Apr 21 '18 at 18:26
Hi! What about when i have to summarize with pivot table subtotals. Basically i havea table like below week Promotion product Distinct week1 1 1 1 week1 1 2 1 week1 1 3 1 week1 3 1 0 week2 2 1 0 week2 2 2 0 week2 2 12 1 – kaos1511 Jan 12 '21 at 09:25

score 10 · Answer 3 · edited Nov 18 '15 at 22:32

I'd like to throw an additional option into the mix that doesn't require a formula but might be helpful if you need to count unique values within the set across two different columns. Using the original example, I didn't have:

ABC   123  
ABC   123  
ABC   123   
DEF   456  
DEF   567  
DEF   456  
DEF   456

and want it to appear as:

ABC   1  
DEF   2

But something more like:

ABC   123  
ABC   123  
ABC   123  
ABC   456  
DEF   123  
DEF   456  
DEF   567  
DEF   456  
DEF   456

and wanted it to appear as:

I found the best way to get my data into this format and then be able to manipulate it further was to use the following:

enter image description here

Once you select 'Running total in' then choose the header for the secondary data set (in this case it would be the header or column title of the data set that includes 123, 456 and 567). This will give you a max value with the total count of items in that set, within your primary data set.

I then copied this data, pasted it as values, then put it in another pivot table to manipulate it more easily.

FYI, I had about a quarter million rows of data so this worked a lot better than some of the formula approaches, especially ones that try to compare across two columns/data sets because it kept crashing the application.

I had a completely different problem, but this answer just pointed me in the right direction. Thanks. — jtolle, Dec 12 '13 at 22:40
this answer fits my need as i have 500,000 rows that I need to apply the formula and my computer runs out of memory if I am trying to. thank you! — cauldyclark, Mar 21 '16 at 00:00

score 6 · Answer 4 · answered Jul 06 '17 at 00:46

6

I found the easiest approach is to use the Distinct Count option under Value Field Settings (left click the field in the Values pane). The option for Distinct Count is at the very bottom of the list.

Here are the before (TOP; normal Count) and after (BOTTOM; Distinct Count)

answered Jul 06 '17 at 00:46

Peter

364
5
17

4

As of Office 2016: To be able to use this feature pivot table should be created with "Add this data to the Data Model" checked. – Leo Sep 24 '18 at 14:26

score 4 · Answer 5 · answered Aug 09 '12 at 03:21

4

See Debra Dalgleish's Count Unique Items

enter image description here

answered Aug 09 '12 at 03:21

brettdj

54,857
16
114
177

score 3 · Answer 6 · edited Oct 01 '13 at 21:10

3

It is not necessary for the table to be sorted for the following formula to return a 1 for each unique value present.

assuming the table range for the data presented in the question is A1:B7 enter the following formula in Cell C1:

=IF(COUNTIF($B$1:$B1,B1)>1,0,COUNTIF($B$1:$B1,B1))

Copy that formula to all rows and the last row will contain:

=IF(COUNTIF($B$1:$B7,B7)>1,0,COUNTIF($B$1:$B7,B7))

This results in a 1 being returned the first time a record is found and 0 for all times afterwards.

Simply sum the column in your pivot table

edited Oct 01 '13 at 21:10

Peter Albert

16,917
5
64
88

answered Oct 01 '13 at 20:52

Jeff Baumet

31
1

2

In case you have a large dataset, use `=IF(COUNTIF($B$1:$B1,B1),1,0)` - this way, countif is only run once! – Peter Albert Oct 01 '13 at 21:11

score 2 · Answer 7 · edited Oct 23 '14 at 18:25

2

My approach to this problem was a little different than what I see here, so I'll share.

(Make a copy of your data first)
Concatenate the columns
Remove duplicates on the concatenated column
Last - pivot on the resulting set

Note: I would like to include images to make this even easier to understand but cant because this is my first post ;)

edited Oct 23 '14 at 18:25

reVerse

35,075
22
89
84

answered Oct 23 '14 at 18:17

Jacob Noone Wade

21
1

score 1 · Answer 8 · answered Feb 08 '13 at 21:55

1

Siddharth's answer is terrific.

However, this technique can hit trouble when working with a large set of data (my computer froze up on 50,000 rows). Some less processor-intensive methods:

Single uniqueness check

Sort by the two columns (A, B in this example)

Use a formula that looks at less data

=IF(SUMPRODUCT(($A2:$A3=A2)*($B2:$B3=B2))>1,0,1)

Multiple uniqueness checks

If you need to check uniqueness in different columns, you can't rely on two sorts.

Instead,

Sort single column (A)
Add formula covering the maximum number of records for each grouping. If ABC might have 50 rows, the formula will be
```
=IF(SUMPRODUCT(($A2:$A49=A2)*($B2:$B49=B2))>1,0,1)
```

answered Feb 08 '13 at 21:55

workglide

11
1

2

Another possibly less processor intensive way is to add a column C and in C2 `=A2&B2`. Then add a column D and in D2 put `=IF(MATCH(C2, C$2:C2, 0) = ROW(C1), 1, 0)`. Fill both down. While this still searches from the start of the whole range, it stops when it finds the first one, and instead of multiplying the values from 50,000 rows together it just has to locate the value--so it should perform much better. – ErikE Feb 10 '13 at 08:14
@ErikE Sharp - I also think your technique stops on the first find. But if you have a lot of unique values in C (example: only 50 ABCs), you will continue to check huge amounts of data. Cool feature: your formula works best when the data is unsorted. – workglide Feb 10 '13 at 12:54

score 1 · Answer 9 · answered Oct 14 '14 at 20:39

1

Excel 2013 can do Count distinct in pivots. If no access to 2013, and it's a smaller amount of data, I make two copies of the raw data, and in copy b, select both columns and remove duplicates. Then make the pivot and count your column b.

answered Oct 14 '14 at 20:39

Zachary

11
1

score 1 · Answer 10 · answered Jul 09 '15 at 19:01

1

You can use COUNTIFS for multiple criteria,

=1/COUNTIFS(A:A,A2,B:B,B2) and then drag down. You can put as many criteria as you want in there, but it tends to take a lot of time to process.

answered Jul 09 '15 at 19:01

user5100077

11
1

score 1 · Answer 11 · edited Sep 09 '15 at 13:12

1

Step 1. Add a column

Step 2. Use the formula =IF(COUNTIF(C2:$C$2410,C2)>1,0,1) in 1st record

Step 3. Drag it to all the records

Step 4. Filter '1' in the column with formula

edited Sep 09 '15 at 13:12

Nagama Inamdar

2,851
22
39
48

answered Sep 09 '15 at 12:36

Abhishek Verma

11
1

score 0 · Answer 12 · answered Aug 09 '12 at 03:20

You can make an additional column to store the uniqueness, then sum that up in your pivot table.

What I mean is, cell C1 should always be 1. Cell C2 should contain the formula =IF(COUNTIF($A$1:$A1,$A2)*COUNTIF($B$1:$B1,$B2)>0,0,1). Copy this formula down so cell C3 would contain =IF(COUNTIF($A$1:$A2,$A3)*COUNTIF($B$1:$B2,$B3)>0,0,1) and so on.

If you have a header cell, you'll want to move these all down a row and your C3 formula should be =IF(COUNTIF($A$2:$A2,$A3)*COUNTIF($B$2:$B2,$B3)>0,0,1).

score 0 · Answer 13 · edited Jun 01 '13 at 20:03

0

If you have the data sorted.. i suggest using the following formula

=IF(OR(A2<>A3,B2<>B3),1,0)

This is faster as it uses less cells to calculate.

edited Jun 01 '13 at 20:03

Flexo

87,323
22
191
272

answered May 27 '13 at 06:58

SumitB

1
1

score 0 · Answer 14 · answered Jun 05 '14 at 11:12

0

I usually sort the data by the field I need to do the distinct count of then use IF(A2=A1,0,1); you get then get a 1 in the top row of each group of IDs. Simple and doesn't take any time to calculate on large datasets.

answered Jun 05 '14 at 11:12

General Earmuffs

1

score 0 · Answer 15 · edited Oct 03 '18 at 08:35

0

You can use for helper column also VLOOKUP. I tested and looks little bit faster than COUNTIF.

If you are using header and data are starting in cell A2, then in any cell in row use this formula and copy in all other cells in the same column:

=IFERROR(IF(VLOOKUP(A2;$A$1:A1;1;0)=A2;0;1);1)

edited Oct 03 '18 at 08:35

Yannis

1,682
7
27
45

answered Oct 03 '18 at 07:40

Marossik

1
1

score -3 · Answer 16 · answered Jan 08 '13 at 17:06

-3

I found an easier way of doing this. Referring to Siddarth Rout's example, if I want to count unique values in column A:

add a new column C and fill C2 with formula "=1/COUNTIF($A:$A,A2)"
drag formula down to the rest of the column
pivot with column A as row label, and Sum{column C) in values to get the number of unique values in column A

answered Jan 08 '13 at 17:06

Mounir

1

Logically this can't possibly work for the OP because it doesn't look at column `B`. How will you adapt this to work with multiple columns? – ErikE Feb 10 '13 at 08:11

Simple Pivot Table to Count Unique Values

16 Answers16

Linked

Related