excelexcel-formulaexcel-indirect

Excel: A simpler way to sum a formula-based range besides INDIRECT() & ADDRESS()?

I have a master value which defines how many columns to sum. So in the below image the master value is 4, which means I am summing D4:G4 (total 1100). If I changed the value to 8, it would sum D4:K4 (total 1700).

At the moment, I'm creating a large indirect address reference. Am I over-complicating this, or is this the simplest way?

=SUM(INDIRECT(ADDRESS(ROW(D4),COLUMN(D4))&":"&ADDRESS(ROW(D4),COLUMN(D4)+$B$1-1)))

enter image description here

Solution

  • Use INDEX, like this:

    =SUM(D4:INDEX(D4:W4,,B1))

    enter image description here