I tried this formula but for some reason, the result is 0 and not the sum of the cells from B7 to the row that I place the formula in.
the formula is:
=SUM(CONCATENATE("B7:","B",ROW()))
I want a sum of all the rows from B7 to the row that I place the formula in.
the CONCATENATE itself is working, the SUM is working (when inserted manually the range like =SUM(B7:B20)
They just do not work together and it gives me a result of 0 no matter the numbers present in column B
Please help.
Since you are concatenating the A1 notation, the nested function CONCATENATE will return a String and SUM is trying to evaluate the sum of the String instead of the values of the Range.
You can pass the result of this concatenation to INDIRECT in order to return to SUM the actual reference of the range of cells to be summed.
Full formula will look like this =SUM(INDIRECT(CONCATENATE("B7:","B",ROW()))).
Note: This formula won’t work if pasted on column B, because it’ll cause a circular reference.