Excel SUBTOTAL: Comparing to SUM, Using with IF, and More!

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

If you’re accustomed to working with numbers in Excel, then you’re no stranger to the SUM function. But if you haven’t learned to master the power of SUBTOTALs, you’re missing out.

The SUBTOTAL function allows you to perform calculations such as count, sum, average, and more, on a specified range of cells within your worksheet.

It’s a versatile tool that you can pair with other conditional functions for specific aggregations on a subset of your data.

In simple terms…it’s awesome!

Why Use SUBTOTAL Instead of SUM?

school supply list before calculations

The SUM function serves as a go-to tool, allowing easy addition of a series of numbers. However, when your data set includes hidden rows or filtered data, SUM falls short as it totals every number in the range irrespective of visibility.

Enter the SUBTOTAL function. This versatile feature performs a range of operations—like SUM—on visible rows only, making it indispensable for dynamic data analysis. Especially useful when dealing with outline groups or subsections within your data, SUBTOTAL automatically adjusts calculations when you hide rows or apply filters, ensuring the integrity of your totals.

Imagine you’re a third-grade teacher who tracked your school supplies purchases for the preparation of a new school year. By using the SUM function, you see the total expense equaled $697.09. However, if you filtered your list on just the purchases made in August, you’ll notice the SUM didn’t change. But the result using SUBTOTAL provides an accurate calculation of your subset.

SUM vs SUBTOTAL result

By using SUBTOTAL, you’re choosing a function tailored for dealing with complex datasets that change frequently. The subsections and filtered views of your work remain intact, with SUBTOTAL offering a clear lens through which to examine your numbers, adapting as you explore your data’s different layers.

Understanding the SUBTOTAL Function

Now let’s get to the good stuff! Here’s the basic syntax used to perform a subtotal:

=SUBTOTAL( function_number, range, [optional range], … )

The function number argument is an integer that signifies a specific aggregation.

1 – AVERAGE

2 – COUNT

3 – COUNTA

4 – MAX

5 – MIN

6 – PRODUCT

7 – STDEV

8 – STDEVP

9 – SUM

10 – VAR

11 – VARP

However, there are alternative function numbers to handle aggregations on hidden rows. These range from 101 to 111. By default, if a row is hidden, SUBTOTAL will still include it in the calculation.

This is not the same as a filtered row! But to treat hidden rows the same as filtered rows, you would use the alternate function number. For instance, instead of 1 for the SUM, the argument would be entered as 101.

What Is the SUBTOTAL 9 in Excel?

To clarify the distinction, let’s look at an example using a SUM aggregate of SUBTOTAL; represented by the number 9.

Calculating the SUM on just the purchases made in the school supply list, I’ll hide the rows for Erasers and Stickers, in rows 11 and 12. You can see that even using the SUBTOTAL function does not pick up on the missing (hidden) rows.

subtotal 9 hidden rows

But when I change the SUBTOTAL formula to account for hidden rows (using 109), the calculation adjusts correctly.

subtotal 109 hidden rows

So, if you notice that your average, sum, count, or other type of calculation isn’t adding up, remember to double-check your function number as filtered rows and hidden rows are treated differently.

How Do You Use SUBTOTAL with IF?

Applying an IF condition to your SUBTOTAL function is really easy. Let’s say we have a set average per item group we can spend on school supplies. The principal has allotted $50 per item group (Crayons, Chalk, Glue, etc.) and however we split it up, it our discretion.

In this example, we’ll determine what the total average that each item group cost, and if it’s over the $50, we’ll return “Over Budget!”. If not, then we’ll return a happier text string. Although there is a caveat. Calculators are not required to be included since the school received a grant to cover their cost.

The syntax is easy as you can see from the image below.

subtotal average with if

I simply wrapped the SUBTOTAL average function in an IF statement. The condition I’m checking for is whether the average subtotal is greater than $50. And since I filtered out the Calculators in row 10, my calculation adjusted for the missing cost and ultimately lowered the average.

Now, depending on the complexity of what you’re trying to do, either in the aggregation or conditions, a better option may be to use a variation of SUMIF or SUMPRODUCT. So, keep that in mind.

Leave a Comment