Need help with a formula

[santors71]

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

[ArviLaanemets]

=sum(offset($l$3,4,$b$12-1,1,$d$12))

[santors71]

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

[NBVC]

Try:


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

[ArviLaanemets]

For me it works!

[santors71]

Big thanks now it works

[Pecoflyer]

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

[Roger Govier]

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

=SUM(INDEX(L7:Q7,B12):INDEX(L7:Q7,D12+1))

[NBVC]

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

[ArviLaanemets]

Originally Posted by Pecoflyer

Be aware that OFFSET is a volatile function.

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

Is that O7 reference correct?

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

[Debaser]

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