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

[Dennis]

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



"Dennis" <[email protected]> wrote in message
news:[email protected]...
> 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]

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
news:[email protected]...
> 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
>
>
>
> "Dennis" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

[Domenic]

Try...

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

Hope this helps!

In article <[email protected]>,
Dennis <[email protected]> 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