Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
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.
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
MATCH(0,C:C,0) otherwise the row will be off by 1.
– Scott Craner
Aug 26 '19 at 21:02
+1 to account for the row offset, unless it introduces a problem I didn't foresee?
– cybernetic.nomad
Aug 26 '19 at 21:14
0!
– Rajesh S
Aug 27 '19 at 6:36
=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.
