Search code examples
excelgoogle-sheetsexcel-formulaarray-formulas

Increment count in column based on value in column

I've 2 columns A and B. A contains names and B contains the count of those names till that record as shown below. 

-----------------------------------
      |      A      |      B      |
-----------------------------------
1     | Fruits      |      1      |
2     | Flowers     |      1      |
3     | Fruits      |      2      |

So, want to have a formula for this. Expecting an array formula. Even if an array formula is not possible, a general formula

Attached a spreadsheet so that it can be explained better.

https://docs.google.com/spreadsheets/d/1wlWqdFwgv90s50iP-bXXBHciyualohj610qFiSatcmQ/edit#gid=1997586177

Solution

  • In a google spreadsheet you may want to try:

    =ArrayFormula(iferror(SORT(ROW(A1:A),SORT(ROW(A1:A),A1:A,1),1)-MATCH(A1:A,SORT(A1:A),0)-ROW()+2))

    Example sheet