+ Reply to Thread
Results 1 to 6 of 6

Formula to count consecutive non-zero values and reset count after a non-zero value

  1. #1
    Registered User
    Join Date
    04-22-2021
    Location
    Somewhere
    MS-Off Ver
    2016
    Posts
    5

    Formula to count consecutive non-zero values and reset count after a non-zero value

    I am desperately seeking help with a formula. I am trying to count number of columns between non-zero values in a row and reset the count once a non-zero value is present. The problem I am trying to solve is related to perishable inventory. Each lot of inventory will be consumed according to the forecast in a first in, first out process. Once a lot has expired, the forecast should subtract from the next lot until its expiration date. I need to know how much inventory will remain upon the expiration date for each expiration quantity (expected shrink).

    Row 2: Daily forecast
    Row 3: Expired inventory quantity
    Row 4: Expected shrink (Manual formula- this is what I am trying to solve)
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,491

    Re: Formula to count consecutive non-zero values and reset count after a non-zero value

    In B5 then copied across
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Registered User
    Join Date
    04-22-2021
    Location
    Somewhere
    MS-Off Ver
    2016
    Posts
    5

    Re: Formula to count consecutive non-zero values and reset count after a non-zero value

    Thank you!!!!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,402

    Re: Formula to count consecutive non-zero values and reset count after a non-zero value

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Registered User
    Join Date
    04-22-2021
    Location
    Somewhere
    MS-Off Ver
    2016
    Posts
    5

    Re: Formula to count consecutive non-zero values and reset count after a non-zero value

    This worked. Marking as solved. Thank you so much kvsrinivasamurth! Added reputation for an excellent solution.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,491

    Re: Formula to count consecutive non-zero values and reset count after a non-zero value

    Thanks for feedback.

+ 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: 2
    Last Post: 02-11-2021, 12:52 PM
  2. Replies: 7
    Last Post: 03-09-2020, 07:11 PM
  3. Formula to count the number of events of consecutive values
    By malaka76 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2020, 08:11 AM
  4. Formula to count days and reset count
    By daviddelatorre in forum Excel General
    Replies: 8
    Last Post: 05-27-2015, 09:25 AM
  5. Replies: 17
    Last Post: 06-27-2014, 04:25 PM
  6. [SOLVED] I need COUNTIFS or SUMPRODUCT formula to count ONLY Non-Consecutive values
    By justinbelkin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-19-2013, 03:24 PM
  7. Count Intervals of 2 Consecutive Values in same Row and Return Count across Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-29-2005, 11:30 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