Using a dynamic string array as argument in Excel function CountIf

Question

Hi,
I'm using COUNTIF in Win11/Excel MS 365 to search för apperences of multiple values, e.g COUNTIF(D5:D81;{"apple";"banana";"lemon"}).
This is working great but I want the second argument to be dynamic. I guess the curly braches are somehow involded why this isn't working. Or, do I need a typecast of the values? It always ends upp in a function error, (syntax error).
I tried these just to se if I could use the reference.

COUNTIF(D5:D81, {F5;F6;F7})

COUNTIF(D5:D81, {F5:F7})
I guess what I want is something like F5= ""apple""&;&""banana""&;&""lemon""

COUNTIF(D5:D81, {F5}) but that also ends upp in a function error

Answer 1

? You would just insert a cell into your existing list, and add it there.

Or use a dynamic named range - define the name List, with the formula

=OFFSET(Sheet1!$AC$1,0,0,COUNTA(Sheet1!$AC:$AC),1)

(Choose any column that you are not using and start your list in row 1 of that column, and then add new entries at the bottom of the list. Then use\

=COUNTIF(D:D, List)

Or enter your comma-delimited list into a cell, and use

=COUNTIF(D:D,TEXTSPLIT(F5,,",")) 'to return a vertical array

=COUNTIF(D:D,TEXTSPLIT(F5,",")) 'to return a horizontal array

Where F5 has apple,banana,lemon

Answer 2

That's great! Thanks.
Range is working
But I still don't get how I build the string.

In case I add more search strings I just want to
change it in one place.
"apple""&;&"banana""&;&"lemon"
is obviously wrong

Answer 3

No need for the curly braces:

=COUNTIF(D5:D81, F5:F7)

Of course, if you use ; change the , to ;