How to Sum-If when the cells to sum are Auto-filter visable cells?
 

Using 2003

How can I change next below
=SUMIF(F3:F26331,"-SPLIT-",G3:G26331)

To effectively:

=SUMIF(F3:F26331,"-SPLIT-",Subtotal(9,G3:G26331))

In short, the items to sum will change as I select choices in the via
Autofilter.

TIA Dennis

How to Sum-If when the cells to sum are Auto-filter visable cells?
 

Dennis,

Simplest is to use the SUBTOTAL function on G, and then just filter one more
step, for -SPLIT- in column F.

If you really want to continue along your path, you would need a helper
column. In H3, use the formula

=SUBTOTAL(9,G3)

And copy down to match column G.

Then use the formula

=SUMPRODUCT((F3:F26331="-SPLIT-")*(H3:H263310)*G3:G26331))

HTH,
Bernie
MS Excel MVP



"Dennis" wrote in message
...
Using 2003

How can I change next below
=SUMIF(F3:F26331,"-SPLIT-",G3:G26331)

To effectively:

=SUMIF(F3:F26331,"-SPLIT-",Subtotal(9,G3:G26331))

In short, the items to sum will change as I select choices in the via
Autofilter.

TIA Dennis

How to Sum-If when the cells to sum are Auto-filter visable cells?
 

Sorry. If you can have negative numbers, you will need to change the 9 to 2
or 3 in the subtotal function in the helper column.

Bernie


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Dennis,

Simplest is to use the SUBTOTAL function on G, and then just filter one
more step, for -SPLIT- in column F.

If you really want to continue along your path, you would need a helper
column. In H3, use the formula

=SUBTOTAL(9,G3)

And copy down to match column G.

Then use the formula

=SUMPRODUCT((F3:F26331="-SPLIT-")*(H3:H263310)*G3:G26331))

HTH,
Bernie
MS Excel MVP



"Dennis" wrote in message
...
Using 2003

How can I change next below
=SUMIF(F3:F26331,"-SPLIT-",G3:G26331)

To effectively:

=SUMIF(F3:F26331,"-SPLIT-",Subtotal(9,G3:G26331))

In short, the items to sum will change as I select choices in the via
Autofilter.

TIA Dennis

How to Sum-If when the cells to sum are Auto-filter visable cells?
 

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F3:F26331,ROW(F3:F26 331)-ROW(F3),0,1)),--(F
3:F26331="-SPLIT-"),G3:G26331)

Hope this helps!

In article ,
Dennis wrote:

Using 2003

How can I change next below
=SUMIF(F3:F26331,"-SPLIT-",G3:G26331)

To effectively:

=SUMIF(F3:F26331,"-SPLIT-",Subtotal(9,G3:G26331))

In short, the items to sum will change as I select choices in the via
Autofilter.

TIA Dennis