Sum values only if consecutive numbers

[rachelih]

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]

I can do a helper row.

Try this. Assumption is that F3 does not contain a 1.

rachelih.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		5,899	0	0	29,455	0	42,512	93,415	52,242	68,938	141,416	92,678	53,359
3		0	0	0	0	0	1	1	1	1	1	1	1
4
5		544,560
Sheet1

Cell Formulas
Range	Formula
G3:R3	G3	=IF(G2>0,IF(F3=1,1,IF(AND(COLUMNS(G2:$R2)>5,COUNTIF(G2:L2,">0")>5),1,0)),0)
G5	G5	=SUMIF(G3:R3,1,G2:R2)

 

[rachelih]

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]

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
	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		5,899	0	0	29,455	0	42,512	93,415	52,242	68,938	141,416	92,678	53,359
3		0	0	0	0	0	1	1	1	1	1	1	1
4
5		544,560
Sheet3

Cell Formulas
Range	Formula
G3:R3	G3	=--OR(AND(COLUMNS(G2:$R2)>5,COUNTIF(G2:L2,">0")>5),AND(G2>0,F3=1))
G5	G5	=SUMIF(G3:R3,1,G2:R2)