Why do I Get a Count Instead of a Sum?

November 30, 2022 - by Bill Jelen

Problem: When I choose Revenue, it goes to the Rows instead of the Values area. When I drag Revenue to the Values area, it defaults to a Count instead of a Sum. As of 05/2018, this has been fixed in Office 365!

Strategy: There are two common causes for this. The second most common cause is that you have a few blank cells in the revenue column. More likely: you are selecting all of columns A:H before creating a pivot table instead of selecting a single cell. This technique allows you to paste new records below the data later and you can simply Refresh. But this method is adding a million blank cells to the data range.

If you are selecting the entire column before creating the pivot table, the next topic will allow you to paste new records and have them automatically become part of the pivot table. If you start using a Table as the source, you won’t have to select the entire column anymore and you can simply checkmark Revenue.

Additional Details: Let’s say you want to add new records once a month. After pasting the records, you can use the Change Data Source button on the Analyze tab. Change the data address from ending in 564 to the new last row and click OK. This whole process takes less than 10 keystrokes and is far less hassle than have Revenue appear in the wrong spot.

Alternate Strategy: If the problem is caused by a few blank cells in the revenue column, follow these steps:

1. Select all of the cells that should be numeric.
2. Ctrl+H to display Find & Replace
3. Leave the top box blank. Type a 0 in the second box. Click Replace All.

You get a Count if there are blank cells in a Revenue column. Use Find & Replace. Leave the Find What box empty. Change the Replace With box to 0. Click Replace All. — Figure 838. People in my seminars convinced me this is easier than Go To Special, Blanks.

This article is an excerpt from Power Excel With MrExcel

Title photo by Hello I'm Nik on Unsplash