Excel - COUNTIFS with an OR?

Asked By RobofM on 19-Jan-09 06:31 PM
```Can countifs work with an "OR" in it? Seperating in the below formula the BO
& AY criteria into 2 countifs works but I would like to do it with just 1
countifs if possible.

Doesn't
work:=COUNTIFS(AMC!\$C\$2:\$C\$1796,OR(\$BO\$67,\$AY\$67),AMC!\$R\$2:\$R\$1796,"="&\$A85,AMC!\$E\$2:\$E\$1796,"="&CT\$67)

Does
work:=COUNTIFS(AMC!\$C\$2:\$C\$1796,\$BO\$67,AMC!\$R\$2:\$R\$1796,"="&\$A85,AMC!\$E\$2:\$E\$1796,"="&CT\$67)+COUNTIFS(AMC!\$C\$2:\$C\$1796,\$AY\$67,AMC!\$R\$2:\$R\$1796,"="&\$A85,AMC!\$E\$2:\$E\$1796,"="&CT\$67)```

T. Valko replied on 19-Jan-09 06:49 PM
```You can try something like this:

The "OR" criteria has to be entered as array constants, not cell references.
If the criteria is TEXT enclose it in quotes:

=SUM(COUNTIFS(AMC!\$C\$2:\$C\$1796,{"x","y"},...........

=SUM(COUNTIFS(AMC!\$C\$2:\$C\$1796,{10,20},...........

--
Biff
Microsoft Excel MVP```
RobofM replied on 19-Jan-09 07:04 PM
`Thank you :)`
T. Valko replied on 19-Jan-09 08:58 PM
```You're welcome!

--
Biff
Microsoft Excel MVP```