Doing a simple summation in Excel is as easy as clicking on the AutoSum button or just using the SUM function. But in some cases, you don’t want to be summing up everything within a range. In those situations, you may want to use the SUBTOTAL function instead. In this post, I’ll go over how that function works, and illustrate the differences between it and SUM function.
What’s the difference between SUM and SUBTOTAL in Excel?
Suppose you have the following data set, which sows airport delays by carriers at different airports:
Using the SUM function on column header for carrier_delay, the total value comes out to 133,453,066. Even if you were to filter the data based on a single airport (in this example, JFK), the total value would remain the same:
If you were to use the SUBTOTAL function, however, then it would only perform a calculation on the cells that are visible and filtered. If you’re using SUBTOTAL, you just need to specify the type of calculation you want to perform:
To do a summation, you just enter 9 for the first argument. As you can see, there are options to do COUNT, COUNTA, MAX, MIN, PRODUCT, AVERAGE, PRODUCT, standard deviation, and variance calculations. Once you specify the first argument, all you need to do after that is select the range as you would in a normal SUM formula. Here’s what the SUBTOTAL formula looks like in my spreadsheet, where I am adding up the values in column Q:
=SUBTOTAL(9,Q7:Q500000)Now, there’s a difference between the SUM and SUBTOTAL formulas in their results:
If you were to change your filters and selections, the SUBTOTAL value would change while the SUM value would remain the same.
If you liked this post on When to Use Subtotals In Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.