Countif with two criteria?

[noobie6675]

Hi everyone

What im trying to do is get some records that are Larger than (>)
number and Smaller than (<) another number.

I have tried using the COUNTIF statement but it only seems to let m
use one criteria?

format is:
=COUNTIF(range,criteria)

Anyone know if i can somehow use 2 criterias in this?
or if not is there some other way i could do this?

cheer

 

[Ragdyer]

One way is to combine the Countifs:

=COUNTIF(A1:A11,"<50")+COUNTIF(A1:A11,">250")
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Kevin Stecyk]

Ragdyer,

I am unsure of noobie6675's question. By larger and smaller, does the OP
mean "<50" or ">250" as your example shows. Or does the OP mean >50 and
<250?

If it is the latter, simply modifying Ragdyer's equation becomes.....

=COUNTIF(A1:A11,">50")-COUNTIF(A1:A11,"<250")

So the first countif captures all values above 50 and the second countif
captures all values above 250. Thus, you are left with a range 50 < X <
250.

Noobie6675, I think either Ragdyer or my solution should be appropriate.
Good luck.

Regards,
Kevin

One way is to combine the Countifs:

=COUNTIF(A1:A11,"<50")+COUNTIF(A1:A11,">250")
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Arvi Laanemets]

Hi

=SUMPRODUCT((range>criteria)*(range<criteria))

 

[Dan E]

Kevin,

Your solution provides the range

50 < X <= 250

=COUNTIF(A1:A11,">50")-COUNTIF(A1:A11,"<=250")

gives 50 < X < 250

Dan E

Ragdyer,

I am unsure of noobie6675's question. By larger and smaller, does the OP
mean "<50" or ">250" as your example shows. Or does the OP mean >50 and
<250?

If it is the latter, simply modifying Ragdyer's equation becomes.....

=COUNTIF(A1:A11,">50")-COUNTIF(A1:A11,"<250")

So the first countif captures all values above 50 and the second countif
captures all values above 250. Thus, you are left with a range 50 < X <
250.

Noobie6675, I think either Ragdyer or my solution should be appropriate.
Good luck.

Regards,
Kevin

 

[J.E. McGimpsey]

Actually, neither of them give 50 < X <(=) 250.

Kevin's subtracts numbers <250 from numbers >50, so if A1:A11
contained all 10's, say, the result would be -11, which doesn't seem
to meet the OP's criteria. Similarly, if all 100's, the result would
be 0, and all 250's would also result in 0.

Dan E's solution for the same numbers would be the same except for
all 250's which would return 11.

To produce a count for the range 50 < X < 250, use

=COUNTIF(A1:A11,">50")+COUNTIF(A1:A11,">=250")

or

=COUNTIF(A1:A11,"<250")-COUNTIF(A1:A11,"<=50")

or

=SUMPRODUCT(--(A1:A11>50),--(A1:A11<250))

To produce a count for the range 50 <= X <= 250, use

=COUNTIF(A1:A11,">=50")+COUNTIF(A1:A11,">250")

or

=COUNTIF(A1:A11,"<=250")-COUNTIF(A1:A11,"<50")

or

=SUMPRODUCT(--(A1:A11>=50),--(A1:A11<=250))

 

[Dan E]

We're all off today, your first formula should be

=COUNTIF(A1:A11,">50")-COUNTIF(A1:A11,">=250")

I would think...

Dan E

 

[J.E. McGimpsey]

Darned editing in the message:

Change the "+" to "-" in the COUNTIF() formulas.

 

[J.E. McGimpsey]

Yep - see my correction. the fourth formulas got the same problem
and same fix.

 

[Kevin Stecyk]

J.E. McGimpsey,

Thank you for correcting my obvious blunder. I was thinking one thing and
wrote another. Yes, of course, you want to subtract those values ABOVE 250.

Again, thanks!

Regards,
Kevin

 

[Kevin Stecyk]

Dan,

Thank you for the corrections as well.

Whew!

Regards,
Kevin

 

[RagDyer]

Hey Fellas,

If you'll notice, I just made up those numbers as an example for the OP, and
I purposely over simplified the arguments to escape all the possibilities
that are being displayed here.

This entire thread appears to be an exercise in futility without any
additional input from the OP, but I guess it's good practice (both typing &
mental acuity) for all.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Dan,

Thank you for the corrections as well.

Whew!

Regards,
Kevin

 

[Jeremy]

I just had to register just to thank you guys.

This thread showed up when I did a google search and your answers were
exactly what I was looking for.

 

[Guest]

I learned a lot from this tread. thanks guys!

 

[Guest]

Thanks guys. you solved my problem.