COUNTIF with AND logic

[Mitchell_Collen via OfficeKB.com]

Please help me. I am trying to create a function that will count if cell
values are between 60 and 180.

The funtion below works but I can't get the AND logic in the function.

=COUNTIF(AE740:AE863,">180")

I have tried this but it does not work: =COUNTIF(AE740:AE863,">60 AND <180")
I have also tried this: =COUNTIF((AE740:AE863,">60) AND (AE740:AE863, "<180"))


Thanks, Misty

 

[Bob Phillips]

=COUNTIF(AE740:AE863,">60")-COUNTIF(AE740:AE863,">180")

you might want to change the second to >= if you want to exclude 180.



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mitchell_Collen via OfficeKB.com]

I will try it thanks and thanks for the tip!

=COUNTIF(AE740:AE863,">60")-COUNTIF(AE740:AE863,">180")

you might want to change the second to >= if you want to exclude 180.

Please help me. I am trying to create a function that will count if cell
values are between 60 and 180.

[quoted text clipped - 9 lines]

Thanks, Misty

 

[Mitchell_Collen via OfficeKB.com]

What doe the subtraction sign do in this function? I just notices that the >
are facing the same way and I was thinking that it had something to do with
it. I can't figure out if that is between 60 and 180 or both greater than 60
and 180.

=COUNTIF(AE740:AE863,">60")-COUNTIF(AE740:AE863,">180")

you might want to change the second to >= if you want to exclude 180.

Please help me. I am trying to create a function that will count if cell
values are between 60 and 180.

[quoted text clipped - 9 lines]

Thanks, Misty

 

[Peo Sjoblom]

The first formula will count all occurrences greater than 60 so if you want
to include 60 use >=60.

Anyway, let's say that it will return 10

now the second will count all occurrences greater than 180, assume that
there are 4, the difference will be 10-4 which is 6 which is in fact all the
occurrences between 60 and 180. My guess is that you want to include 60 and
should then use >=60, if not use >60 and if you don't want to include 180
change >180 to >=180 in the second formula

The reason is that you can't use COUNTIF in one fell swoop with AND

It can also be done by using

=SUMPRODUCT(--(Range>=60),--(Range<=180))

which is probably more in line with what you thought it would look like




--
Regards,

Peo Sjoblom

What doe the subtraction sign do in this function? I just notices that theare facing the same way and I was thinking that it had something to do
with
it. I can't figure out if that is between 60 and 180 or both greater than
60
and 180.

=COUNTIF(AE740:AE863,">60")-COUNTIF(AE740:AE863,">180")

you might want to change the second to >= if you want to exclude 180.

Please help me. I am trying to create a function that will count if cell
values are between 60 and 180.

[quoted text clipped - 9 lines]

Thanks, Misty

 

[Gord Dibben]

Bob's formula says count all cells greater than 60 then subtract all cells
greater than 180

This will return a count of cells from 61 to 180

You said "between 60 and 180" so that should be 60.0000000001 to
179.999999999999

If you want 60 to 180......................

Change the operators and numbers to suit your needs.

e.g. >=60 or >=180


Gord Dibben MS Excel MVP

What doe the subtraction sign do in this function? I just notices that the >
are facing the same way and I was thinking that it had something to do with
it. I can't figure out if that is between 60 and 180 or both greater than 60
and 180.

=COUNTIF(AE740:AE863,">60")-COUNTIF(AE740:AE863,">180")

you might want to change the second to >= if you want to exclude 180.

Please help me. I am trying to create a function that will count if cell
values are between 60 and 180.

[quoted text clipped - 9 lines]

Thanks, Misty

 

[Mitchell_Collen via OfficeKB.com]

Ok, thanks for the clarification! I kept second guessing the numbers. I
appreciate both of you.
-Misty

Bob's formula says count all cells greater than 60 then subtract all cells
greater than 180

This will return a count of cells from 61 to 180

You said "between 60 and 180" so that should be 60.0000000001 to
179.999999999999

If you want 60 to 180......................

Change the operators and numbers to suit your needs.

e.g. >=60 or >=180

Gord Dibben MS Excel MVP

What doe the subtraction sign do in this function? I just notices that the >
are facing the same way and I was thinking that it had something to do with

[quoted text clipped - 10 lines]

 

[Bob Phillips]

It is always debatable when an OP says between whether that should include
the specified numbers or exclude them <bg>.
..
The only guarantee is that whatever you pick, it will be wrong <ebg>.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob's formula says count all cells greater than 60 then subtract all cells
greater than 180

This will return a count of cells from 61 to 180

You said "between 60 and 180" so that should be 60.0000000001 to
179.999999999999

If you want 60 to 180......................

Change the operators and numbers to suit your needs.

e.g. >=60 or >=180


Gord Dibben MS Excel MVP

What doe the subtraction sign do in this function? I just notices that theare facing the same way and I was thinking that it had something to do
with
it. I can't figure out if that is between 60 and 180 or both greater than
60
and 180.

=COUNTIF(AE740:AE863,">60")-COUNTIF(AE740:AE863,">180")

you might want to change the second to >= if you want to exclude 180.

Please help me. I am trying to create a function that will count if
cell
values are between 60 and 180.
[quoted text clipped - 9 lines]

Thanks, Misty

 

[Rick Rothstein \(MVP - VB\)]

Bob's formula says count all cells greater than 60 then subtract all cells

greater than 180

This will return a count of cells from 61 to 180

You said "between 60 and 180" so that should be 60.0000000001 to
179.999999999999

If you want 60 to 180......................

Change the operators and numbers to suit your needs.

e.g. >=60 or >=180

I think you meant to type >180 (in order to include the 180 in the range).

Rick