COUNTIFS with multiple OR

Question

I have two sheets, one is a dump sheet of values like below:

and the second sheet is using values from this to the total.

I am trying to get the total count for the corresponding zone and test name but only if the clash status column says "New" or "Active".

I've tried multiple ways but I am not getting the syntax correct, can anyone help?

Answer 1

countifs

One way is to use countifs(..., D:D, "New") + countifs(..., D:D, "Active") -- addition works as long as the options in OR are mutually exclusive.

filter

More generally, the combination counta(filter(...)), which is more powerful than countifs, can express OR logic by addition:

=counta(filter(A:A, A:A = "name", B:B = "zone", (D:D = "New") + (D:D = "Active"))

query

Yet more generally, query can return such results for all names and zones at once:

=query(A:D, "select A, B, count(D) where D = 'New' or D = 'Active' group by A, B', 1)

Further reading: filter, query.

Answer 2

• COUNTIF with OR could be solved like this:

  =ARRAYFORMULA(SUM(N(REGEXMATCH(A1:A, "new|active"))))

  

---

• finding for one value:

  =ARRAYFORMULA(COUNTA(IFERROR(FILTER(A1:A, A1:A="Zone A", 
   REGEXMATCH(B1:B, "new|active")))))

  

---

• finding for all values:

  =QUERY(A:B, "select A,count(B) 
           where B matches 'active|new' 
           group by A 
           label count(B)''", 0)