Hello, need help with this formula.
Want to change the numbers in boxes B12 (2) and D12 (3) to sum the columns from M7 (2) to O7 (3)
Then the sum will be 8431. When I then change the digits in the boxes, the summary will change.


Have tested several options but not good enough.
Have any desire to help me, see attached file
Anders

Attached Files

sum.xlsx (9.4 KB, 16 views)

Sorry, I can't get this formula working. It looks it get static
I need to change the numbers 2 and 3 and get new figures
Best regards
Anders

Try:


=SUM(INDEX($L$7:$Q$7,B12):INDEX($L$7:$Q$7,D12))

For me it works!

Attached Files

sum.xlsx (9.7 KB, 11 views)

Big thanks now it works

Be aware that OFFSET is a volatile function.
The solution on post #4 is not

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Arvi's formula works fine.
If it was returning a static result, then your Calculation mode must have been set to manual.

Yes, that is a volatile function, however the non-volatile Index formula will return the wrong result unless you add 1 to the value in your box D12

It's a matter of what the OP really meant by "M7 (2) to O7 (3)". Is that O7 reference correct? The 3 coincides with column N. My assumption, I guess what the OP really meant to add from M7(2) to N7(3).

As the formula will be obviously in single cell, it will be no problem at all!

I checked sums for different possibilities and made a comparison with OP's sum. As result I decided, that B12 is number of start column in datarange (L4:Q7), and D12 is number of columns to sum.

Btw, the formula using OFFSET allows to use 4 parameters to select the sum range. OP can determine the row from where summarizing begins (currently fixed at 4), and the number of rows to summarize (currently fixed at 1).

Also, INDEX():INDEX() is still semi-volatile. It will recalculate when the workbook is opened.