In an Excel PivotTable, Count counts nonblank entries in the selected field, including text, while Count Numbers counts numeric entries only. Choose Count when every populated record should contribute, and Count Numbers when the result should include only numeric values, such as recorded sales amounts.
What Count and Count Numbers mean
These are two different ways to summarize a field in a PivotTable. They do not count the number of rows in the entire source table regardless of content; instead, each calculation examines the values in the field you put in the Values area. The PivotTable then reports the result for each group created by its row and column fields.
- Count counts populated entries. Text labels, numbers, and dates can all contribute. Empty cells do not.
- Count Numbers counts entries Excel recognizes as numeric. Text entries are excluded; numeric zero is included.
For example, if a group contains the entries 25, 0, “pending,” and one blank cell, Count returns 3 and Count Numbers returns 2. The zero is a number, so it counts. The text “pending” is populated, so it counts under Count but not Count Numbers. This difference is useful when a column mixes measurements with notes or status labels.
How the PivotTable calculation works
A PivotTable applies the selected summary calculation separately to each group. If Department is in Rows and Amount is in Values, Excel groups records by department and evaluates the Amount entries in each department. The same field can be added to Values more than once and summarized in different ways, so a report can show both the number of populated entries and the number that are numeric.
In Excel for Microsoft 365 on Windows, create or select the PivotTable, place the field you want to analyze in the Values area, and open its value-field settings. You can right-click a value in the PivotTable and choose Summarize Values By, or open Value Field Settings and select the calculation under Summarize Values By. Choose Count or Count Numbers, then select OK if the settings dialog is open. The precise menu presentation can vary with the PivotTable source and Excel build.
On Excel for Mac, the same summary concepts apply, though the ribbon and context-menu layout can differ. Look for the value field’s settings or summarize options rather than relying on a Windows-specific click sequence. Excel for the web may expose a more limited or differently arranged set of PivotTable controls than the desktop app; if an aggregation is unavailable there, open the workbook in desktop Excel.
A worked example with mixed data
Suppose a service log contains a Department field and a Resolution Time field. Some records have a number of minutes, some have the text “not recorded,” and some have no value yet. If Resolution Time is placed in Values and summarized by Count, Excel counts each nonblank number and each nonblank text entry. If summarized by Count Numbers, only the numeric times contribute.
Consider the following values for one department: 18, 0, “not recorded,” blank, 42, and 15. Count is 5: four numeric entries plus the text entry. Count Numbers is 4: 18, 0, 42, and 15. The blank contributes to neither result. The zero may represent a real zero-minute resolution; it is not the same as a missing value.
This is also why the results are not necessarily a record count. If every source row has a transaction ID but some Amount cells are blank, Count of Amount excludes those incomplete amounts. To count all records in each group, use a field that is filled in on every record, such as a unique transaction ID, and summarize that field by Count. If the ID field itself has missing entries, it will undercount rows too.
Choosing the right calculation
Choose based on what the field represents and what the report is meant to answer—not simply because one result looks more plausible. Count is usually appropriate for a mixed field where any entry means “something was recorded.” Count Numbers is appropriate when only numeric observations should be included.
- Use Count for populated names, categories, comments, codes, or a field that may contain both numeric and text values.
- Use Count Numbers for numeric measurements, amounts, quantities, or dates when text notes should not be treated as observations.
- Use a consistently populated ID when the goal is to count source records rather than populated values in a particular measure field.
- Check blanks before interpreting either result. A blank may mean “not entered,” “not applicable,” or something else in the source process.
For instance, in a sales report, Count of Order Status can show how many orders have a status recorded, including text such as “Open” or “Shipped.” Count Numbers of Sales Amount can show how many amount cells contain numbers. Neither number is automatically the total number of orders: that is best counted using a reliably populated order identifier.
Dates, zeroes, text numbers, and formulas
Excel stores dates as serial numbers, so valid date values are numeric for many spreadsheet calculations. As a result, a date field can contribute to Count Numbers as well as Count. The cell’s display format does not turn a date into text: formatting a numeric date to look like a date changes how it is displayed, not the underlying value.
A number-looking entry stored as text is different. For example, a cell containing the text “42” may look like the number 42, but it is text rather than a numeric value. Count can include it as a populated entry; Count Numbers generally excludes it. Imported files are a common source of this mismatch. Check whether the values are genuinely numeric before deciding that the PivotTable is wrong.
Zero is a valid numeric value and is included by Count Numbers. A blank is not. If a source formula returns an empty string, its behavior in a PivotTable can be confusing because it is not always handled like a genuinely empty cell. Check the source cells and refresh the PivotTable before drawing conclusions; if the distinction matters, use a clearly defined source-data rule for missing values.
Related worksheet functions and a common misconception
PivotTable Count is not the same thing as using the worksheet function COUNT. In a worksheet, COUNT counts numbers, while COUNTA counts non-empty cells. Their broad behavior is closer to Count Numbers and Count, respectively, but a PivotTable is summarizing a field from its source and can have its own field and source-model considerations.
For a small source example, assume B2:B7 contains 120, a genuinely blank cell, the text “pending,” 0, the date 1/5/2026, and 85. With those entries in B2:B7, =COUNT(B2:B7) returns 4 because it counts 120, 0, the date’s numeric value, and 85. With the same cell layout, =COUNTA(B2:B7) returns 5 because it also counts “pending” but ignores the blank. This illustrates the practical distinction, but a worksheet formula is not a substitute for checking the PivotTable’s selected summary setting.
Another frequent misunderstanding is that Count Numbers means “count distinct numbers.” It does not: repeated numeric values each contribute. If a field contains 10, 10, and 20, Count Numbers returns 3, not 2. A distinct count is a separate calculation and is available in certain PivotTable setups, such as a PivotTable created with the data added to the Data Model. Availability and options depend on the source and model.
Why the numbers may look wrong
If Count and Count Numbers return the same result, the field may contain only numeric values and blanks, or only text values and blanks. If Count is larger, text or other populated nonnumeric entries may account for the difference. Use the source data to identify what those entries mean before changing the aggregation.
When a result seems incorrect, work through these checks:
- Confirm which field is in the Values area and which summary calculation is selected.
- Inspect the source values for blanks, text-formatted numbers, error values, and formulas that return empty strings.
- Refresh the PivotTable after changing the source data. In desktop Excel, use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh.
- Check whether the PivotTable source range includes all intended rows. If new records were added outside a fixed source range, they may not appear until the source is updated.
- For a PivotTable based on the Data Model or an external source, check the available aggregations and model definitions; they can differ from a standard range-based PivotTable.
Do not “fix” a low count by converting every text entry to a number without checking its meaning. A text note such as “not applicable” should not necessarily become zero: zero is a real numeric observation and will be counted by Count Numbers. Decide how missing, invalid, and not-applicable values should be represented in the source data, then make the summary calculation match that rule.
When another approach is better
If neither Count nor Count Numbers answers the reporting question, adjust the source or choose a different summary. For a record count, use a populated identifier. For a count of unique customers or products, use a distinct-count option when supported, or prepare a separate summary of unique values. For a count that meets additional criteria—such as numeric values above a threshold—cleaning or adding a clearly defined helper field to the source may make the PivotTable easier to audit.
These alternatives matter because a PivotTable’s aggregation is not a data-cleaning step. It does not decide whether “N/A” means missing, whether a repeated ID is a duplicate, or whether text that resembles a number should be converted. Those are data-definition questions. Keeping the source values consistent makes the PivotTable easier to explain and reduces surprises when new records are added.
Frequently Asked Questions
Does Count in a PivotTable include text?
Yes. Count includes populated text entries as well as populated numeric and date entries in the selected field. It excludes blank entries, so it counts populated values rather than every source row automatically.
Does Count Numbers include zero?
Yes. Zero is numeric, so Count Numbers includes it. A blank cell is excluded. If zero means “not recorded” in your process, consider correcting or clarifying the source data rather than treating zero and blank as interchangeable.
Why is Count higher than Count Numbers?
Count may be including populated text entries that Count Numbers excludes. Text-formatted numbers and status notes are common reasons. Inspect the source field to see which entries account for the difference.
How do I count all rows in a PivotTable group?
Put a field that is populated on every source record, such as a transaction ID, into Values and summarize it by Count. If that field has blanks, the result counts only records with a populated ID, not every row.
Is Count Numbers the same as a distinct count?
No. Count Numbers counts every numeric entry, including duplicates. Distinct Count counts unique values and is a separate option that depends on how the PivotTable was created and its data source.
Related
- Can You Insert an Image Into an Excel Cell?
- Can You Delete Duplicates in Excel? Methods and Tips
- Delete Multiple Tabs in Excel at Once: How It Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Analysis ANOVA: Run and Interpret TestsTo run an ANOVA in Microsoft Excel for Microsoft 365 on Windows, enable the Analysis ToolPak, select Data…
- Excel Hide Specific Columns: Steps and TipsTo hide specific columns in Excel for Microsoft 365 on Windows, select the column letter or letters, then…
- Track Changes in Word in Red: Step-by-Step GuideTo track edits in red in Microsoft Word, turn on Review > Track Changes, then open the advanced…
- AG Grid Insert Row: APIs, Examples, and Row Model TipsTo insert a row in AG Grid’s Client-Side Row Model, call gridApi.applyTransaction({ add: [newRow], addIndex: 0 }); omit…
- Excel Decrease Decimal Shortcut: Steps, Mac, and FixesIn Excel for Microsoft 365 on Windows, select the cell or cells and press Alt, H, 9 to…
- Large Excel File Won’t Open? Causes and Practical FixesIf a large Excel file won’t open, first copy it to a local drive, close other programs, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.