4

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. – Rubén Mar 12 '17 at 12:52
  • 1
    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/… – Rubén Mar 12 '17 at 13:31
  • 1
    Another answer using MMULT: webapps.stackexchange.com/a/99772/88163 – Rubén Mar 12 '17 at 14:02
  • 1
    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. – Rubén Mar 12 '17 at 15:31
  • 1
    I did, also simplified the problem to the core of „restarting“ the counter. – Marcus Blättermann Mar 12 '17 at 16:26
3
+100

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

  • I haven’t fully wrapped my head around what is going on in these forumals, but it definitely works. Thank you! – Marcus Blättermann Mar 21 '17 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.