Excel 2013

[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

 

[isabelle]

hi Alejandro,

http://office.microsoft.com/en-ca/excel-help/sous-total-sous-total-fonction-HA102752856.aspx

Function_num Required. The number 1 to 11 (includes hidden values)
or 101 to 111 (ignores hidden values) that specifies which function to
use in calculating subtotals within a list.

isabelle

Le 2013-10-15 15:25, Alejandro Romero a écrit :

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 as well.

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).