subtotal with sumif nested
 

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

=SUBTOTAL(109,SUMIF(Sum!B15:B103,"718",Sum!F15:F10 3))
or
=SUBTOTAL(109,SUMIF(Sum!B15:B103,right(a1,3),Sum!F 15: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.

subtotal with sumif nested
 

Can Subtotal NOT take a sumif as the ref#?

No

Try this...

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

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Hi all.
I'm trying to do a subtotal function with a nested sumif.

=SUBTOTAL(109,SUMIF(Sum!B15:B103,"718",Sum!F15:F10 3))
or
=SUBTOTAL(109,SUMIF(Sum!B15:B103,right(a1,3),Sum!F 15: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.