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

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" wrote in message
ups.com...
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

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

Biff wrote:
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" wrote in message
ups.com...
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

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

Biff wrote:
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" wrote in message
ups.com...
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

On 23 Sep 2006 19:28:01 -0700, "roohbir" wrote:

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

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 Rosenfeld wrote:
On 23 Sep 2006 19:28:01 -0700, "roohbir" wrote:

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" wrote in message
ups.com...
Ron Rosenfeld wrote:
On 23 Sep 2006 19:28:01 -0700, "roohbir" wrote:

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)
^^^

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.]
--
David Biddulph

On 23 Sep 2006 23:45:45 -0700, "roohbir" wrote:

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

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

"roohbir" wrote in message
ups.com...
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 Rosenfeld wrote:
On 23 Sep 2006 19:28:01 -0700, "roohbir" wrote:

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

Inside joke:

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

<VVBG

Biff

"Biff" wrote in message
...
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

"roohbir" wrote in message
ups.com...
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 Rosenfeld wrote:
On 23 Sep 2006 19:28:01 -0700, "roohbir" wrote:

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