MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


May 08, 2019 - by Bill Jelen

Excel Why Do Pivot Tables Count Instead of Sum?. Photo Credit: Majkl Velner at Unsplash.com

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


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.