Counting by a Range of Values?

E

Ender

I was wondering if anyone knew how I could sort numbers and count how
many fall into a certain range?

I have a bunch of cheque values, and want to be able to count how many
are 0-100, 101-200, 201-300 etc...

Ultimately, I want to be able to create a graph showing what portion of
all the values fall into each range.

Does anyone know how I should go about this?

Also would be interested in how to do a sum of all the values in The
range and do a similar graph.
 
C

carimfam

Hi Ender,

With formulas, just go as follows :
=COUNTIF(A1:A20,"<=200")-COUNTIF(A1:A20,">101")

HTH
Carim
 
E

Ender

Is there any easy way to make this forumula Repeat?

I want to do 0-100, 101-200 etc..
But if I try to just drag and copy the forumla It insists on going
A1:A50 and then A2:A51, A3:52 etc...
 
C

Carim

You are complicating your life ...
Use a1:a500, if row 500 is your last row ...
In addition, I would extract your limits into specific cells and use
cell addresses in th countif formula ...

HTH
Carim
 
E

Ender

I know I want to be using A1:A500, but I'm trying to calculate for like
20 different ranges as well, it keeps changing the A1 value to A2, A3,
A4 etc...
I don't know how to stop that without re-writing the whole thing
 
C

Carim

could absolute values help ...

=COUNTIF($A$1:$A$500,"<=200")-COUNTIF($A$1:$A$500,">101")

Cheers
 
B

Bob Phillips

I think the formula should be

=COUNTIF($A$1:$A$500,"<=200")-COUNTIF($A$1:$A$500,"<101")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

MyVeryOwnSelf

Is there any easy way to make this forumula Repeat?
I want to do 0-100, 101-200 etc..
But if I try to just drag and copy the forumla It insists on going
A1:A50 and then A2:A51, A3:52 etc...
Click to expand...


Maybe something like the following csv file would meet your need. Fill in
the formula in B2 and copy it down.

_________________________________________________
64,"=COUNTIF(A:A,""<101"")"
876,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
345,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
975,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
366,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
436,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
248,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
235,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
532,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
4546,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
222,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
5653,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
444,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
222,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
5544,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
222,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
554,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
975,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
433,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
643,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
532,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
33432,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
222,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
335,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
3221,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
453,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
222,"=COUNTIF(A:A,""<=""&ROW()*100)-COUNTIF(A:A,""<""&(ROW()-1)*100+1)"
322,
542,
712,
7532,
673,
742
532
2342
 
Top