# Sum until specific criterion is met

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.

• While there's no proper answer, find "excel lookup last value in column" videos on YouTube, might help you. Cheers. – 174140 Aug 27 '19 at 7:03

Something like:

``````=SUMPRODUCT(C2:C12,--(ROW(C2:C12)<MATCH(0,C2:C12,0)+1))
``````

or (per Scott Craner's suggestion)

``````=SUMPRODUCT(C2:C12,--(ROW(C2:C12)<MATCH(0,C:C,0)))
``````

This will sum all cells in `C2:C12` whose row is smaller than the row of the first cell with a `0`

• The match should be: `MATCH(0,C:C,0)` otherwise the row will be off by 1. – Scott Craner Aug 26 '19 at 21:02
• @Scott Craner - See my edit. I had the `+1` to account for the row offset, unless it introduces a problem I didn't foresee? – cybernetic.nomad Aug 26 '19 at 21:14
• I did not see that. – Scott Craner Aug 26 '19 at 21:16
• @cybernetic.nomad,, second formula is working properly but not the first one, returns `0`! – Rajesh S Aug 27 '19 at 6:36
• @Rajesh S - both work fine for me... – cybernetic.nomad Aug 27 '19 at 16:23
``````=SUM(\$C\$2:INDEX(C:C,MATCH(0,C:C,0)))
``````

Similar to SUMPRODUCT given above, but avoids the use of array calculations It assumes row 1 is a header row and the data starts in row 2. The match does the same job, it finds the location of the first 0 in column C:C. INDEX returns the cell address of that location.