COUNTIF criteria

[roohbir]

I have the following data:
70
80
71
71
66
83
70
77
79
65
62
57
50

I want to use the COUNTIF function to calculate the no. of times
numbers occur in a range. For example how many numbers are between
41-50. The criteria could be something like >=41 "to" <=50. But how do
i properly format it.
If COUNTIF cannot be used for this, other suggestions would be welcome.
Actually I want to make a pie-chart or bar-graph chowing this info.
Like how many occurences are within 41-50, 51-60 and so on.
Thanks in adavnce.

Roohbir

 

[Biff]

Try this:

Numbers in the range A2:A14.

=COUNTIF(A2:A14,">=41")-COUNTIF(A2:A14,">50")

Better to use cells to hold the variables:

C2 = 41
D2 = 50

=COUNTIF(A2:A14,">="&C2)-COUNTIF(A2:A14,">"&D2)

Biff

 

[roohbir]

Thanks for the reply Biff.
I used using this formula:
=COUNTIF(A1:A13,">=41")-COUNTIF(A1:A13,"<=50")
It gives me an error which says not to use "-" in the formula. Any
ideas?
Roohbir

 

[roohbir]

And when I used this =COUNTIF(A1:A13,">="&B1)-COUNTIF(A1:A13,"<="&C1),
it gave 12, where B1 was 41 and C1 was 50. 12 is wrong.
Can you see any reason?
Thanks
Roohbir

 

[Ron Rosenfeld]

And when I used this =COUNTIF(A1:A13,">="&B1)-COUNTIF(A1:A13,"<="&C1),
it gave 12, where B1 was 41 and C1 was 50. 12 is wrong.
Can you see any reason?
Thanks
Roohbir

Your second function is incorrect. Your equality operator is wrong.

Biff wrote:

=... -COUNTIF(A2:A14,">"&D2)
^^^
You used
=... -COUNTIF(A1:A13,"<="&C1)
^^^


--ron

 

[roohbir]

Well, that is because my data starts from A1 and ends at A13. So, I
changed the values accordingly, although the logic is the same.
Roohbir

 

[David Biddulph]

Well, that is because my data starts from A1 and ends at A13. So, I
changed the values accordingly, although the logic is the same.
Roohbir

Ron wasn't talking about the A1:A13, he was talking about the <= where it
should have been >

[You can see what he means if you view the message in a fixed-width font.]

 

[Ron Rosenfeld]

Well, that is because my data starts from A1 and ends at A13. So, I
changed the values accordingly, although the logic is the same.
Roohbir

Please reread what I wrote.

Your formula is WRONG

The logic is NOT the same. The cell references are irrelevant.

">" is NOT THE SAME AS "<="

Can you not see that

"GREATER THAN"

is different than

"LESS THAN OR EQUAL TO"

??


--ron

 

[Biff]

It may seem to be counterintutitve to use ">" in the second Countif formula
but if you tried it you would see that it produces the correct result.

If you prefer, you can use this formula:

=SUMPRODUCT(--(A1:A13>=B1),--(A1:A13<=C1))

The Countif formula is faster.

Biff

 

[Biff]

Inside joke:

I hope you're not gonna go "onlyinsyder" on me!

<VVBG>

Biff