COUNTIF using OR

P

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

Biff

Hi!

Try this:

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

Biff
 
D

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

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

Sloth

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

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

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
 
Top