# Count number of consecutive entries with Arrayformula in 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.

• We know that the first row result always will be one, so, add 1 on the first row, and the formula on the second row and below. Commented Mar 12, 2017 at 12:52
• I think that some users will require the information in my comment. Regarding the question, try MMULT. For a similar question, with an answer using MMULT, see webapps.stackexchange.com/questions/88840/… Commented Mar 12, 2017 at 13:31
• Another answer using MMULT: webapps.stackexchange.com/a/99772/88163 Commented Mar 12, 2017 at 14:02
• I think that somewhere I already posted an answer a to a similar question, not sure in English or Spanish, I will search for it later. Commented Mar 12, 2017 at 15:31
• I did, also simplified the problem to the core of „restarting“ the counter. Commented Mar 12, 2017 at 16:26

# 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

• I haven’t fully wrapped my head around what is going on in these forumals, but it definitely works. Thank you! Commented Mar 21, 2017 at 15:02

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
``````