In an Excel PivotTable, Count counts nonblank entries, including text and numbers, while Count Numbers counts numeric entries only. Choose Count when every populated record should count; choose Count Numbers when the result should include only values Excel recognizes as numbers.
What Count and Count Numbers actually count
These are two different ways to summarize a field in a PivotTable’s Values area. Count treats a populated cell as an entry regardless of whether it contains text, a number, or a date. Count Numbers skips values that are stored as text and counts numeric values. Both exclude genuinely blank cells.
For example, if a column contains a product name, a quantity, and a blank cell, Count returns two and Count Numbers returns one. A date is stored by Excel as a serial number, so a valid date in the field is generally included by Count Numbers. A number displayed with a currency or percentage format is still numeric; formatting changes how a value looks, not the type of value it is.
Count and Count Numbers are PivotTable summary choices, not worksheet formulas. A useful comparison is that Count resembles COUNTA and Count Numbers resembles COUNT, but do not assume the PivotTable will match those worksheet functions in every unusual case. Imported data, formulas that return empty strings, and errors can affect how values are represented in the PivotTable cache.
A small example of the difference
Suppose an order table has a Quantity column with these six entries: 3, a blank, 0, 5 stored as text, 2, and 4. The text value looks like a number, but its underlying cell value is text. A PivotTable gives these results:
- Count: 5, because five cells are populated, including the text-stored 5.
- Count Numbers: 4, because only 3, 0, 2, and 4 are numeric values.
Zero counts as a number. It is not a blank, even if a custom number format makes zero appear invisible. Similarly, a cell containing a space is not truly empty and may be counted as a populated text entry. If a count seems one too high, inspect the source cells for spaces or other invisible-looking content.
How to change the PivotTable calculation
In Excel for Microsoft 365 on Windows, select a cell in the PivotTable and locate the field in the Values area of the PivotTable Fields pane. Use the field’s drop-down arrow and choose Value Field Settings. In the dialog, select the Summarize Values By tab, choose Count or Count Numbers, and select OK. The exact dialog can also be opened by right-clicking a value in the PivotTable and choosing Value Field Settings.
Another route is to right-click a value cell and open Summarize Values By, then select the calculation shown in the menu. If the option is not visible there, use Value Field Settings. To add a field first, drag it from the field list into Values; Excel may choose a default summary based on the data it detects, so check the setting rather than assuming it chose the intended count.
On Mac, select the PivotTable and use its PivotTable controls or the value field’s context menu to open the field settings; ribbon labels and pane layout can differ from Windows. In Excel for the web, some PivotTable controls may be presented differently or be more limited, so if a summary option is unavailable, open the workbook in the desktop app.
Why text that looks numeric can change the result
Excel distinguishes a number stored as a numeric value from digits stored as text. A value imported from a CSV, copied from a website, or produced by a system export may look like 125 but be stored as text. Count includes it because the cell is populated; Count Numbers leaves it out. Changing the display format to Number alone does not convert text into a number.
To investigate, select a suspicious source cell and check whether Excel flags it as a number stored as text, or inspect the formula bar and cell alignment as clues. Alignment is not definitive because formatting can override Excel’s usual display. Depending on the source, you can convert the values using the warning menu, Text to Columns, or a suitable cleaning step before building or refreshing the PivotTable. Keep the original data or a backup before making bulk changes, and confirm that identifiers such as postal codes are not supposed to remain text.
That last distinction matters: counting numeric values is not always the same as counting meaningful records. An account number like 00127 may be an identifier, not a quantity to add or analyze as a number. If the goal is to count records regardless of the field’s type, Count on a consistently populated record ID is often a better choice.
How to choose the right summary
Choose the summary based on the question you want the PivotTable to answer, not merely the field’s appearance. Count is usually appropriate for counting populated labels or records. Count Numbers is appropriate when text entries should not contribute and you specifically want the number of numeric values.
- Count products, names, or statuses: use Count if each nonblank label represents an entry.
- Count recorded measurements: use Count Numbers if only valid numeric measurements should qualify.
- Count all rows in a category: use Count on a field that is filled in for every source row, such as a reliable order ID.
- Count distinct customers: neither setting removes duplicates; use a distinct-count option where available.
For example, a sales report may have Customer, Amount, and Notes fields. Count on Customer can count populated customer entries, including repeated customers. Count Numbers on Amount can count numeric sales amounts, including zero-value transactions, but it will omit amounts stored as text. Count on Notes counts populated notes, not the number of sales. The chosen field and calculation together determine what the result means.
Count is not the same as a distinct count
Both Count and Count Numbers count entries, not unique values. If a customer appears five times in the source, Count on the customer field can contribute five, not one. To count unique customers in a PivotTable, use Distinct Count if it is available. In Excel for Windows, this option is associated with creating the PivotTable using the Data Model; in the Create PivotTable dialog, select Add this data to the Data Model, then look for Distinct Count in the value field settings.
Data Model availability and PivotTable features can differ by platform and Excel edition, especially on Mac. If Distinct Count is not offered, alternatives include removing duplicates from a copy of the data before summarizing or using another supported method to identify unique records. Do not substitute Count Numbers: it filters by numeric type, not by uniqueness.
Common reasons the PivotTable count looks wrong
A mismatch between the expected result and the PivotTable is often a data-quality or field-selection issue rather than a calculation failure. Check the source and settings systematically:
- Verify the summary method. Open Value Field Settings and confirm whether the field is set to Count or Count Numbers.
- Check for numeric text. Values that look numeric but are stored as text are included by Count and excluded by Count Numbers.
- Look for blanks and spaces. A truly empty cell is excluded, but a space or other text may count as an entry.
- Check the selected field. A field with missing values will not count every row. Use a consistently filled identifier if the goal is a row count.
- Refresh after changing source data. Right-click the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. If the source table or range has grown, confirm the PivotTable source includes the new rows.
Formula cells deserve a closer look. A formula that returns an empty string is not necessarily the same as a cell that has never contained a value. How it appears in a PivotTable can depend on the source and cache. If these cells affect the result, test a few representative rows and consider cleaning the source so that missing values are represented consistently. Error values should also be investigated rather than treated as ordinary blanks or numbers; resolve or explicitly handle them in the source before relying on a summary.
How the Values area affects the result
A PivotTable can place the same source field in different areas for different purposes. A field in Rows or Columns organizes the report into labels. A field in Values is summarized, and that is where Count and Count Numbers apply. For instance, placing Department in Rows and Employee ID in Values, summarized by Count, produces a count of populated IDs for each department.
If Excel defaults to Sum, the field is being treated as suitable for adding, or the selected summary is otherwise not the one you want. Open Value Field Settings and choose the intended calculation. You can also add the same source field to Values more than once and set one copy to Count and another to Count Numbers, which can make mixed-type data easier to audit. Give each value field a clear custom name so readers can tell what each figure represents.
Frequently Asked Questions
Does Count include text in an Excel PivotTable?
Yes. Count includes populated text entries as well as numbers and dates. It excludes truly blank cells. Use Count Numbers if text should not be included.
Does Count Numbers include zero?
Yes. Zero is a numeric value, so Count Numbers includes it. A number format that hides the displayed zero does not make the underlying value blank.
Why does Count Numbers ignore a number I can see?
The value may be stored as text, even if it looks like a number. Check the source cell’s underlying type and convert it only if it is meant to be numeric. Refresh the PivotTable after correcting the source.
Does Count give the number of unique items?
No. Count counts populated entries, including repeated values. Use Distinct Count when available to count unique items; it requires a Data Model PivotTable in supported Excel versions.
Should I use Count or Count Numbers to count rows?
Use Count on a column that has a nonblank value in every source row, such as a reliable record ID. Count Numbers works only if every qualifying ID is stored as a number, which is often not true for identifiers.
Why did the PivotTable result not change after I edited the data?
PivotTables may need a refresh to show source changes. Right-click inside the PivotTable and choose Refresh. Also verify that the PivotTable’s source range includes any rows added beyond the original range.
Related
- Excel Number Format: Keep Leading Zeros in Your Cells
- Excel Number Format: Hide Zeros Without Deleting Them
- Excel Number Format for Indian Rupees: ₹ and Lakhs
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Google Sheets Bubble Chart: Create and Customize OneYes—Google Sheets can make bubble charts in a web browser. Arrange your data in five columns for a…
- Excel Pivot Table Rows Side by Side: Layout, Steps, and TipsTo put PivotTable row fields side by side in Excel for Microsoft 365 on Windows, click inside the…
- Custom Column Chart in Excel: How to Build and FormatTo make a custom column chart in Excel for Microsoft 365 on Windows, select your category labels and…
- Power BI Pivot Table: How to Build a Matrix ReportPower BI does not have an Excel-style PivotTable object, but its Matrix visual provides a similar way to…
- Excel Histogram Help: Create and Troubleshoot ChartsTo make a histogram in Excel for Microsoft 365 on Windows, select one column of numeric data, then…
- Excel Pivot Table Date Grouping: Months, Years & FixesTo group dates in an Excel PivotTable, put the date field in Rows or Columns, right-click any date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.