The following works as expected and counts all the cells in the range A2:P2 that contains either PH, V or O.

=SUM(COUNTIFS(A2:P2;{"PH";"V";"O"}))

I have to reuse this several places and therefore want to place the criteria array in a cell and read it from there. Something like: =SUM(COUNTIFS(A2:P2;"&A1&")) where cell A1 contains {"PH";"V";"O"}.

Is it possible to parse the text from A1 into the formula?

CodePudding user response：

Assuming that you're using Excel 2013 or later, and that A1 contains the text PH;V;O the formula below should work

=SUM(COUNTIFS(A2:P2;FILTERXML("<c><e>"&SUBSTITUTE(A1;";";"</e><e>")&"</e></c>";"//e")))

(this will have to be entered as an array formula if you don't have dynamic arrays)