Excel 2013: AutoFilter and SUM()

  • Thread starter Thread starter Alejandro Romero
  • Start date Start date
A

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
 
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).
Click to expand...

Use either SUMIF or a pivot table.
 
Back
Top