Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding subtotals from SUMIF function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding subtotals from SUMIF function
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 "Graeme" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding subtotals from SUMIF function
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding subtotals from SUMIF function
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 "Graeme" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding subtotals from SUMIF function
D'oh! You're right, that fixed the problem.
BTW thanks for the extra shortcut tip! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding subtotals from SUMIF function
I'm glad that helped.....and thanks for the feedback
*********** Regards, Ron XL2002, WinXP "Graeme" wrote: D'oh! You're right, that fixed the problem. BTW thanks for the extra shortcut tip! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How i can create a formula that combine subtotals and sumif | Excel Worksheet Functions | |||
Excluding Zero's from Average (SumIF / CountIF) | Excel Worksheet Functions | |||
Min Function Excluding Zero Values & More | Excel Worksheet Functions | |||
Excluding 0s and blanks from a LINEST function | Excel Worksheet Functions | |||
Modify SUMIF and COUNTIF to work with SUBTOTALS | Excel Worksheet Functions |