Excel - Why Is my Pivot Table Counting Instead of Summing? - Episode 2001

3,799 views · Published 27 August 2016 · 5:00 · Indexed 30 October 2026

Channel: MrExcel.com · 2016 · Education

Watch on YouTube

Microsoft Excel Tutorial - Why Does Pivot Table Count instead of Sum?
Update: Microsoft fixed this problem in version 1804. See: https://youtu.be/rj1nrzTURbI

Welcome to the MrExcel netcast, where we dive into all things Excel. In this episode, we'll be discussing a common issue that arises when working with Pivot Tables - why do some fields show a count instead of a sum? I'm Bill Jelen, and I'll be your guide as we explore this topic.

If you've ever been in a room full of accountants, you may have experienced this scenario. You insert a Pivot Table, add a field, and everything seems to be working fine. But then, someone raises their hand and asks why they're getting a count instead of a sum. This is a common problem, and it usually comes down to one of two reasons.

The first reason is that there are empty cells or text in the data. This can throw off the calculations and result in a count instead of a sum. The solution to this is to select all the cells that should be numeric, use the Go To Special function, and fill in the blanks with 0. This will ensure that the data is all numeric and the Pivot Table will work correctly.

The second reason is that many people have been taught to select the entire column when creating a Pivot Table, instead of just the solid data. This is often done to account for future data being added. However, this can also result in counts instead of sums. To avoid this, I recommend using Tables (CTRL T) to format your data. This will create an End Of Table Marker, which tells the Pivot Table where the data ends. When you add more data, the marker will move, and a simple refresh will update the Pivot Table with the new data. No need to go through the tedious process of changing the data source.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Common issue with Pivot Tables and how to fix it
(01:03) Solution for empty or text cells in Pivot Tables
(01:47) Main reason for Pivot Tables counting instead of summing
(02:24) Alternative method for creating Pivot Tables
(03:00) Using the End of Table Marker to add more data
(03:56) Refreshing the Pivot Table to include new data
(04:22) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #excelbug #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #pivottable #pivot_table #excelpivot #excelpivottablestutorial 

This video answers these common search terms:
pivot table count instead of sum
excel pivot table aggregation issue
why is my pivot table counting instead of summing?
troubleshooting pivot table counting behavior
excel pivot table sum vs. count
understanding pivot table aggregation methods
how to change pivot table from count to sum
summing data in pivot table instead of counting
pivot table aggregation settings
fixing pivot table counting error in excel

Why does the revenue field in your pivot table always Count instead of Sum? It is one of two reasons. Both reasons, along with workarounds, are found in this episode. Recap:
In a perfect world, numeric fields will Sum in a pivot table
Why do they sometimes Count?
Reason 1: Empty or text cells
How to Fix reason 1: Go To Special Blanks. 0. Ctrl+Enter
Reason 2: You are selecting the whole column
You are selecting the whole column so you can add more data later
Ctrl+T to the rescue
Notice the end of table marker

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153828/

More from this channel