Excel PivotTable Shows Count Instead of Sum: Fix the Source and the Value Field

Separate the summary choice from the source data type

This guide targets a normal worksheet-based PivotTable in Microsoft 365 Excel for Windows. It does not cover an OLAP cube or a custom Data Model measure, where field behavior and available calculations differ. The source is a five-row Table with record_id, region, and an amount_text field imported as text.

The two checks are independent. A clean numeric amount field can still have its summary set to Count from an earlier configuration. Conversely, a field set to Sum can omit text amounts and produce a misleadingly small result. Inspect both the field setting and the underlying source values instead of assuming that the visible heading tells you the data has been converted correctly.

Create an intentionally inspectable source

Create an Excel Table named AmountSource from pivot-amounts-input.csv. Preserve the first two fields as Text. For a controlled reproduction of numeric-looking text, format the third column as Text before entering its four nonempty values, or import it explicitly as text. Leave the fifth amount cell genuinely empty, rather than typing a space or a formula returning an empty string.

Add a temporary helper column headed is_number and enter =ISNUMBER([@amount_text]). It should return FALSE for the four text values and for the missing value. Alignment and number formatting are not reliable substitutes for this check. Keep the raw column while converting a copy so you can verify that no amount, zero, or missing record was silently lost.

Convert a copy without turning the missing amount into zero

Add a new Table column named amount_clean. For this fixture, the declared decimal separator is a dot and the grouping separator is a comma. Use the formula below to convert valid amount text while leaving missing input visibly empty. It intentionally lets invalid nonempty text raise an error instead of converting it to zero.

A formula result of an empty string is not the same as a genuinely empty source cell. Before using amount_clean for the final report, copy its results and Paste Special > Values into a new Table column named amount. Filter AmountSource for blank amount_text values and use Clear Contents on their corresponding amount cells; do not delete those source rows. Remove the filter and verify the missing R5 amount is truly blank.

=IF([@amount_text]="","",NUMBERVALUE([@amount_text],".",","))

Build or repair the PivotTable calculation

Use the numeric amount column after the conversion check, not the original amount_text field. For an existing PivotTable, confirm its source includes that column and all five records, then refresh before changing its value setting. Updating cells outside the existing source range does not make them part of the report.

1. Click inside AmountSource and select Insert > PivotTable, placing it on a new worksheet. For this walkthrough, leave Add this data to the Data Model unchecked.
2. Place region in Rows and amount in Values. If the value field displays Count, open its menu and choose Value Field Settings.
3. On Summarize Values By, select Sum. On Show Values As, use No Calculation for these ordinary totals. Apply a number format with two decimal places if desired.
4. Add record_id as a second Values field and set that field to Count. It should count the five nonempty record IDs, including the record whose amount is missing.
5. Compare North 30, South 5, and Grand Total 35 with pivot-summary-expected.csv. The record counts should be North 2, South 3, and total 5.

Validate zero, missing values, and rejected text

R3 contains a recorded zero. It belongs in the four recorded amounts even though it contributes nothing to the sum. R5 has no recorded amount and belongs in a separate missing-value count. Replacing R5 with zero would leave the total at 35 while falsely increasing the number of recorded amounts to five.

On the final amount source column, COUNT should be 4 and SUM should be 35. On record_id, COUNTA should be 5 because all record identifiers are supplied. For a negative test, replace an input amount in a copy with pending. The conversion should fail visibly. A broad IFERROR(...,0) would hide that rejected value among legitimate zeros and make the report difficult to audit.

Make recurring conversions refreshable

The helper-column path is useful for a small one-time repair because every cell can be inspected. For repeated imports, use a Power Query transformation that keeps source text, normalizes the agreed blanks to null, and applies an explicit number culture. Load the cleaned result to a new Table, then refresh that query before refreshing the PivotTable that depends on it.

Whichever route you use, keep the raw source and the rejected-value review together. A file with 1.234,50 needs a different numeric convention from this fixture. Do not strip all commas or punctuation as a universal fix. That could make a value parse while changing its magnitude. Use the source system specification and a known record to confirm the interpretation.

Check what changed when the problem returns

If the PivotTable returns to Count after the field is removed and added again, inspect the refreshed source column for text, mixed values, and the current summary choice. Do not assume this means Excel changed a global preference. A new export may contain a textual placeholder or an empty-string formula that was absent from your first sample.

If Sum is selected but the number is still wrong, check source coverage, report filters, hidden field selections, and duplicate transactions. Refreshing does not decide which records should belong to a total. For a simple refresh test, add R6, North, 7.50 inside the source Table, run the same conversion, and refresh the report. Expected total becomes 42.50 with North 37.50, South 5, six records, and five recorded amounts.