How to understand this array formula

Question

=ARRAYFORMULA(COUNTIFS(COUNTIFS(A2:A, A2:A), 1, ROW(A2:A), "<="&ROW(A2:A)))

I tried to understand by breaking up the formula bit by bit in non array version, by this I understood:

=ARRAYFORMULA(COUNTIFS(ROW(A2:A), "<="&ROW(A2:A)))

(Which gives serial numbers)

I really don't understand anything else, for example in column E:

=ARRAYFORMULA(COUNTIFS(A2:A, A2:A))

Here I don't understand why the blank cells have count 0, does that mean COUNTIFS doesn't count blanks? If yes then when I type ="" at A6 only why value of D6 only turns 950? Why not of other rows which are blank?

And then when the COUNTIFS is branched inside another COUNTIFS I completely failed to understand anything further.

How can I understand this in detail?

user_5632629 · Answer

let's start with inner COUNTIFS and do this in terms of arrays. if you run:

=ARRAYFORMULA(COUNTIFS(A2:A, A2:A))

it means that you are "counting unique values in A column, only if there is a value in A column". so no value in A column returns 0 and the rest is counted. the first range A2:A stands for a range we search on and the 2nd A2:A range is a criterion - if the cell in A column is empty it returns FALSE eg. 0 and if it's not empty it returns TRUE in form of a count of unique item. see:

and:

now let's take a look at the outer COUNTIFS. the 4th argument of outer COUNTIFS is an array so it overrides the 2nd argument of outer COUNTIFS which is just a single value. so if we run it as is the result is a sum of all ones in K column:

=ARRAYFORMULA(COUNTIFS(COUNTIFS(A2:A, A2:A), 1))

therefore to understand it in its array form we need to alter it a bit by adding the 3rd and 4th arguments to the outer COUNTIFS:

=ARRAYFORMULA(COUNTIFS(COUNTIFS(A2:A, A2:A), 1, 
 ROW(A2:A), "<="&ROW(A2:A)))

which is essentially the:

=ARRAYFORMULA(COUNTIFS(K2:K, 1, 
 ROW(A2:A), "<="&ROW(A2:A)))

and that translates as "count how many 1's are in K2:K range on every row ROW(A2:A) up to the current row of every row "<="&ROW(A2:A) and loop this row-by-row". in other words, it is the same as you would drag =SUM(K$2:K2) down the column

important note

this formula works only if all values in A column are unique. if they aren't then the formula will brake:

the correct formula for this task would be:

=ARRAYFORMULA(VLOOKUP(VLOOKUP(ROW(A2:A), IF(A2:A<>"", {ROW(A2:A), A2:A}), 2, 1), 
 {UNIQUE(FILTER(A2:A, A2:A<>"")), SEQUENCE(COUNTUNIQUE(A2:A))}, 2, 0))

or if the set is not sorted:

=ARRAYFORMULA(VLOOKUP(VLOOKUP(ROW(A2:A), IF(A2:A<>"", 
 {ROW(A2:A), A2:A&COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))}), 2, 1), 
 {UNIQUE(FILTER(A2:A&COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A)), 
 A2:A<>"")), SEQUENCE(COUNTA(A2:A))}, 2, 0))

also, keep in mind that a truly empty cell is not equal to a cell with the formula that outputs an empty string =""

How to understand this array formula

1 Answers

important note