Google-sheets – CountIf in upper cells with ArrayFormula

formulasgoogle sheetsgoogle-sheets-arrayformula

I have some values in the column A and in the column B want to calculate concatenation of the value and how many times it was used in rows above.

In the usual formula, I would've written in B2

=A2 & " - " & COUNTIF(A$1:A1; A2)

and spread it down getting

=A2 & " - " & COUNTIF(A$1:A1; A2)
=A3 & " - " & COUNTIF(A$1:A2; A3)
=A4 & " - " & COUNTIF(A$1:A3; A4)
=A5 & " - " & COUNTIF(A$1:A4; A5)

and so on.

screenshot

But the first column is generated from the auto-populated (by google forms) sheet via

=ARRAYFORMULA('Ответы на форму (1)'!A2:A)

So I'd like the formula in B to calculate a value for all rows.

How can I write the same thing using ARRAYFORMULA?

I've tried

=ARRAYFORMULA(A2:A & " - " & COUNTIF(A$1:A1;A2:A))
=ARRAYFORMULA(A2:A & " - " & COUNTIF(A1:A;A2:A))

But they both are wrong.

Best Answer

wrapping simple COUNTIF into ARRAYFORMULA won't do the job, mostly because ARRAYFORMULA doesn't understand that COUNTIF already uses an array, so you need to count it by different means.

=ARRAYFORMULA(IF(LEN(A2:A); A2:A&" - "&
 MMULT((A2:A = TRANSPOSE(A2:A)) * (ROW(A2:A) >= TRANSPOSE(ROW(A2:A))); 
 SIGN(ROW(A2:A)))-1; IFERROR(1/0)))

if you need to put it in B1 use this:

={"Value - upper"; ARRAYFORMULA(IF(LEN(A2:A); A2:A&" - "&
 MMULT((A2:A = TRANSPOSE(A2:A)) * (ROW(A2:A) >= TRANSPOSE(ROW(A2:A))); 
 SIGN(ROW(A2:A)))-1; IFERROR(1/0)))}

Related Topic