Sum Indirect not working

%3CLINGO-SUB%20id%3D%22lingo-sub-1936028%22%20slang%3D%22en-US%22%3ESum%20Indirect%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1936028%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20is%20divided%20into%20several%20sections%20and%20I%20need%20each%20section%20to%20be%20totaled%20separately.%20I%20am%20using%26nbsp%3B%3DSUM(INDIRECT(%22E%2422%3AE%22%26amp%3BROW()-1))%20to%20have%20to%20rows%20automatically%20include%20any%20new%20rows%20that%20I%20add%20the%20problem%20I%20am%20having%20is%20when%20I%20do%20add%20a%20new%20row%20it%20changes%20the%20starting%20column%20number%20and%20throws%20off%20my%20calculation.%20So%20how%20do%20I%20get%20this%20to%20work%20or%20is%20there%20another%20way%20that%20I%20can%20get%20the%20results%20that%20I%20am%20looking%20for.%20I%20don't%20want%20to%20have%20to%20manually%20update%20the%20sum%20formula%20every%20time%20I%20add%20a%20new%20row.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1936028%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1936083%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Indirect%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1936083%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F883616%22%20target%3D%22_blank%22%3E%40gloriaBRC802%3C%2FA%3E%26nbsp%3BTested%20your%20formula.%20Should%20work.%20Either%20by%20inserting%20a%20row%20above%20the%20cell%20with%20the%20formula%20or%20by%20moving%20the%20formula%20down%20and%20inserting%20new%20values%20above%20it.%20Works%20all%20the%20time.%20Can%20you%20upload%20your%20schedule%20(delete%20confidential%20information)%20to%20demonstrate%20the%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1936153%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Indirect%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1936153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Here%20is%20the%20sheet%20that%20I%20am%20having%20problems%20with.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1936229%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Indirect%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1936229%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F883616%22%20target%3D%22_blank%22%3E%40gloriaBRC802%3C%2FA%3E%26nbsp%3BCan't%20see%20anything%20wrong%20with%20it.%20The%20sub-totals%20in%20E%20seem%20to%20work%20OK.%20Can%20insert%20a%20row%2C%20enter%20an%20amount%20and%20the%20sub-total%20updates%20correctly.%20But%20perhaps%20that%20is%20not%20what%20mean.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1936287%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Indirect%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1936287%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20when%20I%20added%20a%20new%20line%20in%20the%20section%20labeled%26nbsp%3BAdministrative%20the%20total%20in%20line%20E51%20changes%20to%20include%20line%20E47%20and%20it%20continues%20to%20do%20that%20so%20the%20total%20cost%20at%20the%20very%20bottom%20is%20wrong.%20It%20should%20not%20start%20including%20the%20subtotal%20from%20the%20section%20above.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a spreadsheet that is divided into several sections and I need each section to be totaled separately. I am using =SUM(INDIRECT("E$22:E"&ROW()-1)) to have to rows automatically include any new rows that I add the problem I am having is when I do add a new row it changes the starting column number and throws off my calculation. So how do I get this to work or is there another way that I can get the results that I am looking for. I don't want to have to manually update the sum formula every time I add a new row.

11 Replies

@gloriaBRC802 Tested your formula. Should work. Either by inserting a row above the cell with the formula or by moving the formula down and inserting new values above it. Works all the time. Can you upload your schedule (delete confidential information) to demonstrate the problem?

@Riny_van_Eekelen  Here is the sheet that I am having problems with. 

@gloriaBRC802 Can't see anything wrong with it. The sub-totals in E seem to work OK. Can insert a row, enter an amount and the sub-total updates correctly. But perhaps that is not what mean.

@Riny_van_Eekelen  when I added a new line in the section labeled Administrative the total in line E51 changes to include line E47 and it continues to do that so the total cost at the very bottom is wrong. It should not start including the subtotal from the section above.

@gloriaBRC802 Okay! That's because you "hard-code" the first cell of the range called by INDIRECT.

 

Using your example for the sub-total in E50, try it this way:

=SUM(E47:OFFSET(E50,-1,0))

You'll need to do this for each of your sub-totals. Let me know if it works, or not.

@Riny_van_Eekelen with this formula to include multiple rows can I just enter the range and it still work properly. Example: =SUM(E44:E59:OFFSET(E60,-1,0))

@gloriaBRC802 Sorry. Don't understand. Did you try the formula in my previous post?

yes, I did but it doesn't add up all the lines in the separate sections. It works but only for a few of the lines

@gloriaBRC802 Really sorry, but don't understand what you mean.

@Riny_van_Eekelen  I see what I did wrong. Thank you for your help.

@gloriaBRC802 Glad you resolved it!