software

Using 2003

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

To effectively:

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

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

TIA Dennis

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=quot;-SPLIT-quot;)*(H3:H26331gt;0)*G3:G26331))

HTH,
Bernie
MS Excel MVP
quot;Dennisquot; gt; wrote in message
...
gt; Using 2003
gt;
gt; How can I change next below
gt; =SUMIF(F3:F26331,quot;-SPLIT-quot;,G3:G26331)
gt;
gt; To effectively:
gt;
gt; =SUMIF(F3:F26331,quot;-SPLIT-quot;,Subtotal(9,G3:G26331))
gt;
gt; In short, the items to sum will change as I select choices in the via
gt; Autofilter.
gt;
gt; TIA Dennis
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.

Berniequot;Bernie Deitrickquot; lt;deitbe @ consumer dot orggt; wrote in message
...
gt; Dennis,
gt;
gt; Simplest is to use the SUBTOTAL function on G, and then just filter one
gt; more step, for -SPLIT- in column F.
gt;
gt; If you really want to continue along your path, you would need a helper
gt; column. In H3, use the formula
gt;
gt; =SUBTOTAL(9,G3)
gt;
gt; And copy down to match column G.
gt;
gt; Then use the formula
gt;
gt; =SUMPRODUCT((F3:F26331=quot;-SPLIT-quot;)*(H3:H26331gt;0)*G3:G26331))
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt;
gt; quot;Dennisquot; gt; wrote in message
gt; ...
gt;gt; Using 2003
gt;gt;
gt;gt; How can I change next below
gt;gt; =SUMIF(F3:F26331,quot;-SPLIT-quot;,G3:G26331)
gt;gt;
gt;gt; To effectively:
gt;gt;
gt;gt; =SUMIF(F3:F26331,quot;-SPLIT-quot;,Subtotal(9,G3:G26331))
gt;gt;
gt;gt; In short, the items to sum will change as I select choices in the via
gt;gt; Autofilter.
gt;gt;
gt;gt; TIA Dennis
gt;
gt;
Try...

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

Hope this helps!

In article gt;,
Dennis gt; wrote:

gt; Using 2003
gt;
gt; How can I change next below
gt; =SUMIF(F3:F26331,quot;-SPLIT-quot;,G3:G26331)
gt;
gt; To effectively:
gt;
gt; =SUMIF(F3:F26331,quot;-SPLIT-quot;,Subtotal(9,G3:G26331))
gt;
gt; In short, the items to sum will change as I select choices in the via
gt; Autofilter.
gt;
gt; TIA Dennis