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?

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

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?

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?

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?

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

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?

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?