mixing SumIf and Subtotal Functions

[dickharlo]

I have a sumif calculation: =SUMIF($C$18:$C$99,"Management",F18:F99)

I want to put filters in, and make the F18:F99 portion of the formula
a subtotal, rather than a full return. I tried putting Subtotal
(9,F18:F99) at the end of the above formula...but they don't mix well.

Is there a way to create subtotal based on filters...within a sumif
formula?

Thanks

 

[Mike H]

Hi,

Posting some sample data is usually helpful but I think you mean you want to
sum visible cells in filtered range in F18 - F99 where C18 - C99 is
management. Is that right?

Try this

=SUMPRODUCT(SUBTOTAL(9,OFFSET(F18:F99,ROW(F18:F99)-MIN(ROW(F18:F99)),,1))*(C18:C99="Management"))


Mike

 

[Shane Devenshire]

Hi,

And here is another variation on the same theme

=SUMPRODUCT(SUBTOTAL(9,OFFSET(F18:F99,ROW(F18:F99)-18,,1))*(C18:C99="Management"))

 

[Shane Devenshire]

Hi,

You can shorten my previous suggestion a little bit more:
Enter the criteria: Management in J1 and modify the ROW reference to exclude
the column letters:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(F18:F99,ROW(18:99)-18,,1))*(C18:C99=J1))

and technically you could write this

=SUMPRODUCT(SUBTOTAL(9,OFFSET(F18:F99,ROW(1:82)-1,,1))*(C18:C99=J1))

but its less obvious where 1:82 comes from.

 

[dickharlo]

Hi,

And here is another variation on the same theme

=SUMPRODUCT(SUBTOTAL(9,OFFSET(F18:F99,ROW(F18:F99)-18,,1))*(C18:C99="Manage­ment"))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire








- Show quoted text -

don't see a 'Yes' button.....but thank you. Got it working....really
appreciate your help!!!