Auto Summing a Column with the hash key from Data in Rows

  • Hi Everyone,

    Its been a while and i hope everyone is doing well.

    I need your help regarding summing values from consecutive rows which can be done using the sum formula however the twist is that i want the sum formula to be dynamic as is happening with office 365 with the integration of the hash icon into formulas.

    As you can see below

    The data in column A and H have the blue box around them meaning that the data is dynamic, however my concern comes into the sum in column H, i have to drag it down to the end of the table every time the data in column H is and A are updated.

    The formula that i used for column AJ is =SUM(XLOOKUP($A3,$A$3#,$H$3#)) which could also have been substituted with =SUM(H3:AI3) and dragged down.

    What i require is a sum formula that calculates the values from cells H3 to AI3 and for it to spill that sum for the rows below it, if a lookup is required then i would use the spilled cells in column A which is =SORT(UNIQUE(FILTER(REFORMAT!$B$2:$B$1048576,REFORMAT!$B$2:$B$1048576<>""))).

    I would really appreciate your assistance on this matter.


  • Hi Roy i've attached a sample workbook.

    I've attached a sample of how the data is structured, and just to reiterate; i am basically looking for a way to make the sum values in column N spill the sum of the values in the cells in rows 3 to 10.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!