# Sum values in a row until a new value appears (Right to left)

1. ## Sum values in a row until a new value appears (Right to left)

I have been looking everywhere and can't find what I need.

For instance, I have these columns:
A B C D E F G
0 3 4 0 0 0 0
2 2 5 3 2 1 0

What I would like to count (backwards) is how many times does 0 show up. So, in the first row, it should give me 4 and in row 2 it should give me 1.
I want the formula to stop once it finds a different value than 0. I was thinking of a COUNTIF, but I don't know how to make it from right to left and to make it stop once it is different than 0.  Register To Reply

2. ## Re: Sum values in a row until a new value appears (Right to left)

Never mind, you want it backwords.  Register To Reply

3. ## Re: Sum values in a row until a new value appears (Right to left)

Thank you Sambo kid, but this would give me 5 in the first row and it should only be 4, as from D to G the values are 0.
It should stop counting after it encounters the 4 in column C  Register To Reply

4. ## Re: Sum values in a row until a new value appears (Right to left)

at what point do you want it to count then stop counting zeros?
so what if you have 0 3 2 4 0 1 1 does it not count since the last zero is in column E and not in column G?  Register To Reply

5. ## Re: Sum values in a row until a new value appears (Right to left)

Exactly, if in the most right column there is no 0, then it shouldn't count or just return 0.
Sorry, my explanation wasn't very clear  Register To Reply

6. ## Re: Sum values in a row until a new value appears (Right to left)

Try this?

=IF(G1=0,IF(F1=0,IF(E1=0,IF(D1=0,IF(C1=0,IF(B1=0,IF(A1=0,7,6),5),4),3),2),1),0)  Register To Reply

7. ## Re: Sum values in a row until a new value appears (Right to left)

I think this will do it for you, it works in my test file...
=IF(SUM(A2:G2)=0,7,IF(AND(B2=0,C2=0,D2=0,E2=0,F2=0,G2=0),6,IF(AND(C2=0,D2=0,E2=0,F2=0,G2=0),5,IF(AND(D2=0,E2=0,F2=0,G2=0),4,IF(AND(E2=0,F2=0,G2=0),3,IF(AND(F2=0,G2=0),2,IF(G2=0,1,"")))))))
this works it from right to left and returns a blank if there are no zeros AND will return a blank if there is no zero in the final column (G).  Register To Reply