+ Reply to Thread
Results 1 to 6 of 6

Count consecutive > zero, ignore zero count consecutive > zero next cell

  1. #1
    Registered User
    Join Date
    06-18-2018
    Location
    Midlands
    MS-Off Ver
    excel 2013
    Posts
    2

    Count consecutive > zero, ignore zero count consecutive > zero next cell

    Hi All,

    Hope you are well,

    So basically i come cap in hand to your superior knowledge,

    In the data range (B2:DK2 Contains a range of numbers (counts of instances by day) between 0 & 5.

    The below array formula counts the zeroes, and when dragged across moves onto the next group of zeroes.

    =IFERROR(SMALL(IF($B2:$DK2>0,COLUMN($B2:$DK2)),COLUMN(A:A)+1)-SMALL(IF($B2:$DK2>0,COLUMN($B2:$DK2)),COLUMN(A:A))-1,"")

    Im ultimately after the reverse, so for example -

    00252500002524210000012512201010102020002222 should show(in consecutive cells), 4,6,6,1,1,1,1,1,4.

    Thanks in Advance.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: Count consecutive > zero, ignore zero count consecutive > zero next cell

    Hi karl and welcome to the forum,

    I have an answer without a single formula, although it may be possible... Here is the method. Concat() all those numbers into a long string of digits. Replace all the "0" with a space (Use the Substitute() formula for that). Then (and here is the secret) Trim() that string reducing all double spaces to a single space. Now take that string and do a Text To Columns. Put a formula under that row of Len() of numbers between zeros and Boom (drop the mic) your answer. See the attached. I may have left out a Paste as Value in the next to last step.
    Len of run of non zero cells.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    456

    Re: Count consecutive > zero, ignore zero count consecutive > zero next cell

    So here's my way of thinking about this....I would eventually use 'Convert text to column wizzard' ~ Delimited: "Other = 0" and check the box 'Treat consecutive delimiters as one [ this is in the Ribbon, under DATA / Text to Columns. Before that I have a Function in VBA that reverses the string. Once the string is reversed, I use the Text to column wizzard to separate the numbers by the character '0' (which will put the single character or cluster of numbers into their own cells...then it's just a matter of concatenating them together. Reverse is available in vba code...the function is ~
    Please Login or Register  to view this content.
    I've attached sheet too....hope this helps.
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: Count consecutive > zero, ignore zero count consecutive > zero next cell

    OK Karl,

    Here is a single formula that will give you the answer.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See the formula and example in the attached
    Len of run of non zero cells single formula.xlsx

  5. #5
    Registered User
    Join Date
    01-24-2020
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    95

    Re: Count consecutive > zero, ignore zero count consecutive > zero next cell

    Hi, there!

    FREQUENCY(data_array,bins_array) function does the trick here.

    Supposing A1=00252500002524210000012512201010102020002222 (text)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    grid is defiened Name for simplicity in the Name Manager
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then the result array is {0;0;4;0;0;0;6;0;0;0;0;6;1;1;1;1;1;0;0;4}

    Pls refer to the attched file.

    Cheers
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-18-2018
    Location
    Midlands
    MS-Off Ver
    excel 2013
    Posts
    2

    Re: Count consecutive > zero, ignore zero count consecutive > zero next cell

    Thanks All,

    They all work very well, im just determining the best one to use for the purposes im using it for.

    Really appreciate it.

    Kind Regards

+ 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: 7
    Last Post: 03-09-2020, 07:11 PM
  2. [SOLVED] Count Consecutive & Non consecutive days per given logic
    By asimraza89 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-26-2019, 04:31 PM
  3. [SOLVED] Count Consecutive Cell Matches
    By MrTwoe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-02-2018, 03:50 PM
  4. [SOLVED] Count Consecutive Colored Cell Ranges
    By Logit in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-19-2017, 04:46 PM
  5. How do you count consecutive values backward from the last cell?- Please help!
    By asseenontv in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-26-2015, 02:58 PM
  6. [SOLVED] Count number of occurrences but if there are consecutive matches count them as one
    By michaljireht in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-26-2013, 08:26 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