Adding additional text criteria to INDEX Query Flatten-CodePudding

Our team has a work calendar on Google Sheets to track company holidays. We're using a formula that counts the number of times the word 'Hol' appears in a row. The below snippet works, but I am also looking to count additional terms such as BH and Half. How would I add additional words to my array?

=INDEX(QUERY(QUERY(FLATTEN(IF(7:7="Hol", ROW(7:7), )), "select count(Col1) group by Col1"), "offset 2", ))

The ideal scenario is to tweak the text criteria part of the formula, but it's not counting the array correctly.

7:7="Hol","Half","BH"

I have made a test sheet here: https://docs.google.com/spreadsheets/d/1ODPNn81m8r2bfZgJqZR80ad5n1Wnsi513c40fQncsF0/edit?usp=sharing

Thanks for your help.

CodePudding user response：

try:

=INDEX(QUERY(QUERY(FLATTEN(IF(REGEXMATCH(7:7, "Hol|Half|BH"), ROW(7:7), )), 
 "select count(Col1) group by Col1"), "offset 2", ))

or OR logic if regex is a no go:

=INDEX(QUERY(QUERY(FLATTEN(IF((7:7="Hol") (7:7="Half") (7:7="BH"), ROW(7:7), )), 
 "select count(Col1) group by Col1"), "offset 2", ))