You can't use a SUM() function to sum a filtered list, unless you intend to evaluate hidden and unhidden values. Here's how to sum only the values that meet your filter's criteria.
Filters are a powerful and easy-to-use feature. Using filters, you can quickly limit data to just the records you need to see. Summing filtered records is another matter. You might try a SUM() function but you might get a surprise—well, I can promise you'll get a surprise.
The figure bellows shows a filtered list. You can tell by the row numbers to the left that many rows are hidden. (We'll skip how the actual filter works. To learn more about that.
The next figure shows what happens when you try to sum the filtered values. You can easily tell that the result isn't correct; the value is too high, but why? The SUM() function is evaluating all the values in the range D14:D64, not just the filtered values. There's 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 think! Simply click AutoSum—Excel will automatically enter a SUBTOTAL() function, instead of a SUM() function. This function references the entire list, D6:D82, but it evaluates only the filtered values.
About SUBTOTAL()
Although the SUBTOTAL() function references the entire list of values in column D, it evaluates only those in the filtered list. You might think that's because of the first argument, the value 9. This argument tells Excel to sum the referenced values. The following table lists this argument's acceptable values:
Evaluates hidden values Ignores hidden values Function
1 101 AVERAGE()
2 102 COUNT()
3 103 COUNTA()
4 104 MAX()
5 105 MIN()
6 106 PRODUCT()
7 107 STDEV()
8 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 have an explanation, I just 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's a quirk—just one of those little details you need to know about the function. Whether you use 9 or 109, SUBTOTAL() will evaluate only the visible values—it will not evaluate hidden values.
