mixing SumIf and Subtotal Functions

D

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
 
M

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
 
S

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"))
 
S

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.
 
D

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!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top