| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
| COUNTIFS | Excel Worksheet Functions | |||
| Two COUNTIFs | Excel Discussion (Misc queries) | |||
| countifs | Excel Discussion (Misc queries) | |||
| 2 COUNTIFS | Excel Discussion (Misc queries) | |||
Hybrid Mode
