Count in a Pivot Table: Count vs Distinct Count

A pivot table count tells you how many rows fall in each group: orders per region, tickets per agent, sales per month. You get it by putting a field in Values and setting it to Count. When you need to count each customer or product only once, you need Distinct Count instead. This guide shows both, explains why the two numbers differ, and how to check them with formulas.

How to count rows in a pivot table#

To count, put a field in the Values area and make sure it summarizes by Count. The examples use this order list in A1:D9:

1. Click inside the data and choose Insert > PivotTable, then OK.
2. Drag Region to Rows.
3. Drag Customer to Values.

Because Customer is a text field, Excel uses Count by default. The pivot shows Count of Customer: East 3, North 1, South 2, West 2, Grand Total 8. That's the number of orders in each region. The beginner's guide to pivot tables covers the basic layout in more detail.

How to change Sum to Count#

Number fields go into Values as Sum. To count them instead:

1. Right-click any number in the values area.
2. Choose Summarize Values By > Count.

You can also click the field's drop-down arrow in the Values box, choose Value Field Settings, and pick Count on the Summarize Values By tab. While you're there, click Number Format to set how the values look.

Sometimes Excel picks Count for a number column on its own. That happens when the column has at least one blank cell or a number stored as text. If a sales column shows counts instead of totals, look for those cells first.

Count vs Count Numbers#

The Summarize list has two counting options, and they behave like two worksheet functions:

Neither counts blank cells. So if you count a column where some cells are empty, those rows quietly drop out of the total. The safest field to count is one that is never empty, such as an order ID or ticket number. In the table above, counting Order gives the same 3, 1, 2, 2 as counting Customer, because no cell is blank.

How to use Distinct Count in a pivot table#

Count adds up rows, so Acme, with two East orders, counts twice. If the question is "how many different customers bought in each region?", use Distinct Count:

1. Click inside the data and choose Insert > PivotTable.
2. In the dialog, tick Add this data to the Data Model, then click OK.
3. Drag Region to Rows and Customer to Values.
4. Click the drop-down on Count of Customer in the Values box and choose Value Field Settings.
5. Scroll to the bottom of the list, choose Distinct Count and click OK.

Notice the Grand Total for Distinct Count is 4, not 2 + 1 + 1 + 1 = 5. Acme bought in East and North, but it's one customer overall. Distinct totals never simply add up, which is correct, but it surprises people.

Distinct Count only appears in pivots built on the Data Model, an option in Excel for Windows (2013 and later). You can't switch it on for an existing pivot; build a new one. If you don't see the checkbox, use the formula method below.

How to check a pivot table count with formulas#

Formulas give the same answers and are a good way to check a pivot. For the order count per region, use COUNTIF or COUNTIFS:

=COUNTIF(C2:C9,"East")

The result is 3, the same as the pivot. For distinct customers in the East, combine three functions:

=COUNTA(UNIQUE(FILTER(B2:B9,C2:C9="East")))

FILTER returns Acme, Acme, Delta. UNIQUE shortens that to Acme, Delta. COUNTA counts them: 2. UNIQUE and FILTER need Excel 2021, Excel 2024 or Microsoft 365. For when to use a table and when a pivot, read Excel table vs pivot table.

Why is my pivot table count wrong?#

Most wrong counts come from the data or a stale pivot. Check these:

• New rows are missing. A pivot doesn't update by itself. Right-click it and choose Refresh, and make sure the source range includes the new rows. See how to refresh a pivot table.
• The count is too low. The counted field has blank cells. Count an ID column instead.
• The count is too high. You need Distinct Count, not Count, because the same customer appears in many rows.
• "Banana" and "banana " count separately. A trailing space makes a different item. Clean the data with TRIM and refresh.
• The pivot won't build. A blank header causes the "PivotTable field name is not valid" error. Here is how to fix the field name error.

To show each count as a share of the total, right-click a value and choose Show Values As > % of Column Total.

Practise it#

Counting by group is one of the most common pivot questions in interviews and job tests. Practise pivot tables in a live spreadsheet: build a pivot, switch from Sum to Count and Average, and read the answers off it. For interview prep, go through the pivot table interview questions next.