# subtotal with sumif nested

S

#### Steve

Hi all.
I'm trying to do a subtotal function with a nested sumif.

=SUBTOTAL(109,SUMIF(Sum!B15:B103,"718",Sum!F15:F103))
or
=SUBTOTAL(109,SUMIF(Sum!B15:B103,right(a1,3),Sum!F15:F103))

where a1 is the name that I want to test against for my criteria, and the
Sum! is the sheet name, column B is my criteria range, and column F is my sum
range.

I keep getting an error message box, but it doesn't way what the error is.
I.e., "the formula you typed contains an error" and then gives the 3 typical
options for resolving it. None of which apply as far as I can tell.

Can Subtotal NOT take a sumif as the ref#?

I did test just the sumif and it shows exactly what I'm wanting it to. I.e.,
no failed equation, or elements that are throwing any # errors.

Thank you.

T

#### T. Valko

Can Subtotal NOT take a sumif as the ref#?

No

Try this...

=SUMPRODUCT(SUBTOTAL(103,OFFSET(Sum!B15:B103,ROW(Sum!B15:B103)-ROW(Sum!B15),0,1)),--(Sum!B15:B103="718"),Sum!F15:F103)