Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF using OR
I'm trying to figure a way to simplify this COUNTIF formula by using the OR
Function. It works the way it is, but it seems like I should be able to make it more simple. =COUNTIF('Week 1'!N$3:N$10000,"CR")+COUNTIF('Week 1'!N$3:N$10000,"ON")+COUNTIF('Week 1'!N$3:N$10000,"CC")+COUNTIF('Week 1'!N$3:N$10000,"OR") I've tried this with no success. =COUNTIF('Week 1'!N$3:N$10000,OR("CR","ON","CC","OR")) Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF using OR
You pretty much have to do what you are doing. That's how countif works.. -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572763 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF using OR
Hi!
Try this: =SUMPRODUCT(--(ISNUMBER(MATCH('Week 1'!N3:N10000,{"cr","on","cc","or"},0)))) Biff "PCLIVE" wrote in message ... I'm trying to figure a way to simplify this COUNTIF formula by using the OR Function. It works the way it is, but it seems like I should be able to make it more simple. =COUNTIF('Week 1'!N$3:N$10000,"CR")+COUNTIF('Week 1'!N$3:N$10000,"ON")+COUNTIF('Week 1'!N$3:N$10000,"CC")+COUNTIF('Week 1'!N$3:N$10000,"OR") I've tried this with no success. =COUNTIF('Week 1'!N$3:N$10000,OR("CR","ON","CC","OR")) Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF using OR
Put OR to the left of the first COUNTIF and separate each COUNTIF with a
comma and enclose the whole thing with parentheses: =OR(COUNTIF('Week 1'....)) Separate COUNTIFs with commas. That doesn't really simplify the formula, though, just gives it different syntax. "PCLIVE" wrote: I'm trying to figure a way to simplify this COUNTIF formula by using the OR Function. It works the way it is, but it seems like I should be able to make it more simple. =COUNTIF('Week 1'!N$3:N$10000,"CR")+COUNTIF('Week 1'!N$3:N$10000,"ON")+COUNTIF('Week 1'!N$3:N$10000,"CC")+COUNTIF('Week 1'!N$3:N$10000,"OR") I've tried this with no success. =COUNTIF('Week 1'!N$3:N$10000,OR("CR","ON","CC","OR")) Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF using OR
Another way:
=SUM(COUNTIF('Week 1'!N3:N10000,{"cr","on","cc","or"})) Biff "Biff" wrote in message ... Hi! Try this: =SUMPRODUCT(--(ISNUMBER(MATCH('Week 1'!N3:N10000,{"cr","on","cc","or"},0)))) Biff "PCLIVE" wrote in message ... I'm trying to figure a way to simplify this COUNTIF formula by using the OR Function. It works the way it is, but it seems like I should be able to make it more simple. =COUNTIF('Week 1'!N$3:N$10000,"CR")+COUNTIF('Week 1'!N$3:N$10000,"ON")+COUNTIF('Week 1'!N$3:N$10000,"CC")+COUNTIF('Week 1'!N$3:N$10000,"OR") I've tried this with no success. =COUNTIF('Week 1'!N$3:N$10000,OR("CR","ON","CC","OR")) Any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF using OR
But you don't have to restrict yourself
=SUMPRODUCT(COUNTIF('Week 1'!N$3:N$10000,{"CR","ON","CC","OR"})) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bearacade" wrote in message ... You pretty much have to do what you are doing. That's how countif works.. -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572763 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF using OR
=SUMPRODUCT(--(A1:A10={"CR","ON","CC","OR"}))
should work. COUNTIF doesn't have a way to include OR. "PCLIVE" wrote: I'm trying to figure a way to simplify this COUNTIF formula by using the OR Function. It works the way it is, but it seems like I should be able to make it more simple. =COUNTIF('Week 1'!N$3:N$10000,"CR")+COUNTIF('Week 1'!N$3:N$10000,"ON")+COUNTIF('Week 1'!N$3:N$10000,"CC")+COUNTIF('Week 1'!N$3:N$10000,"OR") I've tried this with no success. =COUNTIF('Week 1'!N$3:N$10000,OR("CR","ON","CC","OR")) Any ideas? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF using OR
These have all been good suggestions, some of which worked and others did
not appear to. However, I have gone with yours, Biff, as it appears to be the simplist one. =SUM(COUNTIF('Week 1'!N3:N10000,{"cr","on","cc","or"})) Thanks to all. Paul "Biff" wrote in message ... Another way: =SUM(COUNTIF('Week 1'!N3:N10000,{"cr","on","cc","or"})) Biff "Biff" wrote in message ... Hi! Try this: =SUMPRODUCT(--(ISNUMBER(MATCH('Week 1'!N3:N10000,{"cr","on","cc","or"},0)))) Biff "PCLIVE" wrote in message ... I'm trying to figure a way to simplify this COUNTIF formula by using the OR Function. It works the way it is, but it seems like I should be able to make it more simple. =COUNTIF('Week 1'!N$3:N$10000,"CR")+COUNTIF('Week 1'!N$3:N$10000,"ON")+COUNTIF('Week 1'!N$3:N$10000,"CC")+COUNTIF('Week 1'!N$3:N$10000,"OR") I've tried this with no success. =COUNTIF('Week 1'!N$3:N$10000,OR("CR","ON","CC","OR")) Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct vs. countif | Excel Discussion (Misc queries) | |||
Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions |