CODEWITHSUNDEEP
excelexcel-formulaexcel-indirect
MrMusAddict
MrMusAddict

Reputation: 427

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

Upvotes: 0

Views: 59

Answers (1)

BigBen
BigBen

Reputation: 50008

Use INDEX, like this:

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

enter image description here

Upvotes: 3

Related Questions