Sign up for our Excel webinar, times added weekly: [ Ссылка ]

Learn why the pivot table calculation type defaults to Count sometimes, and how to fix it to default to Sum.

[ Ссылка ]

One of the most common questions I see on my free 3-part video series on pivot tables & dashboards ([ Ссылка ]) is, “Why does the pivot table default to Count instead of Sum when I add a field to the values area?”

Well, Pivot Tables have some rules on which calculation type will be selected when we add a field to the Values area.

The rules are pretty simple:

If ANY cell in the column (field) contains text, blanks, or errors (#N/A, #REF!, #VALUE!, #NAME?), then the calculation type will default to COUNT.

If ALL cells in the column (field) contain numbers, then the calculation type will default to SUM.

Now that we know the rules, we can prevent this error by cleaning up our data.

In the video above I show a data set that contains blanks in the Revenue column. Since there are blanks in this column, the pivot table calculation type defaults to Count.

One quick way to fix this is to replace the blank cells with a zero (0) value. Here are instructions on how to find & replace all blanks in a column.

1. Select all cells in the column or Table (keyboard shortcut: Ctrl+Space Bar).
2. Open the Replace Window from the Home tab on the Ribbon in the Find & Select menu (keyboard shortcut: Ctrl+H).
3. Delete everything in the Find What text box so it finds blank cells.
4. Type a zero 0 in the Replace With box.
5. Press the Replace All button (keyboard shortcut: Alt+A).
6. Refresh the pivot table (keyboard shortcut: Alt+F5).
7. Add the field to the Values area of the pivot table.

The calculation type should default to a Sum calculation if all cells in the data source column are numbers.

Blank cells are the most common cause that I see for calculation type defaulting to Count. However, cells that contain text or errors can also cause this problem.

If the source data column contains a formula that is returning errors, we can use an IFERROR function to return a zero instead of the error. Of course we will want to determine why the error is occurring in the first place.

My free 3-part video series on the lookup formulas ([ Ссылка ]) has an entire video dedicated to learning how to prevent and fix errors with formulas like VLOOKUP and INDEX/MATCH.

Sometimes values in cells look like numbers, but the numbers are stored as text. This is a common issue when you are importing data into Excel from another system. In this case we will need to convert the text to numbers first. Checkout my article on keyboard shortcuts to convert text to numbers ([ Ссылка ]) to learn more about these techniques.

At the end of the video I also explain how PivotPal can save time with this issue. PivotPal has it’s own rules for adding a field to the values area, and does NOT default to count if the column contains blanks.

If the column contains numbers and blanks, PivotPal will default the calculation type to Sum when we add the field to the values area using the PivotPal window. This is one way to get around this annoying issue without having to modify the source data.

The PivotPal Add-in is packed with features that will save you time when working with pivot tables in Excel. Please click the link below to learn more and give PivotPal a try today.

[ Ссылка ]
00:00 Introduction
01:16 Default to Count
02:25 Find and Replace
03:35 Refresh Table
04:30 Pivot Pal

свернуть