Formula to sum values in cells in the same row following and excluding the first cell containing a value >0

[dyanna]

The project that I am working on requires that we identify the number of events that occur following the first month in which one or more events occur. We need to return the sum of the cells in one row following and excluding the first cell in that row with a value greater than 0. There will always be 12 cells in each row.

Example: Row 1 - A1 - L1 and Row 2 - A2 L2

[TABLE="width: 251"]
<tbody>[TR]
[TD="width: 14, bgcolor: transparent"][/TD]
[TD="width: 27, bgcolor: transparent"]A
[/TD]
[TD="width: 27, bgcolor: transparent"]B
[/TD]
[TD="width: 27, bgcolor: transparent"]C
[/TD]
[TD="width: 27, bgcolor: transparent"]D
[/TD]
[TD="width: 27, bgcolor: transparent"]E
[/TD]
[TD="width: 27, bgcolor: transparent"]F
[/TD]
[TD="width: 27, bgcolor: transparent"]G
[/TD]
[TD="width: 27, bgcolor: transparent"]A
[/TD]
[TD="width: 27, bgcolor: transparent"]B
[/TD]
[TD="width: 27, bgcolor: transparent"]C
[/TD]
[TD="width: 27, bgcolor: transparent"]D
[/TD]
[TD="width: 27, bgcolor: transparent"]E
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]3
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]5
[/TD]
[/TR]
</tbody>[/TABLE]

For row 1, exclude cell C1 and sum cells D1 - L1 = 8
For row 2, exclude cell A2 and sum cells B2 - L2 = 16

I have tried numerous searches to try to locate a solution per the instructions on the Board guidelines to avoid creating a new thread but have been unable to find a solution to match my need. I appreciate any advice that you can provide. Thank you!!!

 

[arthurbr]

Perhaps try =SUM(INDEX($A1:$L1,1+MATCH(TRUE,INDEX(A1:L1>0,),0)):L1) and pull down

 

[Peter_SSs]

If the number of events are always single digits like your samples, and you have the CONCAT function in your Excel version, then you could also use this.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	0	0	1	2	1	0	1	1	2	0	0	1		8
2	1	0	0	1	2	3	1	0	0	4	0	5		16
Sum

Cell Formulas
Range	Formula
N1	=SUM(A1:L1)-LEFT(CONCAT(A1:L1)+0,1)

 

[dyanna]

Thank you so much! That worked beautifully!!!

 

[dyanna]

Thank you so much for your response Peter. The numbers could potentially exceed two digits but I will definitely save this formula for future use. I appreciate your assistance.

 

[Peter_SSs]

Thank you so much for your response Peter. The numbers could potentially exceed two digits but I will definitely save this formula for future use. I appreciate your assistance.

You're welcome. (It was a bit of a long-shot. )