Quick answer
If a PivotTable counts amounts rather than adding them, inspect the source column for text and mixed values. Then choose Sum in Value Field Settings and refresh the PivotTable.
Understand the problem before changing anything
Count tells you how many records have values; Sum tells you their combined numeric amount. A column containing currency symbols as text, imported spaces or mixed entries can make Excel choose a summary you did not expect.
Step-by-step checks
Check a small group
Compare the PivotTable with two or three source rows. Establish the expected sum before changing a large report.
Inspect the source column
Look for numbers stored as text, blank strings, headings repeated within data or inconsistent decimal separators. Changing cell appearance to Currency does not convert text.
Clean the amounts in a copy
Use an appropriate numeric conversion after confirming the source locale. Keep errors visible so they are not silently turned into zero.
Choose the right summary
Open Value Field Settings for the affected Values field and select Sum. Give the field a clear name so readers know it is an amount.
Refresh and reconcile
Refresh the PivotTable, check filters and compare its grand total with the cleaned source. If grouping still looks wrong, inspect relationships and duplicate records.
What to avoid
Switching Count to Sum without correcting source text can leave amounts omitted. Distinct Count is a different calculation and should not be used as a substitute for a financial total.
How to verify the result
Check the expected subtotal and grand total, not only the field label. A renamed field called Sum can still contain a calculation you did not intend.
Check the workbook’s structure as well as the visible result
Work on a separate copy when the workbook supports invoices, stock, payroll or a shared report. A cell’s appearance is only one layer: its stored value, formula, number format and validation rule can differ. Record the affected worksheet and one example cell so you can compare the same case after each change.
Use a small, known example to test the intended behaviour. Check one ordinary row, one blank and one unusual value rather than judging a whole report from its grand total. If the workbook imports data, correct the import or source definition where possible; repeatedly correcting the final display can leave the same problem waiting for the next refresh.
Before sharing the result, check formulas that depend on the changed area and any charts or PivotTables fed by it. Save, close and reopen a working copy, then verify the same example. Mention any deliberate conversion of text, dates or identifiers so another user does not reverse it accidentally. If a colleague owns the workbook, agree changes to validation, protection and calculation settings rather than silently changing the model.
Before you finish
- Affected sheet and sample cell
- Original value compared with expected value
- Source, formula and display checked separately
- Dependent reports tested after saving
Common question
Does applying number formatting convert text amounts?
No. Number formatting controls appearance. Convert the stored values deliberately and verify decimal separators before refreshing the PivotTable.
Need help with the next step?
Tell RJ Computers the device model, exact symptom, checks already tried and whether important files are backed up. Confirm availability, assessment charges and the scope of work before arranging a visit.
Reference and version notes
Check the official instructions for your exact product version; menu names and available features can differ.
Official product documentation