When you drag a value column to the value section of a pivot table, sometimes the default calculation is COUNT, not SUM. This is frustrating, but easily fixed.

The cause is either a blank cell or cells in the value range in the data, or there is a text entry or entries in the value range.

To fix the blank cell issue follow these steps.

1. Select the range involved – don’t select a whole column
2. Press the F5 function key and click the Special button
3. Select the Blanks option and click OK
4. Type a zero and hold the Ctrl key down and press Enter

This populates a zero in all the blank cells and this will stop the COUNT issue happening in the future.

To fix a text cell in a value range follow these steps (this assumes there are only values in the cells in the values range, not formulas. This also assumes that the values are not entered as text).

1. Select the range involved – don’t include the heading row and don’t select a whole column
2. Press the F5 function key and click the Special button
3. Select the Constants option and un-tick the Numbers option (see image below)
4. Type a zero and hold the Ctrl key down and press Enter

This populates a zero in all the text cells and this will stop the COUNT issue happening in the future.