1

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.

1
  • 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
2

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

5
  • 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
1
=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.

POC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.