Search code examples
google-sheetsgoogle-sheets-formula

ARRAYFORMULA Auto-Increment

I'm trying to understand this auto-increment formula, that my colleague has written. I understand how arrayformula usually works and also countifs.

Formula from the screen: =ARRAYFORMULA(COUNTIFS(ROW(B2:B), "<="&ROW(B2:B)))

I'm stacked about why ROW(B2:B) (1param in COUNTIFS) as a range works fine. It should be a range, not just a number that ROW function returns.

I have been trying to find an answer, read documentation, but nothing helped.

I think that, for example, for 4th line the formula would look like this (if we seperate from ARRAYFORMULA):

COUNTIFS(ROW(B4:B), "<="&ROW(B4:B)),

COUNTIFS(4, "<=4")

I need to understand this code, not other solutions.

enter image description here

Solution

  • The best way to understand how an ARRAYFORMULA works is to write down the equivalent drag-down formula.

    The equivalent drag-down formula for:

    =ARRAYFORMULA(COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B)))

    is

    =ARRAYFORMULA(COUNTIFS($B$2:$B, B2, ROW($B$2:$B), "<="&ROW(B2)))
=ARRAYFORMULA(COUNTIFS($B$2:$B, B3, ROW($B$2:$B), "<="&ROW(B3)))
=ARRAYFORMULA(COUNTIFS($B$2:$B, B4, ROW($B$2:$B), "<="&ROW(B4)))
...

    (We only need ARRAYFORMULA because ROW($B$2:$B) is an array formula, which means that if you type it in a cell without wrapping it an an array-enabling function it will only evaluate ROW($B$2))

    If we recall the COUNTIFS parameters:

    =COUNTIFS(criteria_range1, criterion1, criteria_range2, criterion2)

    We can see that in the drag-down formula, every parameter that by default expects a range, becomes an absolute reference and every parameter that by default does not expect a range, is just a single value that increments each row.

    This is true for any other function: if a function has a parameter that by default expects a range, when we wrap it in ARRAYFORMULA(), that range stays the same throughout the entire computation, which means that every single value in that range is seen by the array formula at any time. What increments, and therefore is only seen by the array formula on that specific row, are the parameters that do not natively expect a range.

    This seems like an obvious observation but I'm sure it's the reason many people are confused about how that formula works.

    If you understand this concept, then you can also understand how the other variants of the formula work:

    =ARRAYFORMULA(COUNTIFS(B2:B, B2:B, ROW(B2:B), "<"&ROW(B2:B)))
=ARRAYFORMULA(COUNTIFS(B2:B, B2:B, ROW(B2:B), ">="&ROW(B2:B)))
=ARRAYFORMULA(COUNTIFS(B2:B, B2:B, ROW(B2:B), ">"&ROW(B2:B)))