I have two sheets, one is a dump sheet of values like below: enter image description here

and the second sheet is using values from this to the total. enter image description here

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?

  • Show us what you've tried so far. We might be able to suggest a small change that allows this to work. Seeing your work will also give us a better idea of your goal and the method you have in mind.
    – Folk
    Jul 26, 2017 at 16:50

2 Answers 2



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.


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


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.

  • 2
    user135384's answer about counta(filter(...)) was very helpful to me, but I needed to modify it to counta(iferror(filter(...))) for it to work for me. The issue is that filter(...) returns an error if it finds nothing, and counta(...) then counts that error and returns 1. Wrapping filter(...) with iferror(...) changes any errors filter(...) returns into blanks, which ensures that counta(...) returns 0 when filter(...) finds nothing.
    – noahtreb
    Dec 9, 2018 at 6:02
  • COUNTIF with OR could be solved like this:

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


  • finding for one value:

     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)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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