CODEWITHSUNDEEP
excelexcel-formulaexcel-indirect
kelvinfrog
kelvinfrog

Reputation: 467

indirect reference for sum a range

I want to have a sum formula in a cell such as =SUM(Ex:Ey) while E is the column and x and y are row numbers. Is there a way I can have excel to match x and y to the numbers in other cells? For example: cell D1=3 and D2=12 and there are many numbers in column E. By the end of column E, I want to have a cell that sum only the rows from the numbers of D1 to D2, which is sum of E3 to E12 in this example. The idea is that I can change D1 and D2 to change what rows in column I want to sum.

Upvotes: 0

Views: 1016

Answers (2)

Kharoof
Kharoof

Reputation: 597

Another way to achieve this is to use the sumif formula or sumifs for multiple conditions. This works better in some situations and can be easier to read and audit\review.

Upvotes: 0

Captain
Captain

Reputation: 2218

You can use INDIRECT to reference a range using a constructed string address - SUM(INDIRECT("E"&D1&":E"&D2)).

"E"&D1&":E"&D2 will give you the string "E3:E12" in your example, which INDIRECT will then convert to a reference to that actual range.

Note that INDIRECT comes with a recalculation overhead, but will be fine if you aren't doing too many of them or too complex things!

Upvotes: 3

Related Questions