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

 

[Biff]

Hi!

Try this:

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

Biff

 

[Guest]

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.

 

[Biff]

Another way:

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

Biff

 

[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)

 

[Guest]

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

should work. COUNTIF doesn't have a way to include OR.

 

[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