COUNTIF using OR

[PCLIVE]

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?

[Bearacade]

You pretty much have to do what you are doing. That's how countif works..

[Dave F]

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?
>
>
>

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH('Week 1'!N3:N10000,{"cr","on","cc","or"},0))))

Biff

"PCLIVE" <[email protected]> wrote in message
news:%[email protected]...
> 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?
>

[Biff]

Another way:

=SUM(COUNTIF('Week 1'!N3:N10000,{"cr","on","cc","or"}))

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Try this:
>
> =SUMPRODUCT(--(ISNUMBER(MATCH('Week
> 1'!N3:N10000,{"cr","on","cc","or"},0))))
>
> Biff
>
> "PCLIVE" <[email protected]> wrote in message
> news:%[email protected]...
>> 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?
>>
>
>

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Sloth]

=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?
>
>
>

[PCLIVE]

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" <[email protected]> wrote in message
news:[email protected]...
> Another way:
>
> =SUM(COUNTIF('Week 1'!N3:N10000,{"cr","on","cc","or"}))
>
> Biff
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hi!
>>
>> Try this:
>>
>> =SUMPRODUCT(--(ISNUMBER(MATCH('Week
>> 1'!N3:N10000,{"cr","on","cc","or"},0))))
>>
>> Biff
>>
>> "PCLIVE" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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?
>>>
>>
>>
>
>