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

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

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