COUNTIFS with an OR?
 

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)+COUNTIF S(AMC!$C$2:$C$1796,$AY$67,AMC!$R$2:$R$1796,"="&$A8 5,AMC!$E$2:$E$1796,"="&CT$67)

COUNTIFS with an OR?
 

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


"RobofMN" wrote in message
...
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)+COUNTIF S(AMC!$C$2:$C$1796,$AY$67,AMC!$R$2:$R$1796,"="&$A8 5,AMC!$E$2:$E$1796,"="&CT$67)

COUNTIFS with an OR?
 

Thank you :)

"T. Valko" wrote:

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


"RobofMN" wrote in message
...
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)+COUNTIF S(AMC!$C$2:$C$1796,$AY$67,AMC!$R$2:$R$1796,"="&$A8 5,AMC!$E$2:$E$1796,"="&CT$67)

COUNTIFS with an OR?
 

You're welcome!

--
Biff
Microsoft Excel MVP


"RobofMN" wrote in message
...
Thank you :)

"T. Valko" wrote:

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


"RobofMN" wrote in message
...
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)+COUNTIF S(AMC!$C$2:$C$1796,$AY$67,AMC!$R$2:$R$1796,"="&$A8 5,AMC!$E$2:$E$1796,"="&CT$67)