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.
>Solution :
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", ))