+ Reply to Thread
Results 1 to 3 of 3

Count consecutive networkdays rows with a cell containing "Yellow", "Red" and reset count

  1. 02-04-2021, 08:40 AM #1
    ddander54
    ddander54 is offline
    Registered User
    Join Date
    03-09-2011
    Location
    Minnesota, USA
    MS-Off Ver
    Excel 2016, O365
    Posts
    18

    Count consecutive networkdays rows with a cell containing "Yellow", "Red" and reset count

    I am capturing 3 columns of data (~150-180 rows) every day in order to count the consecutive status if it is "Yellow" or "Red". If it is green (or "None", or blank) I want to reset the counter to 0. The reason for the counts will be to determine the consecutive Yellow or Red that exceeds some value (ie, >=5), and then determine how many times (the sets of 5) the condition of Yellow or Red occurred in some period (ie, 15 days).
    This link below provides some examples of how to get the current consecutive which is also valuable, but I also need it to count how many times the consecutive condition occurred in a timeframe.
    Linkback: https://www.excelforum.com/excel-gen...s-entered.html

    Adding helper columns for formulas would be ok, but I also like MarvinP's function as well. Also, If there is a better way to format the data, I'm open to that as well.

    I would appreciate any help I can get....

    TIA,
    Don

    Based on 254 working days in our calendar year this table will grow to approximately ~40,000-46,000 rows in a year.
    The Data:
    ID | Date | Color
    50394 1/11/2021 YELLOW
    50394 1/12/2021 YELLOW
    50394 1/13/2021 YELLOW
    50394 1/14/2021 YELLOW
    50394 1/15/2021 YELLOW
    50394 1/18/2021 YELLOW
    50394 1/19/2021 YELLOW
    50394 1/20/2021 YELLOW
    50394 1/21/2021 YELLOW
    50394 1/22/2021 YELLOW
    50394 1/25/2021 YELLOW
    50394 1/26/2021 YELLOW
    50394 1/27/2021 YELLOW
    50394 1/28/2021 YELLOW
    50394 1/29/2021 GREEN
    50394 1/31/2021 GREEN
    50394 2/1/2021 GREEN
    50394 2/2/2021 YELLOW
    50394 2/3/2021 YELLOW
    49163 1/11/2021 GREEN
    49163 1/12/2021 GREEN
    49163 1/13/2021 GREEN
    49163 1/14/2021 GREEN
    49163 1/15/2021 GREEN
    49163 1/18/2021 GREEN
    49163 1/19/2021 GREEN
    49163 1/20/2021 YELLOW
    49163 1/21/2021 YELLOW
    49163 1/22/2021 YELLOW
    49163 1/25/2021 YELLOW
    49163 1/26/2021 YELLOW
    49163 1/27/2021 YELLOW
    49163 1/28/2021 YELLOW
    49163 1/29/2021 GREEN
    49163 1/31/2021 GREEN
    49163 2/1/2021 GREEN
    49163 2/2/2021 GREEN
    49163 2/3/2021 GREEN
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-09-2021, 12:36 PM #2
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,567

    Re: Count consecutive networkdays rows with a cell containing "Yellow", "Red" and reset co

    If I interpret the request correctly then there will need to be a count of the number of yellows and reds per ID.
    To accomplish this put the following in cell D2 and copy down: =IF(OR(C2="Green",C2="None"),0,1)
    There will need to be some user inputs:
    Number of days in cell G1
    Start date in cell I1
    Size of set in cell G2
    The list of distinct IDs in column H is produced using: =IFERROR(INDEX($A$2:$A$4824,MATCH(0,INDEX(COUNTIF(H$3:H3,$A$2:$A$4824),,),)),"")
    The number of sets is produced using: =IF(H4="","",ROUNDDOWN(SUMIFS(D$2:D$4824,A$2:A$4824,H4,B$2:B$4824,">="&I$1,B$2:B$4824,"<="&J$1)/5,0))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  3. 02-11-2021, 12:52 PM #3
    ddander54
    ddander54 is offline
    Registered User
    Join Date
    03-09-2011
    Location
    Minnesota, USA
    MS-Off Ver
    Excel 2016, O365
    Posts
    18

    Re: Count consecutive networkdays rows with a cell containing "Yellow", "Red" and reset co

    JeteMc,

    Thank you for the solution. I have been looking at this for the last couple of days with additional days of data and other variations of R, Y, G, by day to get a better understanding of your solution and it seems to work for any scenarios that I have thrown at it, so thanks again.
    Of course my leadership has changed their mind now and want to have different criteria for Yellow and Red, so I'll be separating the YorR column (D) out into 2 columns and modifying the 'counter column' (I) formula appropriately, but I think I can handle that now that you have done all the 'heavy lifting'.

    Great job!
    Don
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to count the numnber of "agree", "disagree" and "N/A" in a filtered list
    By mbeginner in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2015, 01:13 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. [SOLVED] Cell Formula to count time with "Greater Than or Equal to" and "Less Than"
    By chriswhite1982 in forum Excel General
    Replies: 3
    Last Post: 06-16-2013, 12:30 AM
  4. Return "green", "yellow" or "red" from date/age and priority ranking
    By Cantaloop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2013, 12:12 AM
  5. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  6. Count the unique items in column F if E:E = "Chris" and M:M = "VIC" and N:N = "PT"
    By tyrese214 in forum Excel General
    Replies: 1
    Last Post: 07-16-2010, 02:44 AM
  7. Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next
    By BCB in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 05-13-2006, 05:02 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1