Excluding subtotals from SUMIF function

[Guest]

I'm trying to use a SUMIF function on a column that includes SUBTOTAL
functions. The SUMIF seems to include the SUBTOTAL even though it doesn't
match the criteria. Why? How can I can exclude the SUBTOTALs from the SUMIF?

TIA, Graeme

 

[Guest]

You'll need to post your SUMIF formula for us to see the criteris. It sounds
like your criteria is inadvertently including the values in the subtotal rows.

Example:
=SUMIF(A1:A100,"<>Harold",B1:B100)


***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

thanks Ron...here it is

=SUMIF($S$2:$S$88,S96,$F$3:$F$88)

S2:S88 = the range containing the labels like "direct debit" or "cheque"
S96 = a cell containing the criteria which is a phrase like "direct debit"
F3:F8 = the $ amounts to be summed, and also the SUBTOTAL function

I'm absolutely sure S2:S88 does not have any labels at all next to the
subtotal rows. I can't prove it without attaching the sheet, but i've been
over it a dozen times.

Any advice?

 

[Guest]

I think I spotted the problem, my friend.

In your formula:
=SUMIF($S$2:$S$88,S96,$F$3:$F$88)

The search range begins at $S$2
but the calc range begins at: $F$3
....notice Row_2 versus Row_3

That means for every matched item in Col_S the value from Col_F on the NEXT
ROW DOWN is summed. So if S19 is a match, the value from F20 is summed.

Try this:
=SUMIF($S$2:$S$88,S96,$F$2)

I used a shortcut in that formula. SUMIF always forces the calc range to be
the same size as the search range....so you only need to enter the 1st cell
of the calc range. In the above formula, Excel implicitly reads that formula
as:
=SUMIF($S$2:$S$88,S96,$F$2:$F$88)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

D'oh! You're right, that fixed the problem.

BTW thanks for the extra shortcut tip!

 

[Guest]

I'm glad that helped.....and thanks for the feedback


***********
Regards,
Ron

XL2002, WinXP