Search code examples
arraysif-statementgoogle-sheetsflattengoogle-query-language

Adding additional text criteria to INDEX Query Flatten


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", ))