# 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.

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

Never mind, you want it backwords.

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

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?

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

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)

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).

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1