SUM, AVERAGE, COUNT and their conditional equivalents are staples in Microsoft Excel and Google spreadsheets. However, there is a seldom-used alternative that allows users to incorporate those functions and a few others in one line: SUBTOTAL. Here’s how it works:
How to Use SUBTOTAL in Google Sheets
Say we had a two-day sale, specifically a Valentine’s Day sale. We wanted to compare how each store location did overall in 2018 and the year before. We want to see the sums and averages for each year.
Of course, the SUM function is the easiest to do here. Just highlight the total sales and get a number, $8,455 in this case. Since we also wanted the average, we calculated that as $705.
We transferred the formula to the next section to get total sales of $6,734 and an average of $561 in 2018.
Now, we want a grand total for both, and we accidentally sum all of the sales numbers, including subtotals, which leads to a larger total that’s incorrect.
Obviously, the total of $30,378 looks suspicious, but in haste, we click enter and run with it.
Now, we want to achieve the same results with SUBTOTAL, but there is something special about this function. SUBTOTAL takes at least 2 arguments:
- Function_code: this is the trick that allows the function to do what you need it to do. We will discuss this later, but for this first exercise, we will need codes 1 and 9
- Range: This is either the row or column, but you can add as many more ranges as needed in arguments 3 and beyond.
Since we need to sum to sales numbers, function_code will be 9 in this case:
=SUBTOTAL(9,I2:I13) yields $8,455 just like the sum did. For the average, function_code 1 gives the average of $705.
So far, the calculations are the same. But when we total the entire column of numbers like we accidentally did with SUM, here’s the major difference:
=SUBTOTAL (9,I2:I29) gives $15,189! This is major difference with the SUM function. Because it recognized that the other two subtotals were used, it doesn’t add them to the grand total calculation, yielding a more accurate number.
SUBTOTAL is Dynamic
The function_code parameter is based on this table, which shows 11 different functions SUBTOTAL can perform, including performing variance and standard deviations on entire populations.
But, there’s another great characteristic that works in its favor.
If we implement a standard filter, this allows us to implement quick changes to the tables so that we can see different subsets of the data. If we wanted to show sales that were lower than $450:
Here’s what happens with the totals calculated with SUM/AVERAGE and SUBTOTAL:
The SUBTOTAL numbers adjust while the SUM/AVERAGE ones stay the same. This shows that SUBTOTAL is more dynamic than SUM/AVERAGE, making it far more useful when dealing with filtered data!
There are plenty of reasons why we use SUM/AVERAGE/COUNT; we’re used to them and they are like second nature. However, when you take a look at the versatility of this SUBTOTAL function, it’s a good idea to find ways to integrate it into your sheets. It can save time and reduce frustration.
If you want to make it even more powerful, check out how to combine SUBTOTAL with MATCH and other functions:
Quick Note: HERE is the link to my post referenced in the video