Search code examples
google-sheetslambdagoogle-sheets-formulacountifflatten

Google Sheets ArrayFormula with CountA and Filter

I'm stuck with using counta and filter with arrayformula. Here's my code. =ARRAYFORMULA(IF($A$2:$A="", , COUNTA(FILTER(FLATTEN($W$2:$Z), FLATTEN($W$2:$Z) = A3:A))))

Desired output. I have a list of names on column A and I want to know how many times their name appear on column W:Z without having to drag down the formula because the list on W:Z will keep on updating.

Here's the sample sheet.

Solution

  • Option 1

    =ArrayFormula(
      LAMBDA(r,u, VLOOKUP(r,{UNIQUE(u),COUNTIF(u,"="&UNIQUE(u))},2,0))
            (A3:A,FILTER(FLATTEN(W2:Z),FLATTEN(W2:Z)<>"")))

    enter image description here

    Explanation

    • FLATTEN(W2:Z),FLATTEN(W2:Z)<>"") to get all the columns in W2:Z in one array, and its "stored" in a Lambda() call and named u (you can name it anything) so we can recall it in the formula without repeating the references like the option 2 below.

    the lookup range {1,2}

    • 1 UNIQUE(u) this is just as saying UNIQUE(FILTER(FLATTEN(W2:Z),FLATTEN(W2:Z)<>""))

    • 2 COUNTIF(u,"="&UNIQUE(u)) its a simple countif

    • VLOOKUP(r,{UNIQUE(u),COUNTIF(u,"="&UNIQUE(u))},2,1) r is lambda name of the range A3:A, this is a normal vlookup

    Just like saying "not a formula!"

    VLOOKUP(names in col A,{UNIQUE(names in col w,x,y,z),COUNTIF(names in col w,x,y,z,"="&UNIQUE(names in col w,x,y,z))},2,0)

    Option 2
    This explains the previous formula its the same without using the new Lambda() function.

    =ArrayFormula(IF(A3:A="",,VLOOKUP(A3:A, {UNIQUE(FILTER(FLATTEN(W2:Z),FLATTEN(W2:Z)<>"")),COUNTIF(FILTER(FLATTEN(W2:Z),FLATTEN(W2:Z)<>""),"="&UNIQUE(FILTER(FLATTEN(W2:Z),FLATTEN(W2:Z)<>""))&"")},2,0)))

    enter image description here

    Used formulas help
    ARRAYFORMULA - LAMBDA - VLOOKUP - UNIQUE - COUNTIF - FILTER - FLATTEN - IF