Forum Discussion
Automate range to sum
Hi,
I have thousands of calculations to sum over specific ranges. I know how to use the basic formula =sum(E1:E20), but that requires me to manually highlight the range to sum. In the calculations I need to do, I know the start and stop cells (E1 and E2), but E1 and E2 are different for every calculation. Instead of highlighting the range manually every time, is there a way to automate the individual summation? I've attached an example spreadsheet that may help explain my question.
Thanks,
Jeff
Solution using offset:
Use this formula in H4:
=SUM(OFFSET($C$4,E4-1,,F4-E4+1))
offset decides where to start and how much to cover.
=SUM(INDEX($C$4:$C$13,MATCH(E4,$B$4:$B$13,0)):INDEX($C$4:$C$13,MATCH(F4,$B$4:$B$13,0)))
This formula returns the results from range G4:G8.
=SUM(INDIRECT(ADDRESS(E4+3,3)):INDIRECT(ADDRESS(F4+3,3)))
An alternative could be this formula.
Solution using offset:
Use this formula in H4:
=SUM(OFFSET($C$4,E4-1,,F4-E4+1))
offset decides where to start and how much to cover.
Your data is arranged perfectly to do some discard/keep with dynamic arrays.
I've added 3 dynamic names items to your workbook to be used in the formula here:
=LET( DynamicSum, LAMBDA(x, y, LET( keep, y - x + 1, discard, x - 1, rng, TAKE(DROP(Values, discard), keep), SUM(rng) ) ), MAP(Start, Stop, DynamicSum) )
