Excel - How to use the Subtotal Function with Filters

We love to use Filters in Excel. When filtering data, the filtered rows turn blue. To get the SUM, AVERAGE, MAX, MIN, or COUNT of the filtered data, use the SUBTOTAL function. If you use one of the functions with one argument - SUM, AVERAGE, MAX, MIN, or COUNT - you'll end up with the incorrect answer. For example, when filtering data and you want the highest value, the MAX function is incorrect. You want to use =SUBTOTAL(4,cell range). You don't want the highest value if it is hidden. This is why you use the SUBTOTAL function.

Chris Menard's blog post

Chapters:
0:00 Intro
0:35 Subtotal Sum
2:30 CTRL + ' copies formula
3:07 ATL + Down arrow

The SUBTOTAL function has two groups. Group 1 is 1 through 11, and Group 2 is 101 to 111. The SUBTOTAL function can return hidden or ignore hidden values, but it doesn't matter which group you use when used with Filters. The first group, 1 to 11, will include hidden, and 101 to 111 will ignore hidden. But as mentioned, with filtered data, it doesn't matter which group is used.

Syntax of the SUBTOTAL function
SUBTOTAL(function_num,ref1,[ref2],...)

Arguments for the SUBTOTAL function
Function Include hidden Ignore hidden
AVERAGE 1 101
COUNT 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUCT 6 106
STDEV 7 107
STDEVP 8 108
SUM 9 109
VAR 10 110
VARP 11 111

#msexcel #chrismenard #microsoftexcel #exceltips

And make sure you subscribe to my channel!

-- EQUIPMENT USED ---------------------------------

-- SOFTWARE USED ---------------------------------

DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[einoconsult]

Thanks Chris. I always wondered what's the difference between 1 and 101, must be something, no ? Also, I use ALT + DOWN ARROW for expanding a dropdown but glad to see that it works here as well...
EDIT : 1 vs. 101 will be addressed in your next video, looking forward

[age_of_reason]

How do you subtotal based on criteria (without filtering)? Say you want to find the total from GA when Dept is training? I want to add up two different subtotals and find their difference. I need formulas for this since filtering would take too long.

[tommyharris]

How do you calculate the Median from the filtered list?

[gryranfelt]

And then the question is ... what if you want sums of rows that are filtered and you want to KEEP that sum static while filtering.

[Pemenari]

love the clear instructions. just too bad it doesn't fucking work for me anyways