SOLVED

Automate range to sum

Brass Contributor

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

4 Replies

Re: Automate range to sum

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

Re: Automate range to sum

An alternative could be this formula.

best response confirmed by jdr_lzv (Brass Contributor)
Solution

Re: Automate range to sum

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.

Re: Automate range to sum

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)
)``````
1 best response

Accepted Solutions
best response confirmed by jdr_lzv (Brass Contributor)
Solution

Re: Automate range to sum

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.