# Sum values only if consecutive numbers

#### rachelih

##### New Member
Row 39 which varies by month (across columns) has some months with values and some without:

GOAL: I would like to have a formula in cell G41 that sums row 39 (highlighted) ONLY IF there are 6 consecutive months where values in row 39 are greater than 0. In other words, G41 should total to \$544,560.

#### Peter_SSs

##### MrExcel MVP, Moderator
I can do a helper row.
Try this. Assumption is that F3 does not contain a 1.

rachelih.xlsm
FGHIJKLMNOPQR
1
25,8990029,455042,51293,41552,24268,938141,41692,67853,359
3000001111111
4
5544,560
Sheet1
Cell Formulas
RangeFormula
G3:R3G3=IF(G2>0,IF(F3=1,1,IF(AND(COLUMNS(G2:\$R2)>5,COUNTIF(G2:L2,">0")>5),1,0)),0)
G5G5=SUMIF(G3:R3,1,G2:R2)

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### rachelih

##### New Member
Try this. Assumption is that F3 does not contain a 1.
Thank you both so much for your help!! Using the helper row in the end.

Really appreciate this suggestions!!

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Thanks for the follow-up.

If you are interested, a marginally shorter formula (1 less function call) for the helper row.

rachelih.xlsm
FGHIJKLMNOPQR
1
25,8990029,455042,51293,41552,24268,938141,41692,67853,359
3000001111111
4
5544,560
Sheet3
Cell Formulas
RangeFormula
G3:R3G3=--OR(AND(COLUMNS(G2:\$R2)>5,COUNTIF(G2:L2,">0")>5),AND(G2>0,F3=1))
G5G5=SUMIF(G3:R3,1,G2:R2)

Replies
4
Views
505
Replies
3
Views
462
Replies
34
Views
729
Replies
3
Views
588
Replies
6
Views
186

1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back