Countif Question

[Firecraker]

Is there an easy way to combine two countif statements? I need to find
the amount of values in a certain rage. I currently am using two
countif statements

In A1
=COUNTIF(AE11:AE21,"<=10")
In A2
=COUNTIF(AE11:AE21,">=15")

I then have a formula in A3 that adds the values together and then
subtracts the values from the total number of cells to get the amount
of cells between 10 and 15.

This is working so far, but I'm wondering if there is an actual formula
for this. I know this isn't the correct syntax but I need the formula
to go something like Countif AE11:AE21 is between or equal to 10 and
15.

Any suggetions would be greatly appreciated!! Thanks in advance!!

Roy

 

[RagDyer]

One way is:

=COUNTIF(AE11:AE21,"<=15")-COUNTIF(AE11:AE21,"<10")
--

HTH,

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


Is there an easy way to combine two countif statements? I need to find
the amount of values in a certain rage. I currently am using two
countif statements

In A1
=COUNTIF(AE11:AE21,"<=10")
In A2
=COUNTIF(AE11:AE21,">=15")

I then have a formula in A3 that adds the values together and then
subtracts the values from the total number of cells to get the amount
of cells between 10 and 15.

This is working so far, but I'm wondering if there is an actual formula
for this. I know this isn't the correct syntax but I need the formula
to go something like Countif AE11:AE21 is between or equal to 10 and
15.

Any suggetions would be greatly appreciated!! Thanks in advance!!

Roy

 

[Laura Cook]

Try the following:

=SUMPRODUCT((AE11:AE21>10)*(AE11:AE21<15))

 

[Peo Sjoblom]

=COUNTIF(AE11:AE21,">=10")-COUNTIF(AE11:AE21,">15")

or

=COUNTIF(AE11:AE21,">10")-COUNTIF(AE11:AE21,">=15")

depending on if you want to included 10 and 15

another way

=SUMPRODUCT(--(AE11:AE21>=10),--(AE11:AE21<=15))

or

=SUMPRODUCT(--(AE11:AE21>10),--(AE11:AE21<15))

 

[RagDyer]

Laura,

Maybe you really meant:

=SUMPRODUCT((AE11:AE21<=15)-(AE11:AE21<10))
--


Regards,

RD

 

[Firecraker]

Wow...thank you to everyone who posted!!! That was fast!!

Roy

 

[Peo Sjoblom]

Hi RD,

I believe the OP wanted to count the values between 10 and 15 and all
suggestions will do that
one way or another (depending if 10 and 15 should be included they differ a
bit)

--

Regards,

Peo Sjoblom

Laura,

Maybe you really meant:

=SUMPRODUCT((AE11:AE21<=15)-(AE11:AE21<10))
--


Regards,

RD

 

[RagDyer]

You're right Peo.
Yesterday was a long, hard day for me.<g>

Sorry Laura !
--

Regards,

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

Hi RD,

I believe the OP wanted to count the values between 10 and 15 and all
suggestions will do that
one way or another (depending if 10 and 15 should be included they differ a
bit)

--

Regards,

Peo Sjoblom

Laura,

Maybe you really meant:

=SUMPRODUCT((AE11:AE21<=15)-(AE11:AE21<10))
--


Regards,

RD

 

[Laura Cook]

No problem!

--
Laura Cook
Appleton, WI

You're right Peo.
Yesterday was a long, hard day for me.<g>

Sorry Laura !
--

Regards,

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

Hi RD,

I believe the OP wanted to count the values between 10 and 15 and all
suggestions will do that
one way or another (depending if 10 and 15 should be included they differ a
bit)