Help with Reset Counter when condition Met

    Hello, I am in need of some assistance with this type of VBA coding.



    Example -


    Col B Col C


    X X
    X X
    X X
    X X
    X X
    E X
    X X
    X X
    X E
    X X



    I want a counter to show how many X's in a row there is outputted in a different Cell - BUT i want that counter to "reset" when it see's E - then resets Count.


    So from Col B it had 5 in a row - but count gets Resets to 4. and the Count doesnt have to be Automatic (if so be nice) otherwise have a VBA Command key to RUN macro to do it. Any thing will be helpful!


    thanks.

    Re: Help with Reset Counter when condition Met


    Okay, there are a variety of ways to do this, this is a "pretty" one, which doesn't require VBA at all.


    I'm using the range B1:B1000 for this example. This WON'T work with B:B, so you DO need to specifcy less than the entire column. The "best practice" way to do this is with dynamic named ranges (which I'm not doing here, to make it easier to read)


    Put this formula "somewhere" (I've chosen A1) :"=IFERROR(COUNTIF(OFFSET(B1:B1000,LARGE(IF(B1:B1000="E",ROW(B1:B1000)-ROW(INDEX(B1:B1000,1,1))+1),1),0),"X"),COUNTIF(B:B,"X"))". This MUST be entered as an array formula (paste it, and then instead of enter, hit ctrl+shift+enter)


    I can give you a detailed explanation if you need it, but this basically finds the part of the range "B1:B1000" that is below the last "E" and counts the X's in it. it will automatically update as you enter new information.

    Re: Help with Reset Counter when condition Met


    Another variation, slightly different - works the same as that given by richadj4


    =COUNTIF(OFFSET($B$1,MAX(--($B$2:$B$1000="E")*ROW($B$2:$B$1000)),0,10,1),"x")


    For formula simplicity and to slightly reduce array calculations the above formula uses a fixed value ( 10 ) for the row size argument - you can change this to a higher value to pad in extra rows. This argument in the OFFSET function defines how many rows to include in the range


    Or to auto-calculate how many rows are below the last occurrence of "E", use this.


    =COUNTIF(OFFSET($B$1,MAX(--(B2:B1000="E")*ROW(B2:B1000)),0,COUNTA(B2:B1000)+2-MAX(--(B2:B1000="E")*ROW(B2:B1000)),1),"x")



    It, too, is an array formula and must be entered using the Ctrl+Shift+Enter keys in combination

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login