+ Reply to Thread
Results 1 to 4 of 4

Count to next Occurrence (Zero if next)

  1. #1
    Registered User
    Join Date
    08-24-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    38

    Count to next Occurrence (Zero if next)

    Heads or Tails Evens-Heads-Tails
    HEADS WON 0 0 0
    HEADS WON 0 1 0
    TAILS WON 0 0 2
    HEADS WON 0 0 0
    HEADS WON 0 6 0
    TAILS WON 0 0 1
    EVENS WON 11 0 0
    TAILS WON 0 0 0
    TAILS WON 0 0 0
    TAILS WON 0 0 0
    TAILS WON 0 0 3
    HEADS WON 0 0 0
    HEADS WON 0 0 0
    HEADS WON 0 3 0
    TAILS WON 0 0 0
    TAILS WON 0 0 0
    TAILS WON 0 0
    HEADS WON 0
    EVENS WON

    Above is what I want it to look like (manually entered)
    I need a formula for and event of Evens, Heads and Tails
    To count down to the next instance of the event, 0 (Zero) would be if event it is next
    (A Blank would be better than a 0 (Zero) if possible.
    I have tried formula's with Match but no good
    It is really saying the event has not occurred for this many rows.
    Attached Files Attached Files

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Count to next Occurrence (Zero if next)

    Hi
    Try this for "HEADS WON" in D2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Do the same for the others events

  3. #3
    Registered User
    Join Date
    08-24-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    38

    Re: Count to next Occurrence (Zero if next)

    Thanks Jose', that worked perfect!
    Appreciate your help

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,913

    Re: Count to next Occurrence (Zero if next)

    Try this ...

    In C2:

    =IFERROR(IF(OR($B2=$B3,COUNTIF($B2,C$1&"*")=0),"",MATCH(C$1&"*",$B3:$B$20,0)-1),"")

    Copy cross and down.

+ 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. [SOLVED] How to count the order of occurrence of duplicates
    By orehovka in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-28-2023, 11:29 PM
  2. Count number of occurrence between dates
    By Wskip49 in forum Excel General
    Replies: 5
    Last Post: 09-14-2016, 03:06 PM
  3. [SOLVED] count and sum occurrence against a value
    By koshur in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2015, 02:46 AM
  4. SUMIFS Question: Count Occurrence
    By swipethemagnets in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2015, 03:25 PM
  5. Count No. of occurrence of consecutive values
    By rvkadu1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-02-2013, 06:42 AM
  6. [SOLVED] Count Intervals Before Next Occurrence
    By jasonsarea in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-15-2012, 05:10 AM
  7. Replies: 3
    Last Post: 11-11-2008, 12:45 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