Change Count to Sum for Reported Values in an Excel PivotTable
Question details
The user needs an Excel PivotTable to summarize reported laboratory test values instead of showing the count of values.
- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- A PivotTable uses the REPORTED VALUE field in its Values area to review laboratory test results by sample or test.
- Observed behavior
- The Values area displays Count of REPORTED VALUE rather than the expected numeric test-result totals or values.
Check that the REPORTED VALUE source column contains real numeric values rather than numbers stored as text. A PivotTable uses Count automatically when the field contains text, blanks, or mixed data types.
Change the REPORTED VALUE summary from Count to Sum
Use this when the reported values are numeric and you want the PivotTable to total them instead of counting populated records.
Click anywhere inside the PivotTable, then locate the PivotTable Fields pane and find REPORTED VALUE under the Values area.
Select the drop-down arrow next to Count of REPORTED VALUE in the Values area, then click Value Field Settings.
On the Summarize Values By tab, select Sum and click OK. The field label changes to Sum of REPORTED VALUE.
Right-click any cell in the PivotTable and select Refresh to update the summary using the current source data.
Clean or restructure source data when Sum is unavailable or misleading
Use this when Excel continues to count the field, when values are stored as text, or when multiple results exist for the same sample and test.
Summarize Reported Values with a PivotTable in WPS Spreadsheets
WPS Spreadsheets lets you build and edit PivotTables with familiar value-summary settings. Place your laboratory result field in Values, switch its calculation from Count to Sum, and refresh the report after cleaning numeric source data.
- 1. Open the worksheet in WPS Spreadsheets: Open the Excel workbook in WPS Office and click any cell in the PivotTable.
- 2. Locate the value field: In the PivotTable Fields panel, find REPORTED VALUE in the Values section.
- 3. Open the field calculation settings: Click the drop-down next to Count of REPORTED VALUE and choose Value Field Settings.
- 4. Set the calculation to Sum: Choose Sum under the summary options, then click OK to replace the count with the total reported value.
- 5. Refresh after source-data changes: Right-click the PivotTable and select Refresh after converting text results to numbers or separating result qualifiers.
Frequently Asked Questions
Why does my PivotTable show Count of REPORTED VALUE instead of Sum?
Excel uses Count when the REPORTED VALUE field includes text, blanks, mixed data types, or numbers stored as text. Open the value-field drop-down in the Values area, choose Value Field Settings, and select Sum after confirming the source values are numeric.
Can a PivotTable show the actual laboratory result instead of adding values together?
A PivotTable always summarizes records. Add Sample ID and Test Name to Rows and use Sum only when each sample-and-test pair has one numeric result. If multiple source records exist for the same pair, clean or restructure the data to create a unique result record.
How do I convert reported values stored as text to numbers in Excel?
Select the affected cells in the REPORTED VALUE column and use the cell warning icon to select Convert to Number. For clean numeric text, you can also select the column and use Data > Text to Columns > Finish.
What should I do with results such as ND or <0.01 in a PivotTable source column?
Keep numeric values in a dedicated numeric column and place qualifiers such as ND or < in a separate qualifier column. Mixed text and numbers in the same REPORTED VALUE field can cause the PivotTable to use Count.
