Excel: Reference a cell value as part of a Sum()

I don't think that will work, it will error because it will interpret A1 as a String.

You may not be explaining correctly so attach an example workbook.

Book1.xlsxThe green and yellow increases, but the last column will always be grand total on both.   I need to create sum that automatically updates.

Give this a try:

=SUM(INDIRECT("B6:" & K2))

Select allOpen in new window

Paul

You can use an OFFSET function to create a range to SUM:

=SUM(OFFSET($B$6,0,0,MATCH("Grand Total",A:A,0)-6,MATCH("Grand Total",5:5,0)-2))

Range starts at B6
First MATCH finds row with "Grand Total" in column A and deducts 6 to allow for blank rows above data and row containing "Grand Total". This determines range height (number of rows).
Second MATCH finds column with "Grand Total" in row 5 and deducts 2 to allow for blank column before data and column containing "Grand Total". This determines range width (number of columns).

Awesome thanks!