Google Sheets Arrayformula – Count Number of Consecutive Entries with Arrayformula in Google Sheets

google sheets

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.

Best Answer

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

Related Topic