Sum up row up to the last entry in row above

Question

I am trying to figure out how to sum up the values in row 2 up to the point where the last entry in row 1 is. I googled a lot and asked on Reddit already but I am an Excel noob so I can't really make it work. I already tried stuff like INDEX,LOOKUP,SUMPRODUCT and so on but I am either too stupid or they do not really work out for my problem.

This are some of the suggestions: =LOOKUP(2,1/(A:A<>""),A:A) but this functions has to be converted to one that adds up the values of B up to the point where the last entry is if I understand it correctly:)

Thanks in advance and have a nice day

Answer 1

Try:

=SUM(A2:INDEX(2:2,LOOKUP(2,1/(1:1<>""),COLUMN(1:1))))

• LOOKUP(2,1/(1:1<>""),COLUMN(1:1)) will return the column number of the last entry in Row 1.
• That result is then used as a Column argument for an INDEX function that represents Row 2
• We construct a range argument that starts at the first column of Row 2 (A2) and extends to the relevant column number
• then SUM that range
• Note that if there is an entry in row 1 of the same column where exists your formula, this will produce a circular reference. You can check for that within the formula, or ignore it.

Answer 2

sum up the values in row 2 up to the point where the last entry in row 1 is

I think you are trying to sum up all values in row 2 until last X in row 1.

So in this case, because last not empty cell is in column E, so you want to sum up all values in row 2 from Column A to Column E.

I'm using this formula:

=SUMPRODUCT(--(COLUMN(A2:F2)<=SUMPRODUCT(MAX(--($A$1:$F$1<>"")*COLUMN($A$1:$F$1))));A2:F2)

This is how it works:

1. SUMPRODUCT(MAX(--($A$1:$F$1<>"")*COLUMN($A$1:$F$1))) will return the last column number of a non empty cell in range A1:F1
2. Rest of formula will compare each column number with the value returned in step 1, and if column is lower or equal, it will be added to final sum.