Count the criteria which have the range value

[Mohsin]

Hi there

I would like to count a list value which have a range value for example 60-80,
The idea is > = countif (B1:B15,"80-60").. So how i do the right command,

try to used this;

= SUM(INDIRECT("B"&MATCH(0.3,B$1:B$15,0)&":B"&MATCH (0.4,B$1:B$15,0)))

but returning with #NA.

Please correct me if I'm wrong or better idea

br, mohsin

[Maistrye]

Try:

=COUNTIF(B1:B15,">=60")-COUNTIF(B1:B15,">80")

Scott

[Mohsin]

hI SCOTT

tX, IS WORK JUST CHANGE THE <80 INSTEAD OF >80

=COUNTIF(B175:B182,">=60")-COUNTIF(B178:B182,"< =80")

THANK YOU VERY MUCH INDEED....

[Maistrye]

Actually, I think if you look at what I meant, the original expression is correct.

To explain:

COUNTIF(B175:B182,">=60") - .... -> This counts all the values that are greater than 60.

This also includes all the values that are greater than 80. These are the values you don't want.

.... - COUNTIF(B175:B182,">80") -> This is the count of all items greater than 80. The other part of the expression includes these values, which you did not want.

So, by taking all the values greater than 60, and subtracting those of them that are greater than 80, you get all the values between 60 and 80.

Consider an example with these numbers:

B175:B182 = 50 65 70 75 80 100 110 120 <- Editted to add 80, which somehow got deleted by me.... otherwise the example doesn't make sense. :-P

=COUNTIF(B175:B182, ">=60") = 7
=COUNTIF(B175:B182, ">80") = 3

=COUNTIF(B175:B182, ">=60") - COUNTIF(B175:B182, ">80")
= 7 - 3
= 4

Using COUNTIF(B175:B182, "<=80"), you'd get:
=COUNTIF(B175:B182, ">=60") = 7
=COUNTIF(B175:B182, "<=80") = 5

=COUNTIF(B175:B182, ">=60") - COUNTIF(B175:B182, "<=80")
= 7 - 5
= 2

Scott

[Carim]

Scott,

You are absolutely right ...

I was about to drop a note ..
BUT you have written an excellent explanation ... which is apparently very much needed ... this misinterpretation is indeed very common ...

Cheers
Carim