Excel 2020: Why Do Pivot Tables Count Instead of Sum?

May 11, 2020 - by Bill Jelen

In almost every seminar, someone asks why pivot tables default to count instead of sum. This long-standing problem was fixed in May 2018 for Office 365 subscribers. The Count was triggered if you had one revenue cell that contained text or an empty cell.

Someone wrote a letter to the Excel team complaining that a single empty cell should not be treated like text. If a cell is blank and you refer to that cell in a formula, Excel treats the cell as a zero. The letter-writer pointed out that a columns with mostly numbers and a few empty cells should not trigger a Count. The person on the Excel team agreed, and quietly pushed out a change.

If you are not using Office 365, then you can avoid the Count issue by making sure that there are no blank cells in your revenue column. If you don't think that you have any blank cells, make sure you are selecting one cell in your data set and not the entire columns A:J. If your data is in A2:J999 and you select A:J, you are selecting 998 numbers and over a million empty cells.

Title Photo: Majkl Velner at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.