COUNTIF with 2 variables. HOW???

[Patrick]

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!

 

[T. Valko]

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.

 

[Patrick]

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:S3>0,Attendance!D3:S3<2.5)

 

[Patrick]

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]

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:Z1>0),--(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!