Search code examples
arraysexcelcellcountif

How to get a string array in an Excel cell and use as a reference

I have the following formula:

=SUM(COUNTIF(Country;{"CHN";"SGP";"AUS";"MYS"}))

where Country is a Column. I would like to have a cell containing the array of countries I'm searching for. So for instance cell P3 would have a value like:

{"CHN";"SGP";"AUS";"MYS"}

and the formula would become something like

=SUM(COUNTIF(Country;P3))

But whatever I tried in cell P3

{"CHN";"SGP";"AUS";"MYS"}
"CHN";"SGP";"AUS";"MYS"
"CHN","SGP","AUS","MYS"

or modifying the formula to something like

=SUM(COUNTIF(Country;{P3}))

I can't seem to find the right syntax.. hoping that it is possible.

Also tried to do something like:

=SUM(COUNTIF(Country;TEXTSPLIT(P3,",")))

but to no avail.

Thanks for helping out.

Solution

  • You used semi-colon as your delimiter in COUNTIF() and a comma as the delimiter within TEXTSPLIT(). Therefor I suspect you should use:

    =SUM(COUNTIF(Country;TEXTSPLIT(P3;",")))

    Works for me at least assuming P3 == CHN,SGP,AUS,MYS.

    If that was not the issue then you probably just need to get rid of the quotes in P3.