google-sheets

Increment a number based on alternating values in another column


Bit of a tricky one. I need a formula that can accomplish this set of numbers.

1 Alpha
2 Alpha
1 Beta
3 Alpha
2 Beta
3 Beta
4 Alpha
4 Beta

Essentially, the number starts at 1 with the first appearance of the string "Alpha" in the next column. Every additional instance of "Alpha" increases that count by 1. Separately, the first appearance of the string "Beta" also starts at 1 and increases with additional instances of "Beta", without interfering with the Alpha count.

Ideally, the counter resets after a blank row, or can otherwise be adjusted to work in blocks of cells:

[...] [...]
4 Alpha
4 Beta
1 Alpha
1 Beta
2 Alpha
2 Beta
3 Beta

Is this possible?

I tried various [IF] and [ARRAYFORMULA] statements I found elsewhere, but none accomplished the exact alternation I need.


Solution

  • Here's a solution:

    =IF(A1="", "", COUNTIF(A$1:A1, A1) - COUNTIF(A$1:INDEX(A:A, MAX(IF(A$1:A1="", ROW(A$1:A1)))-1), A1))

    I put the Alphas and Betas in A column on Google Sheets. Formula checks if A1 is blank, does the counting if the cell is not blank, and otherwise resets the counter.

    Alpha   1
    Beta    1
    Beta    2
    Beta    3
        
    Alpha   1
    Beta    1
    Beta    2
        
    Beta    1
    Beta    2
    Beta    3
    Alpha   1