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

Count unique records between two dates

This blog article is one out of  four articles on the same subject. Most common value between two dates in […]

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 and duplicates text values in a closed workbook

Here is a picture of a closed workbook How to count unique distinct text values in a closed workbook This […]

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 […]

Auto populate a sheet

Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]

Count cells by cell and font color

Dave asks: How difficult would it be to make it count colour alone (so not unique values) and / or […]