Sum until specific criterion is met

[AIS]

Hello,
I was looking for a formula which will stop the SUM operation as soon as it finds the value in the column.

Total	Pass	Fail
36	35	1
58	57	1
32	34	1
59	58	1
42	41	1
77	75	2
44	44	0
53	52	1
57	55	2
52	50	2
54	53	1
49	48	1

Here the summation will stop when in Fail column will finds 0 like the orange marked regardless of the values afterward. Here the Total sum will be 7 instead of 14 because of the 0.

 

[Peter Bartholomew]

You could use the fact that INDEX/MATCH returns a cell reference to build the range you wish to sum.
= IFERROR( SUM( start : INDEX( fail, MATCH( 0, fail, 0 ) ) ), SUM(fail) )
where 'fail' is the column and 'start' is the top cell. The error traps the #N/A that results from having no '0's in which case the formula returns the column SUM.