This solution was arrived at whilst doing some advanced analysis for a Matric PAT where data from a Google Form questionnaire is being analysed. A closed question required respondents to select 3 of their favourite ice-cream flavours from a list of options. The resulting data appears in the Excel workbook as comma-separated values in a single column. A COUNTIFS function is being used to evaluate data where one of the ranges contains multiple, comma-separated values.
The first 2 arguments of the COUNTIFS function present no new challenges and should be familiar territory: a range and criteria. The criteria for the second range, however, is somewhat more tricky!
We are searching for the string “chocolate” in a larger string. The larger string could contain the string “chocolate” in any position (the respondent chose chocolate as one of their options, or no position (the respondent did not choose chocolate as an option). To achieve this, we employ the wildcard: *. The asterisk is the wildcard; the wildcard is a placeholder for any combination of characters or no characters. You will see the wildcard used extensively in Microsoft Access.
In the example below, I have carefully laid out all the possible options as labels.
The final function is as follows:
In the above functions, the difficult part is the method used to express the criteria: “*”&E9&”*”
The ampersand (&) is used to concatenate (join) the wildcard to the beginning and the end of the value in cell E9.