Here is what I am trying to do. In a cell I want it to look at a range of
other cells with the "COUNTIF" formula. I want to be able to say to a
particular cell, for example:

Look at the range of cells A1:Z1. Count them if the value is 0 and <2.5.

For some reason I just can't seem to get it to work or to get Excel to tell
me what I'm doing wrong!!! HELP!

Look at the range of cells A1:Z1. Count them if the value is 0 and <2.5.

Based on the *literal* interpretation:

=COUNTIF(A1:Z1,"0")-COUNTIF(A1:AZ1,"2.49999999999")

This will count cells greater than 0 and less than 2.5. This *excludes* 0
and 2.5. If you meant the count to be *inclusive*:

=COUNTIF(A1:Z1,"=0")-COUNTIF(A1:AZ1,"2.5")

This *includes* 0 and 2.5.

--
Biff
Microsoft Excel MVP


"Patrick" wrote in message
...
Here is what I am trying to do. In a cell I want it to look at a range of
other cells with the "COUNTIF" formula. I want to be able to say to a
particular cell, for example:

Look at the range of cells A1:Z1. Count them if the value is 0 and <2.5.

For some reason I just can't seem to get it to work or to get Excel to
tell
me what I'm doing wrong!!! HELP!

BTW, here is the formula I have tried using and Excel keeps telling me I am
missing a parenthesis. Just won't tell me WHERE it is missing!

=COUNTIF(AND(Attendance!D3:S30,Attendance!D3:S3<2 .5)

"Patrick" wrote:

Here is what I am trying to do. In a cell I want it to look at a range of
other cells with the "COUNTIF" formula. I want to be able to say to a
particular cell, for example:

Look at the range of cells A1:Z1. Count them if the value is 0 and <2.5.

For some reason I just can't seem to get it to work or to get Excel to tell
me what I'm doing wrong!!! HELP!

Thanks, and please pardon my confusion (ignorance). When I look at your
first suggestion it looks like you are saying to count anything greater than
0, which I do want it to do, but I don't want it to count anything GREATER
than 2.49999999 and that is how I am reading your formula. I tried typing
your formula in but changed the to <. Still no luck. Can you see any
error in the formula I attached before your post?

"T. Valko" wrote:

Look at the range of cells A1:Z1. Count them if the value is 0 and <2.5.

Based on the *literal* interpretation:

=COUNTIF(A1:Z1,"0")-COUNTIF(A1:AZ1,"2.49999999999")

This will count cells greater than 0 and less than 2.5. This *excludes* 0
and 2.5. If you meant the count to be *inclusive*:

=COUNTIF(A1:Z1,"=0")-COUNTIF(A1:AZ1,"2.5")

This *includes* 0 and 2.5.

--
Biff
Microsoft Excel MVP


"Patrick" wrote in message
...
Here is what I am trying to do. In a cell I want it to look at a range of
other cells with the "COUNTIF" formula. I want to be able to say to a
particular cell, for example:

Look at the range of cells A1:Z1. Count them if the value is 0 and <2.5.

For some reason I just can't seem to get it to work or to get Excel to
tell
me what I'm doing wrong!!! HELP!

Trust me!

The logic of the formula I suggested does exactly what you asked for. I
understand that just looking at it, it does not appear to be intuitive but
it does what you want. Here's a different version that might be more
visually appealing:

=SUMPRODUCT(--(A1:Z10),--(A1:Z1<2.5))

This returns the same result as the COUNTIF version. The COUNTIF version is
more efficient than the SUMPRODUCT version.

If I would have orginally posted the SUMPRODUCT version someone else would
have posted the COUNTIF version!

--
Biff
Microsoft Excel MVP


"Patrick" wrote in message
...
Thanks, and please pardon my confusion (ignorance). When I look at your
first suggestion it looks like you are saying to count anything greater
than
0, which I do want it to do, but I don't want it to count anything GREATER
than 2.49999999 and that is how I am reading your formula. I tried typing
your formula in but changed the to <. Still no luck. Can you see any
error in the formula I attached before your post?

"T. Valko" wrote:

Look at the range of cells A1:Z1. Count them if the value is 0 and
<2.5.

Based on the *literal* interpretation:

=COUNTIF(A1:Z1,"0")-COUNTIF(A1:AZ1,"2.49999999999")

This will count cells greater than 0 and less than 2.5. This *excludes* 0
and 2.5. If you meant the count to be *inclusive*:

=COUNTIF(A1:Z1,"=0")-COUNTIF(A1:AZ1,"2.5")

This *includes* 0 and 2.5.

--
Biff
Microsoft Excel MVP


"Patrick" wrote in message
...
Here is what I am trying to do. In a cell I want it to look at a range
of
other cells with the "COUNTIF" formula. I want to be able to say to a
particular cell, for example:

Look at the range of cells A1:Z1. Count them if the value is 0 and
<2.5.

For some reason I just can't seem to get it to work or to get Excel to
tell
me what I'm doing wrong!!! HELP!