Countif Question

  • Thread starter Thread starter Firecraker
  • Start date Start date
F

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
 
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
 
=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))
 
Laura,

Maybe you really meant:

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


Regards,

RD
 
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

RagDyer said:
Laura,

Maybe you really meant:

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


Regards,

RD
Click to expand...
 
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

RagDyer said:
Laura,

Maybe you really meant:

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


Regards,

RD
Click to expand...
 
No problem!

--
Laura Cook
Appleton, WI


RagDyer said:
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)
Click to expand...
 
Back
Top