How to reference/define an array in one cell?-CodePudding

I'm using countifs with an array to search for and count specific names from a column.

=ArrayFormula(SUM(COUNTIFS(A1:C10,{"Tom"; "Sam"; "Bill"})))

However, the array is likely to change, so rather than change the formula, I want to use a separate cell to "store" the array for the formula to reference.

=ArrayFormula(SUM(COUNTIFS(A1:C10,D1))) Where D1 = {"Tom"; "Sam"; "Bill"}

Is there a way to reference an array from a different cell in a formula? Or is there a work around to accomplish the same thing? The "array reference cell" needs to be a single cell.

CodePudding user response：

let D1 be:

(?i)Tom|Sam|Bill

and formula:

=SUMPRODUCT(REGEXMATCH(A1:C10; D1)*1)

CodePudding user response：

Use this formula to count the number of times each name appears in the list "Array" To Count in column E.

=SUMPRODUCT(REGEXMATCH(A1:C; 
 REGEXREPLACE(TEXTJOIN("|";;UNIQUE($E$2:$E));".\z";"")))