Filters are a powerful and piece of cake-to-use feature. Using filters, y'all can quickly limit data to simply the records you need to see. Summing filtered records is some other thing. Yous might try a SUM() function simply you lot might become a surprise–well, I tin can promise you'll get a surprise.

The figure bellows shows a filtered list. Y'all tin tell by the row numbers to the left that many rows are hidden. (We'll skip how the actual filter works. To learn more most that, read How to utilize And and Or operators with Excel'due south Advanced Filter.

The adjacent figure shows what happens when you endeavor to sum the filtered values. You tin can easily tell that the result isn't correct; the value is besides high, but why? The SUM() office is evaluating all the values in the range D14:D64, non simply the filtered values. There'due south no way for the SUM() function to know that you want to exclude the filtered values in the referenced range.

The solution is much easier than you might call back! Simply click AutoSum–Excel will automatically enter a SUBTOTAL() function, instead of a SUM() function. This part references the entire list, D6:D82, but it evaluates only the filtered values.

Automatically sign up today!

About SUBTOTAL()

Although the SUBTOTAL() function references the entire list of values in column D, it evaluates merely those in the filtered list. You might think that's because of the first statement, the value ix. This argument tells Excel to sum the referenced values. The post-obit tabular array lists this argument'south acceptable values:

Evaluates subconscious values Ignores hidden values Function
i 101 AVERAGE()
2 102 COUNT()
3 103 COUNTA()
four 104 MAX()
5 105 MIN()
6 106 Product()
7 107 STDEV()
eight 108 STDEVP()
9 109 SUM()
10 110 VAR()
11 111 VARP()

At this point, you might be saying, Wait a minute! The value 9 is supposed to evaluate hidden values. Shouldn't the correct argument be 109? It's a valid question and I take an explanation, I only don't think it's a great explanation: SUBTOTAL() ignores rows that aren't included in the result of a filter, regardless of the argument you specify. It'south a quirk–simply ane of those little details you need to know near the function. Whether y'all apply ix or 109, SUBTOTAL() will evaluate only the visible values–it will not evaluate hidden values.