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 three 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.
In this post:
Required knowledge:
1. The easy, COUNTIF part
The first two 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 trickier!
2. The wild(card) part
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 available options as labels:
The final function in cell F9 is as follows:
=COUNTIFS($D$2:$D$7, D9, $F$2:$F$7, "*" & E9 & "*")
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.
- The wildcard must be in quotation marks so that Excel does not interpret it as a multiplication operator