Excel 2013: AutoFilter and SUM()

[Alejandro Romero]

Hello all,

It seems in the newer than 2003 versions of Excel, when an autofilter is applied to a column, and you try to do an auto sum or select a range while filling out a sum function, it pulls everything in between what is showing aswell.

Yes, the subtotal(9, RANGE) option works, but only while it is filtered.

The end goal I am trying to create, without a macro, is a summary sheet where it shows monetary totals for a certain classification. The classifications are not in the same range, as they are normally sorted by date.

Example:
Item Class Cost
Widget A 4 $1,000
Widget B 4 $1,000
Widget C 5 $1,000
Widget D 6 $1,000
Widget E 5 $1,000
Widget F 4 $1,000

If I only want to add the Class 4's up by filtering only for the 4's and selecting the range, it would pull everything in between. Back in the old days, it would grab just those cells for the formula so that it looked more like this: sum(C2,C3,C7) instead of sum(C2:C7).

The temporary workaround I have found is to CTRL+Click each cell I want in the formula. This, however, is very time consuming.

Is there another way around?

Thank you in advance,
-=Alejandro

 

[Alex Plantema]

Alejandro Romero schreef in

Example:
Item Class Cost
Widget A 4 $1,000
Widget B 4 $1,000
Widget C 5 $1,000
Widget D 6 $1,000
Widget E 5 $1,000
Widget F 4 $1,000

If I only want to add the Class 4's up by filtering only for the 4's
and selecting the range, it would pull everything in between. Back
in the old days, it would grab just those cells for the formula so
that it looked more like this: sum(C2,C3,C7) instead of sum(C2:C7).

Use either SUMIF or a pivot table.