Look Who it Was Random video

Excel - Understanding Subtotals in Excel: Why does it Sum or Count? - Episode 514

477 views · Published 1 September 2009 · 2:32 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Understanding Subtotals in Excel: Why does it Sum or Count?.

Welcome back to the MrExcel podcast! I'm Bill Jelen and today we're diving into the world of subtotals in Excel. But before we get started, I want to remind you all about our podcast 500 giveaway. We still have about 15 or 20 prizes left to give away, so if you entered, don't give up yet! We're giving away five prizes a day and it won't be long until everyone has received their prize. Thank you to everyone who entered and stay tuned for more exciting giveaways in the future.

Now, onto our topic for today. This question comes from someone who attended one of my power Excel seminars. They were confused about the automatic subtotals feature and why it sometimes decides to sum and other times it decides to count. Well, let me show you exactly how this works. We have a data set here with region, product, date, customer, quantity, revenue, cost of goods sold, and profit. When we use the data subtotals feature, it automatically wants to subtotal by the leftmost column, which in this case is customer. You can change this to whatever column you need, and it will use the sum function by default on the rightmost column, which in this case is profit.

But what happens when we have a different data set where the rightmost column is a sales rep name instead of numbers? This is where things get tricky. Excel doesn't know how to sum text, so it defaults to using the count function instead. This means that you have to pay particular attention to the function used in the drop-down menu when setting up your subtotals. If you see that your rightmost column is text-based, you'll need to change the function from count to sum in order to get the desired result.

So, the key takeaway here is to always check your data set before using the subtotals feature. If your rightmost column contains text, you'll need to make sure you change the function to sum in order to get accurate subtotals. I hope this explanation helps and thank you for tuning in to another netcast from MrExcel. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time!

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) Introduction to podcast and giveaway update
(00:32) Question from a seminar attendee about subtotals feature
(00:49) Explanation of how subtotals work with different data sets
(01:26) Difference in subtotals when using text in rightmost column
(02:00) Reminder to pay attention to function used when using subtotals
(02:11) Clicking Like really helps the algorithm

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


This video answers these common search terms:
Automatic subtotals feature
Changing function in drop-down menu
Count function in Excel
Excel data set
Excel seminars
Excel tips and tricks
Excel troubleshooting
Subtotal dialog box
Sum function in Excel
Text-based columns in Excel


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

Why does the automatic subtotals command sometimes choose to Sum and sometimes choose to Count? Episode 514 shows you why Excel seems to arbitrarily count or sum.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel