Article updated on September 12, 2017

ExcelBeginner asks:

I have a small problem that I am not sure on how to solve.
I now have a list of 15 cells with 3 unique values (arr formula in all 15 cells). When I use this into a pivot table, I want to get the count as 3 since that is the unique count. But I am getting 15 as the value!
I am assuming that the pivot table is reading the formula as a value and counting it. Any way to circumvent that?

Answer:

Update: It is now possible to count unique distinct values in a Pivot Table. You need Excel 2013 or a later version.

I don´t think it is possible to count unique values in a pivot table unless you add another column to your table or use VBA:  Improve your Excel Pivot Table to count unique values or items

Formula in D3:

=1/COUNTIF($C$3:$C$17, C3)

Copy cell D3 (Ctrl + c). Paste (Ctrl+ v) on cell range D4:D17.

Setup pivot table

  1. Click and drag Array formula to Row Labels
  2. Click and drag Count to Values

How the formula in cell D3 works

Step 1 - Construct the COUNTIF function

COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition.

  1. Select cell D3.
  2. Type =COUNTIF(
  3. Select cell range C3:C17
  4. Press F4. You have now created absolute cell references to cell range C3:C17.
    =COUNTIF($C$3:$C$17
  5. Now create a relativ cell reference to cell C3.
    =COUNTIF($C$3:$C$17, C3)

=COUNTIF($C$3:$C$17, C3)

becomes

=COUNTIF({"AA"; "AA"; "AA"; "BB"; "BB"; "BB"; "BB"; "BB"; "BB"; "BB"; "BB"; "CC"; "CC"; "CC"; "CC"}, "AA")

and returns 3. There are three AA in the array.

Step 2 - Divide 1 with the number of counted values

=1/COUNTIF($C$3:$C$17, C3)

becomes

=1/3

and returns

0.333333333333333 in cell D3.

Download excel sample file for this article.

count unique values in a pivot table.xlsx
(Excel 2007 Workbook *.xlsx)

Recommended blog posts
Excel 2007 pivot table: Count unique distinct records (rows)

Recommended posts

Excel 2007 pivot table: Count unique distinct records (rows)

Table of Contents Count unique distinct records Count duplicate records Excel 2007 pivot table: Count unique distinct records (rows) The […]

Count unique distinct values in a filtered table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Extract a unique distinct list based on a condition [Pivot Table]

Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]

Count unique distinct text values in a range

Array formula in D14: =SUM(IF(FREQUENCY(COUNTIF(tbl,  "<"&IF(ISTEXT(tbl), tbl, "")),  COUNTIF(tbl,  "<"&IF(ISTEXT(tbl), tbl, "")))>0,  1,  0)) + CTRL + SHIFT + ENTER […]

Count unique distinct values in a large dataset [UDF]

Overview This blog post describes how to count unique distinct values in list. We have created excel formulas before to […]

Count unique distinct values based on date criteria in a range

Question: How do I count unique values (not date values) in a range where every second column contains dates? I […]