How can i drag formula from left to right?

Question

I have the following table. I want to fill in the empty cell by using this formula: =SUM(M2:N2). When I drag this formula from left to right (col 0 to 2...), it becomes =SUM(N2:O2). I want it to become =SUM(M3:N3) not =SUM(N2:O2) how can I do this?

ENG Date    0   1   2   3   4   5   6

Z#1 6/1                         
Z#1 6/2                         
Z#1 6/3                         
Z#1 6/4 

Thanks

Answer 1

Use the INDEX function and pick the row to SUM by referencing the relative column.

=SUM(INDEX($M:$N, COLUMN(B:B), ))

      

Resist the urge to use the volatile OFFSET function.