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...
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)
)
