Microsoft Excel Tutorial: Count instead of Sum in a Pivot Table.
Welcome back to the MrExcel netcast! In this episode, we will be discussing a common issue that many Excel users face when creating pivot tables. Have you ever noticed that instead of getting a sum of your data, you end up with a count? Well, in this podcast, we will dive into why this happens and how to fix it.
First, let's address the two main reasons why this may occur. The first reason is that there are blank cells in your data. This can happen if you have a few blank cells in your revenue column. The second reason is that instead of selecting just one cell in the pivot table, you select all the cells. While this may seem like a convenient way to add more data later, there is a better solution.
To fix this issue, you can simply format your data as a table by using the shortcut [ctrl T]. This will allow you to add more data later without affecting the sum function in your pivot table. However, in the past, this solution did not always work. But, I have some exciting news to share with you. In the latest version of Office 365 (version 1805), this issue has been fixed!
I was pleasantly surprised to see that the sum function was working correctly in my pivot table. I even went back to Ash, who was featured in a previous Wednesday tip, to confirm this improvement. She informed me that someone had reported it as a bug and it has now been fixed. The issue was that a mix of numbers and blank cells was being interpreted as text. Now, with the new update, it will be interpreted as numbers, giving you the correct sum in your pivot table.
However, there is one thing to keep in mind. If you clear cells by typing spaces instead of using the [delete] key, it will still result in a count instead of a sum. So, make sure to use the [delete] key to clear cells and avoid this issue.
I hope this tip has been helpful for you. If you want to learn more useful Excel tips, check out my book "MrExcel Live: The 54 Greatest Tips of All Time". Thank you for tuning in to this episode of the MrExcel netcast. Don't forget to subscribe and hit the notification bell to stay updated on our latest episodes. See you next time!
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube/
#excel
#exceltutorial
#exceltricks
#excelnew
#evergreen
#pivottable
#pivot_table
#excelpivot
#excelpivottablestutorial
This video answers these common search terms:
why is excel counting instead of summing
how to change count in excel to sum
how to change from count to sum in excel
how to do count in excel pivot table
how to add count to a pivot table in excel
what is the count in excel pivot tables
how to change count of in excel pivot
how to convert pivottable count to sum in excel
how to create a pivot table in excel to count
how to create pivot tables in excel with counts
changing count to sum on excel
Table of Contents:
(00:00) Issue with Pivot Table counting instead of summing
(00:18) Issue with getting a count instead of a sum for revenue
(00:28) Blank cells or selecting all cells in pivot table
(00:57) Checking for fix in Office 365 version 1805
(01:26) Fix also present in version 1804
(01:41) Explanation from Ash about the fix
(02:00) Fix also works for multiple columns
(02:23) Recap of the fix
(02:57) Tips for fixing the issue
(03:18) Clicking Like really helps the algorithm
For decades, some pivot tables give you a Count instead of a Sum. The problem was usually caused by a few empty cells in your data. Starting in version 1804 of Office 365, the problem is fixed.
To read more about this: mrexcel.com/excel-tips/empty-cells-in-pivot-table-values-column/
Excellers have been annoyed by Count of Revenue in pivot tables for years
It happens when you have one or more empty cells in the revenue column
Starting with version 1804 of Excel in Office 365, the behavior is fixed.
You will still get a count if someone puts spaces instead of a number.
Join the MrExcel Message Board discussion about this video at mrexcel.com/board/threads/1154024/
- Excel - Counting Instead of Summing in Pivot Tables - Episode 2195 ( Download)
- Excel - Why Is my Pivot Table Counting Instead of Summing - Episode 2001 ( Download)
- Excel: Pivot Table Hack - Display Actual Data Instead of Sum or Count ( Download)
- Two Reasons Your PivotTable Counts Instead of Sums Values ( Download)
- Excel Pivot Table: How to change Count to Sum ( Download)
- Excel Tip Why Is My Pivot Counting Not Summing ( Download)
- excel showing count instead of sum ( Download)
- How To Remove Sum of From Pivot Tables I Excel 2023 Tutorial ( Download)
- Change Calculation to Count of Items in Pivot Table. Change from Sum to Count. #howto #msexcel ( Download)
- Fix: Excel showing count instead of sum ( Download)
- Pivot tables values show 0 but we want to count items ( Download)
- How to Change Sum to Count in Value field in Pivot Table in MS Excel 2016 ( Download)
- Pivot Table trick - How to fix Problem if Pivot Table calculating COUNT instead of SUM ( Download)
- Pivot Table in Excel | How to Sum in Excel Using Pivot Table ( Download)
- Excel Sum Challenge: Sum Some Trailing Minus Numbers - Episode 2201 ( Download)
