Excel formula with array referenced from other cells

[Formula11]

I have the formula shown below which includes the array word1, word2, word3 and word4 (* is a wildcard).
=SUM(COUNTIF(References!B2,{"*word1*","*word2*","*word3*","*word4*"}))
When I want to take the array from other cells, it won't work.
=SUM(COUNTIF(References!B2,{"*A2*","*B2*","*C2*","*D2*"})) ... will not work

Is there any way to do it in Excel or VBA.

 

[arthurbr]

Instead of "*A2*" try "*"&A2&"*"

 

[Formula11]

No. Error message: "The formula you typed contains an error".

 

[Fazza]

try using 4 simple COUNTIF formulas

=COUNTIF(whatever,"*"&A2&"*")+COUTIF(whatever, "*"&B2&"*")+etc

 

[Formula11]

Well done, post #4.

 

[Fazza]