Excel 2013: AutoFilter and SUM()

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
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top