According to Google's Query Language Reference, string literals should be
enclosed in either single or double quotes. Examples: "fourteen" 'hello world' "It's raining".
You are joining the Attendance values by
| only, which again is enclosed in
's, so if
the result of the
"... '" & JOIN("|", Attendance!A2:A) & "' ..." would be
As you can see, this leads to unmatched
' - the string seemingly ends and begins with a
', but the apostrophe in
O'Malley shortcuts the string so that it is really
You could use
"" instead of
' when creating the string:
=COUNTA( Iferror(QUERY(Requirements!A2:F, "select A where A matches """ & JOIN("|", Attendance!A2:A) & """ and B = 'Yes'", 0)))
Note how the double quotation mark
"" allows the character to be escaped, so it does not short-circuit the string.
This would allow
O'Malley to be counted, but would be trouble if you have a name that includes
". To avoid all sorts of character issues, your formula should be rewritten so that it does not rely on creating a query by concatenating input strings.