How to sum x cells that are greater than zero on the right?

[Learn]

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.

 

[Charabeuh]

Hello,

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

In A2 put this array formula:
=IF(MIN(IF(C22>0,COLUMN(C22),""))=0,0,SUM(OFFSET(A2,0,MIN(IF(C22>0,COLUMN(C22),""))-1):OFFSET(A2,0,MIN(IF(C22>0,COLUMN(C22),""))+1)))

In B2 put this array formula:
=IF(MIN(IF(C22>0,COLUMN(C22),""))=0,0,SUM(OFFSET(A2,0,MIN(IF(C22>0,COLUMN(C22),""))-1):OFFSET(A2,0,MIN(IF(C22>0,COLUMN(C22),""))+10)))

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

 

[Donald Guillett]

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.

Sub sumfromfirstright()
For r = 2 To 7
For c = Cells(r, Columns.Count) _
..End(xlToLeft).Column To 1 Step -1
If Cells(r, c) = 0 Then
mc = c + 1
Exit For
End If
Next c
'MsgBox mc
MsgBox Cells(r, mc) _
+ Cells(r, mc + 1) _
+ Cells(r, mc + 2)
Next r
End Sub

 

[Ron Rosenfeld]

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.

Unless the data you have posted is incomplete, I don't understand your values for the First 12M, but the following formula should work.

This assumes that your actual First Month is in Column C; adjust as required:

3M:

A4: =IF(COUNTIF($C4:$Z4,">0"),SUMPRODUCT(OFFSET($B4,0,MATCH(TRUE,$C4:$Z4>0,0),1,3)),0)

12M:

B4: =IF(COUNTIF($C4:$Z4,">0"),SUMPRODUCT(OFFSET($B4,0,MATCH(TRUE,$C4:$Z4>0,0),1,12)),0)

 

[Rick Rothstein]

This array-entered** formula appears to work for your 3M condition...

=IF(COUNTIF(C4:Z4,">0")>3,SUM(IF(COLUMN(C4:Z4)<=SMALL(IF(C4:Z4<>0,COLUMN(C4:Z4)),3),C4:Z4,0)),SUM(C4:Z4))

Change the two occurrences of the number 3 to 12 for your 12M condition.

Note: You did not say what to do if there were more than 1 value but less
than the number being looked for (for example, 2 non-zero value for your 3M
condition)... I assumed you wanted to add what was there, hence the
SUM(C4:Z4) at the end of my formula... if you want to report something else
for this situation, then just change that part of the formula.

**Commit formula using CTRL+SHIFT+ENTER, not just Enter by itself

Rick Rothstein (MVP - Excel)






"Learn" wrote in message

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.