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.
CodePudding user response：
every next row is by logic an incrementation of the previous row 1. what this formula does it checks the given row number against row number. for example, for row 4, COUNTIFS checks if ROW(A4) is smaller or equal to ROW(A4). then the evaluation is "yes, row 4 is equal to row 4" and the output is TRUE. what COUNTIF actually does is counting these TRUE outputs up to every row summing all the previous rows. something like:
| rows | COUNTIFS processing | output | counting TRUES | final output |
|---|---|---|---|---|
| row 1 | row 1 equal to row 1? | TRUE | 1st TRUE | 1 |
| row 2 | row 2 equal to row 2? | TRUE | 2nd TRUE | 2 |
| row 3 | row 3 equal to row 3? | TRUE | 3rd TRUE | 3 |
| row 4 | row 4 equal to row 4? | TRUE | 4th TRUE | 4 |
| row 5 | etc | etc | etc | etc |
CodePudding user response：
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)))