Forum Discussion

gloriaBRC802's avatar
gloriaBRC802
Copper Contributor
Nov 27, 2020

Sum Indirect not working

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 ...
excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 27, 2020

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's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 27, 2020

    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.

    • gloriaBRC802's avatar
      gloriaBRC802
      Copper Contributor
      Nov 27, 2020

      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.

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Nov 27, 2020

        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.

Resources