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

[Guest]

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

 

[Bernie Deitrick]

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:H26331>0)*G3:G26331))

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

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

 

[Domenic]

Try...

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

Hope this helps!