COUNTIF using OR

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PCLIVE - 17 Aug 2006 18:20 GMT

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 to this Message

Bearacade - 17 Aug 2006 18:41 GMT

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

Signature

Bearacade

Reply to this Message

Bob Phillips - 17 Aug 2006 19:01 GMT

But you don't have to restrict yourself

=SUMPRODUCT(COUNTIF('Week 1'!N$3:N$10000,{"CR","ON","CC","OR"}))

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

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

Reply to this Message

Biff - 17 Aug 2006 18:50 GMT

Hi!

Try this:

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

Biff

> 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
[quoted text clipped - 8 lines]
>
> Any ideas?

Reply to this Message

Biff - 17 Aug 2006 18:57 GMT

Another way:

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

Biff

> Hi!
>
[quoted text clipped - 17 lines]
>>
>> Any ideas?

Reply to this Message

PCLIVE - 17 Aug 2006 20:00 GMT

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

> Another way:
>
[quoted text clipped - 23 lines]
>>>
>>> Any ideas?

Reply to this Message

Dave F - 17 Aug 2006 18:52 GMT

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.

> 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
[quoted text clipped - 8 lines]
>
> Any ideas?

Reply to this Message

Sloth - 17 Aug 2006 19:25 GMT

=SUMPRODUCT(--(A1:A10={"CR","ON","CC","OR"}))

should work. COUNTIF doesn't have a way to include 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
[quoted text clipped - 8 lines]
>
> Any ideas?

Reply to this Message