COUNTIF with AND logic
 

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

--
Message posted via http://www.officekb.com

COUNTIF with AND logic
 

=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" <u33726@uwe wrote in message
news:73effad7f72b7@uwe...
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

--
Message posted via http://www.officekb.com

COUNTIF with AND logic
 

I will try it thanks and thanks for the tip!


Bob Phillips wrote:
=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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200706/1

COUNTIF with AND logic
 

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.

Bob Phillips wrote:
=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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200706/1

COUNTIF with AND logic
 

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



"Mitchell_Collen via OfficeKB.com" <u33726@uwe wrote in message
news:73f14f8d3af7f@uwe...
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.

Bob Phillips wrote:
=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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200706/1

COUNTIF with AND logic
 

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

On Tue, 19 Jun 2007 17:53:35 GMT, "Mitchell_Collen via OfficeKB.com"
<u33726@uwe wrote:

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.

Bob Phillips wrote:
=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

COUNTIF with AND logic
 

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

Gord Dibben wrote:
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]

Thanks, Misty

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200706/1

COUNTIF with AND logic
 

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)

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Tue, 19 Jun 2007 17:53:35 GMT, "Mitchell_Collen via OfficeKB.com"
<u33726@uwe wrote:

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.

Bob Phillips wrote:
=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

COUNTIF with AND logic
 

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