Forum Discussion

jdr_lzv's avatar
jdr_lzv
Brass Contributor
Mar 18, 2024

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  

Excel
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 18, 2024

    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.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Mar 18, 2024

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

Share

Resources