COUNTIFs once per row, with multiple criteria in curly braces in ARRAYFORMULA (Google Sheets)

Question

(Note: I found similar questions here, but none of them used the curly braces in Google Sheets, so my question may address a different situation)

I have the following code in Google Sheets:

=IF(B3,
   ArrayFormula(
       SUM(
           COUNTIFS($A$5:$A, "1",
               $B$5:$B, {"*TU Wien*", "*Vienna Univ Techn*", "*Tech?Univ?Wien*", "*TU Vienna*"}
           )
        )
    ),
 "")

Explanation: B3 is a checkbox. If ticked, I want to check whether B5:B contains either "TU Wien" or "Vienna Univ Techn" etc., given the additional criterion that A5:A is 1.

My problem: This code counts a row twice if a cell contains both "TU Wien" and "Vienna Univ Techn" (or any other value in the curly braces).

What I want: It should count each row only once, even if there are multiple matches in a cell.

Test Sheet: Here.

Answer 1

This would be my approach in C3, given the specific details of your example:

=ArrayFormula(IF(B3<>TRUE,,COUNTA(FILTER(B5:B,A5:A=1,NOT(ISERROR(REGEXEXTRACT(B5:B,"[vVwW]ien")))))))

The IF is still assessing whether the checkbox is ticked or not. If not, do nothing. If so, process the rest of the formula.

COUNTA will get a count of the items selected by FILTER.

FILTER will run the data in B5:B through two tests. First, rule in only those rows where A5:A is 1. Second rule in only those remaining elements where the REGEXEXTRACT condition is met [i.e., NOT(ISERROR(...)) means the REGEXEXTRACT condition was met].

The REGEXEXTRACT is looking for a match to "[vVwW]ien"; in plain English, this means "Match anything that starts with 'v' or 'w' (uppercase or lowercase) and then has 'ien' (i.e., it will match 'vien' 'Vien' 'wien' or 'Wien').

If all conditions are met, then COUNTA counts the cell text.