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

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

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.