Automate range to sum

[jdr_lzv]

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  

[#2]

@jdr_lzv 

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

[#3]

@jdr_lzv 

=SUM(INDIRECT(ADDRESS(E4+3,3)):INDIRECT(ADDRESS(F4+3,3)))

An alternative could be this formula.

[#4 · Accepted solution]

@jdr_lzv 

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.

[#5]

@jdr_lzv 

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