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.

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

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

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.