0

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”)

1
0

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") 
    ) 
  ) 
)
1
  • I've been bashing my head against this for hours, and this solved it. Thanks a ton! – Bradley Camacho Jun 4 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.