Search code examples
arraysregexgoogle-sheetssumgoogle-sheets-formula

How to reference/define an array in one cell?

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.

Solution

  • let D1 be:

    (?i)Tom|Sam|Bill

    and formula:

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