# COUNTIFS with multiple OR

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?

• 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

### 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)
``````

• 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. 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:

``````=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)``````