Count number of consecutive entries with Arrayformula in Google Sheets

Question

I want to count the number of consecutive times the same value appears until the given row, until another value appears then start counting from zero again. And I want to use an arrayformula so I don’t have to copy it down all rows.

Input   Count
Foo        1
Foo        2
Foo        3
Bar        
Bar        
Foo        1
Foo        2

A way that works is something like this if you copy it all the way down:

=IF( A2="Foo", 1, B1+1 )

The problem is a formula like this doesn’t work for arrayformula, because you can’t reference the preceding row, because this gives you an error for circular dependencies.

I think some combination of filter, query and countif might work but can‘t figure out how.

EDIT: I tried to use mmult as suggested but that doesn’t seem to be able to solve the problem, because I can only count row by row. I can count all the rows with a certain value like this:

=ArrayFormula(MMULT(N(
    ROW(A2:A)>=TRANSPOSE(ROW( A2:A))),
    N(A2:A="Foo"))
)

but I can’t reset the counter each time a different input appears.

Answer 1

Summary

Below there are two alternatives, one that uses MMULT which doesn't require an auxiliary formula and one that uses SUMIF but it requieres an auxiliary formula.

NOTE: Open ended references could make that recalculation time be very large. One alternative to reduce recalculation time is to remove empty rows below the data, another is to use FILTER(A2:A,LEN(A2:A)) instead of using just A2:A.

Formula with MMULT

This doesn't require an auxiliary column

=ArrayFormula(
  IF(
    A2:A8="Foo",
    MMULT(N(ROW(A2:A8)>=TRANSPOSE(ROW(A2:A8))),N(A2:A8="Foo"))
    -
    HLOOKUP(
      0,
      MMULT(N(ROW(A2:A8)>TRANSPOSE(ROW(A2:A8))),N(A2:A8="Foo")),
      MATCH(
        VLOOKUP(ROW(A2:A8),IF(N(A2:A8<>A1:A7),ROW(A2:A8),),1,TRUE),
        VLOOKUP(ROW(A2:A8),IF(N(A2:A8<>A1:A7),ROW(A2:A8),),1,TRUE),
        0
      ),
      FALSE
    ),
  )
)

Formula with SUMIF

This require an auxiliary formula, becaus SUMIF require a reference, as the third argument

Auxiliary formula

Formula in B2: =ArrayFormula(N(A2:A8="Foo"))

Main formula

=ArrayFormula(
  IF(
    A2:A8="Foo",
    SUMIF(ROW(A2:A8),"<="&ROW(A2:A8),B2:B8)
    -
    HLOOKUP(
      0,
      SUMIF(ROW(A2:A8),"<"&ROW(A2:A8),B2:B8),
      MATCH(
        VLOOKUP(ROW(A2:A8),IF(N(A2:A8<>A1:A7),ROW(A2:A8),),1,TRUE),
        VLOOKUP(ROW(A2:A8),IF(N(A2:A8<>A1:A7),ROW(A2:A8),),1,TRUE),
        FALSE),
      FALSE
    ),
  )
)

Reference

Answer by AdamL to ArrayFormula to compute Running Average for groups of rows

Answer 2

Here's a solution based on my matrix. A bit different. Column E is calculating the consecutive days of weight loss. The formula is =IF(C4="","",IF(C4>0,IF(E3="",1,E3+1),"")).

                     4 lbs lost       1 lbs average daily loss  Days of Consecutive Weight Loss
Weight Loss Tracker  Morning Weight   Daily weight loss     
9/21/2021            195.2          
9/22/2021            192.2            3.00  lbs                 1
9/23/2021            191.2            1.00  lbs                 2
9/24/2021            191               .20  lbs                 3
9/25/2021            195             -4.00  lbs     
9/26/2021            192              3.00  lbs                 1
9/27/2021            191              1.00  lbs                 2
9/28/2021               
9/29/2021               
9/30/2021