excelvbaformulacountifrunning-count

COUNTIF Function, reset count if value equals a specific value

I need to list a running count in column A of each occurrence of the value "4A" in column B, but the count needs to reset to 0 if the value in column B equals "2B".

I have attached a sample of my workbook, column A shows how I need the count to operate. I am open to using VB or a formula.

enter image description here

Or in text format:

| A | B  |
+---+----+
| 0 | 2B |
|   | 3A |
| 1 | 4A |
|   | 4B |
|   | 4B |
| 2 | 4A |
|   | 4B |
| 3 | 4A |
|   | 9A |
| 0 | 2B |
|   | 3A |
| 1 | 4A |
|   | 4C |
| 2 | 4A |
|   | 9A |
| 0 | 2B |
|   | 3A |
| 1 | 4A |
|   | 9A |
| 0 | 2B |
|   | 3A |
| 1 | 4A |
|   | 4C |
|   | 9A |
|   | 9B |
|   | 9Z |
Solution

  • EDIT:

    @Jeeped and @trincot proposed better non-array versions. I modified them slightly here:

    @Jeeped's formula:

    =IF(OR(B1={"2B","4A"}), COUNTIF(INDEX(B:B,AGGREGATE(14, 6, ROW(B$1:B1)/(B$1:B1="2B"), 1)):B1, "4A"),"")

    After modification it uses LARGE function in the aggregate rather than SMALL and "" instead of TEXT(,).

    @trincot's formula:

    =IF(OR(B2={"2B","4A"}),(B2="4A")*(1+IFERROR(INDEX(G:G,MATCH(MAX(G$1:OFFSET(G2,-1,0))+1,G$1:OFFSET(G2,-1,0))),0)),"")

    Compared to original, it's been rearranged slightly 'IF-wise', I also replaced magic 9999 with MAX([...]) + 1 and made it so that you can just paste it in 1st cell and drag down rather than have two separate formulas for first cell and the consecutive ones.

    My original version:

    I think it has the benefit of perhaps being the easiest to understand (? - I am obviously biased here so I might be wrong) and it's physically the shortest of all.

    It has the obvious drawback of being an array-formula.

    In cell "A1" you can write:

    =IF(OR(B1="2B",B1="4A"),SUM((B$1:B1="4A")*(ROW(B$1:B1)>MAX(ROW(B$1:B1)*(B$1:B1="2B")))),"")

    It's an array formula so you have to confirm it with ctrl + shift + enter.

    Then you can drag it down.

    Basically it counts number of occurrences of "4A" in a range extending from current row to the first provided that rows of counted "4A" are larger than that last row of "2B".