+ Reply to Thread
Results 1 to 7 of 7

Restart count function after meeting condition

  1. #1
    Registered User
    Join Date
    12-14-2017
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    6

    Restart count function after meeting condition

    What formula can be used to ensure that the count function will reset once meeting a condition

    Example:
    A B
    1
    1
    1
    1
    1
    0 5 <-- Count
    1
    1
    0 8 <-- I want this value to be 2 because i only want it to count the 1 and once there is a 0, I want the count to reset.

    All help is appreciated thanks in advance

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,289

    Re: Restart count function after meeting condition

    Start from B2 then drag down:

    =IF(A2=0,COUNTIF($A$1:A2,1),"")

    Untitled.png
    Quang PT

  3. #3
    Registered User
    Join Date
    12-14-2017
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    6

    Re: Restart count function after meeting condition

    Hi bebo021999,

    Thanks for the reply but this formula is not working for some reason.
    I still get "7" on cell B9 instead of "2".
    bebo.PNG

  4. #4
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Restart count function after meeting condition

    hi Jepri, try below with slight modification

    =IF(A2=0,COUNTIF(A$1:A2,1)-SUM(B$1:B1),"")
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin

  5. #5
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,924

    Re: Restart count function after meeting condition

    Try with a helper column.

    B2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Row\Col
    A
    B
    C
    1
    Data HelperColumn Result
    2
    1
    1
    3
    1
    2
    4
    1
    3
    5
    1
    4
    6
    1
    5
    7
    0
    1
    5
    8
    1
    2
    9
    1
    3
    10
    0
    1
    2
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  6. #6
    Registered User
    Join Date
    12-14-2017
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    6

    Re: Restart count function after meeting condition

    Thanks Alvin that formula works.

  7. #7
    Registered User
    Join Date
    12-14-2017
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    6

    Re: Restart count function after meeting condition

    Thanks Shukla for the help

+ 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. vba code for count restart formula if condition
    By pedersenn in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-18-2017, 02:29 AM
  2. [SOLVED] Restart count everytime value in adjacent column changes
    By Niklasie in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-06-2017, 07:24 AM
  3. Restart a Count within a Column
    By cookthebooks80 in forum Excel General
    Replies: 3
    Last Post: 07-06-2014, 11:46 AM
  4. Replies: 11
    Last Post: 06-19-2014, 09:34 AM
  5. Replies: 8
    Last Post: 01-30-2013, 02:58 PM
  6. count sessions then restart 365 days after - help
    By itsnotfair in forum Excel General
    Replies: 2
    Last Post: 06-29-2011, 09:31 AM

Tags for this Thread

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