CountIf in upper cells with ArrayFormula

Question

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.

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.

Answer 1

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)))}

Answer 2

I would use a custom function which are a reasonable option for complex array processing in Google Sheets. In B2, enter =valuesWithCounts(A2:A) where the function is placed in the script file (Tools/Script Editor):

function valuesWithCounts(arr) {
  var counts = {};
  var out = [];
  for (var i = 0; i < arr.length; i++) {
    var val = arr[i][0];
    if (val === "") {
      out.push([""]);
    }
    else if (counts[val] === undefined) {
      out.push([val + "-0"]);
      counts[val] = 1;
    }
    else {
      out.push([val + "-" + counts[val]]);
      counts[val]++;
    }
  }
  return out;
}