Count number of rows with certain criteria that can appear in any column

Question

In Google Sheets, I need to return the count of rows that contain two criteria within a range of columns. The criteria can appear in any of 3 columns. For example:

 |  A           B           C
1|  external    FSO        
2|  FSO         internal    test
3|  test        AI          internal
4|  FSO         test        external
5|  AI          internal     
6|  FSO
7|  external    test        FSO
8|  external    test      

If I needed to return the count all columns with FSO and external, I would return a count of 3 since they appear in rows 1,4, and 7.

How could I accomplish this? My original query does not work: =COUNTIFS(Issues!$E$2:$I$500,”external”,Issues!$E$2:$I$500,”FSO”)

Answer 1

Use a filter() with two conditions, like this:

=count( 
  iferror( 
    filter( 
      row(Issues!A1:C), 
      regexmatch(Issues!A1:A & Issues!B1:B & Issues!C1:C, "FSO"), 
      regexmatch(Issues!A1:A & Issues!B1:B & Issues!C1:C, "external") 
    ) 
  ) 
)