mixing SumIf and Subtotal Functions
 

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

mixing SumIf and Subtotal Functions
 

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

" wrote:

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

mixing SumIf and Subtotal Functions
 

Hi,

And here is another variation on the same theme

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


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

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

mixing SumIf and Subtotal Functions
 

Hi,

You can shorten my previous suggestion a little bit mo
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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

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

mixing SumIf and Subtotal Functions
 

On Feb 21, 1:11*pm, Shane Devenshire
wrote:
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



" wrote:
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- Hide quoted text -

- Show quoted text -

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