OmniDocCount Unique Values in a Pivot Table
Learn why a pivot table defaults to Sum or Count, and three ways to get a distinct count of unique values, plus how to verify the totals.
You have a list of orders and want a pivot table to show how many different customers placed orders in each region. You drag the customer field into the values area, and the pivot shows a Count that is higher than the number of customers, because it counts every row, including repeat orders from the same person. Switching to Sum does not help either, since customer names are text and cannot be added up.
This article shows how to get a true count of unique values inside a pivot table. It covers the distinct count option that some spreadsheet programs offer, a helper column method that works almost anywhere, and the checks that confirm the totals are right. The steps assume you already have your data in a spreadsheet, and they describe general operations rather than one program's menus.
The short version: a pivot table's default value settings count rows, not distinct values. You need either a distinct count option or a helper column that marks the first appearance of each value, then a sum of those marks in the pivot.
How do you count unique values in a pivot table? The short answer
Use a distinct count setting where your program offers one. Where it does not, sort the data so repeated pairs sit next to each other, add a helper column that flags the first row of each group and value pair, then sum that column in the pivot table.
| Your situation | What to do | Why |
|---|---|---|
| Your program offers a distinct count option for value fields | Add the source data to the data model, then set the value field to distinct count | The pivot engine does the counting, so the result updates when the pivot refreshes |
| Your program only offers Sum and Count | Sort by group and value, add a helper column that flags the first occurrence of each pair, then sum the flags | Basic formulas work in any spreadsheet program |
| You need the count for one group only | Filter to that group, then mark the first occurrence of each value | Easy to check by eye on a small dataset |
| Your data sits in a PDF table | Export the table to spreadsheet format, then apply one of the methods above | The pivot needs clean rows and columns to read |
Before you start
Check these points first. Each one affects whether the count comes out right.
- Work on a copy. Helper columns and sorting change the layout of a sheet. Keep the original untouched so you can compare results later.
- Trim spaces. To a spreadsheet, "Acme Ltd" and "Acme Ltd " (with a trailing space) are two different values. Remove extra spaces before you count, or one customer will be counted twice.
- Check that numbers are numbers. Numbers stored as text can group and count differently from real numbers. If a column of numbers lines up on the left side of the cells, it is probably text.
- Decide how to handle blanks. Empty cells may be counted as one value by some methods. Decide whether blanks should count before you build the pivot.
- Name your group and value fields. The unique count is always per group, such as per region. Know which column is the group and which column holds the values you want to count.
Building a unique count with a helper column, step by step
This method works in any spreadsheet program that supports basic formulas and pivot tables. The example uses column A for the group (Region) and column B for the value (Customer), with headers in row 1.
- Set up a clean data range. Put a header row at the top, with the group and value in adjacent columns. Remove blank rows inside the data, because they can break the formula ranges.
- Sort by group, then by value. Sorting puts repeated pairs next to each other, so each pair's first appearance is the row where it changes from the row above. Sort by the group column first, then the value column.
- Enter the flag formula. In the first empty column to the right, type
=IF(AND(A2=A1,B2=B1),0,1)in row 2. The formula compares the current row with the row above it and returns 0 when both the group and the value repeat, and 1 when the pair is new. - Fill the formula down. Copy the formula to the last row of data so every row has a 1 or a 0. Check the bottom row to confirm the formula reached the end of your data.
- Create the pivot table. Select the full range, including the helper column, and insert a pivot table. Place the group field in the rows area.
- Sum the helper column. Add the helper column to the values area and set it to Sum. Each group's total now equals the number of distinct values in that group.
If your program offers a distinct count option, you can skip the helper column. Add the source range to the data model when you create the pivot, then change the value field's setting to distinct count. This option is often hidden until the data model is in use, so if you don't see it, the helper column method gives the same result.
Check the result
Once the pivot shows totals, confirm they match what the data actually contains. Start with a small group you can count by hand.
| Check | How to see it | If it's wrong |
|---|---|---|
| Group totals | Filter to one group, count its distinct values by eye, and compare with the pivot total | Make sure the formula reaches the last row, and that sorting covered the whole range |
| Overall distinct count | Remove duplicates from a copy of the group and value columns, then count the remaining rows | If totals differ, look for spelling or spacing variations and trim them |
| Blank values | Filter the value column for empty cells | Exclude blanks in the formula, for example by wrapping the test in an IF that returns 0 when the value cell is empty |
| Numbers stored as text | Check whether numeric values align on the left side of the cells | Convert the column to real numbers before building the pivot |
| Stale totals | Compare the pivot with the data after you add rows | Extend the source range, copy the formula to the new rows, and refresh the pivot |
❓ FAQ
1. Why does my pivot table show Count when I need unique values?
Text fields often default to Count, which counts every row, including repeated values. A Count setting cannot tell you how many values are different. Use the distinct count option or the helper column method described above.
2. Does a blank cell count as a unique value?
It depends on the method. Many approaches treat a blank as one value, so the total can be one higher than you expect. Exclude blanks in the helper formula if they should not count.
3. Can I count unique combinations across two columns?
Yes. The helper formula in the steps already checks the group and value together, so the same customer counts once within a region but again in a different region.
4. Why do my counts change after I add new rows?
The pivot reads only the source range it was built from, and helper formulas must be filled down to the new rows. Extend the source range, copy the formula to the new rows, and refresh the pivot. Re-sort the data if the new rows break the group and value order.
5. Can I count unique values from a table inside a PDF?
Not directly. Export the table to spreadsheet format from the desktop client, then build the count on the exported file. If the PDF is scanned, run OCR first and check the recognized text, because OCR can misread characters such as 0 and O, which would create false distinct values.
📝 Summary
A pivot table counts rows by default, so counting unique values needs either a distinct count option or a helper column that flags first appearances. Trim spaces and decide how to handle blanks before you count. Then compare a few group totals with a manual count so you can trust the figures in your report.
