+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    01-30-2018
    Location
    Amsterdam
    MS-Off Ver
    10
    Posts
    3

    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.

    Thanks in advance for your help.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

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

    Never mind, you want it backwords.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    01-30-2018
    Location
    Amsterdam
    MS-Off Ver
    10
    Posts
    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. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    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. #5
    Registered User
    Join Date
    01-30-2018
    Location
    Amsterdam
    MS-Off Ver
    10
    Posts
    3

    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. #6
    Registered User
    Join Date
    06-06-2017
    Location
    Wimborne, England
    MS-Off Ver
    Office 365 Business
    Posts
    30

    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. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 07-25-2015, 07:23 AM
  2. [SOLVED] INDEX? IF Any Of These Values Appears in B1, Insert Value in A1
    By ThanksAlot in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-19-2015, 08:06 PM
  3. How do i add a sheet and add values to the cells before it appears?
    By kjy1989 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2015, 08:53 AM
  4. ''navigation pane'' appears on the left at startup
    By Joe Miller in forum Excel General
    Replies: 1
    Last Post: 11-05-2014, 10:51 AM
  5. Replies: 0
    Last Post: 02-04-2013, 01:18 AM
  6. Replies: 2
    Last Post: 06-18-2012, 04:26 PM
  7. Paste Values appears to be broken
    By david in forum Excel General
    Replies: 1
    Last Post: 10-05-2005, 06:05 AM

Bookmarks

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