Hello,

I assume that your data are in columns C2 to P2.

In A2 put this array formula:
=IF(MIN(IF(C2:P20,COLUMN(C2:P2),""))=0,0,SUM(OFFS ET(A2,0,MIN(IF(C2:P20,COLUMN(C2:P2),""))-1):OFFSET(A2,0,MIN(IF(C2:P20,COLUMN(C2:P2),""))+1 )))

In B2 put this array formula:
=IF(MIN(IF(C2:P20,COLUMN(C2:P2),""))=0,0,SUM(OFFS ET(A2,0,MIN(IF(C2:P20,COLUMN(C2:P2),""))-1):OFFSET(A2,0,MIN(IF(C2:P20,COLUMN(C2:P2),""))+1 0)))

Then drag down these formulas.

These two formula are array formulas. You should validate these
formulas with the combination of the three keystrokes CTRL+Shift+Enter
instead of using the single keystroke ENTER. These formulas will be
surrounded by braces. Every time you edit these formulas, you have to
validate them with CTRL+Shift+Enter.

Hope that will help you...


Hi

I am finding a solution (formula) to automatically sum (3 or 12) cells
on the right after encountering first right cell greater than 0.

Currently, this is done manually. (eg. 3.8 is the sum of 2.9 (Aug) +
0.5 (Sep) + 0.4 (Oct).)

Question is there a way to do this automatically because there are
thousands of rows..and this will drive me crasy in maintaining this
worksheet effectively. Thanks.

First First
3M 12M Jul Aug Sep Oct Nov
Dec ....................
---------------------------------------------------------------------------
----------------------------------------------------------------
3.8 5.4 0.0 2.9 0.5 0.4 0.3
0.2 ....................
1.1 2.2 0.0 0.9 0.0 0.2 0.0
0.4 ....................
0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 ....................
4.1 5.2 0.0 0.0 0.0 1.9 1.3
0.9 ....................
3.1 5.1 0.0 0.0 0.0 0.5 1.7
0.9 ....................
2.6 3.9 0.0 0.0 0.0 0.4 1.1
1.1 ....................

Thanks.