I have a column of numbers ranging from 0 to -100
I need to count the number of cells with in the ranges
0 to -6
-7 to -14
-15 to -28
-85 to -100

Can you tell me the best way to get these figures, I would imagine I have to
put this formula into 4 different cells.
Thanks AJ

Using Excel 2007
Supposing your values are in column A rows 1 to 17 try this:
Four seperate cells:
=COUNTIFS($A$1:$A$17,"<=0",$A$1:$A$17,"=-6")
=COUNTIFS($A$1:$A$17,"<=-7",$A$1:$A$17,"=-14")
=COUNTIFS($A$1:$A$17,"<=-15",$A$1:$A$17,"=-28")
=COUNTIFS($A$1:$A$17,"<=-85",$A$1:$A$17,"=-100")
and one cell:
=COUNTIFS($A$1:$A$17,"<=0",$A$1:$A$17,"=-6")&" between 0 and -6,
"&COUNTIFS($A$1:$A$17,"<=7",$A$1:$A$17,"=-14")&" between -7 and -14,
"&COUNTIFS($A$1:$A$17,"<=-15",$A$1:$A$17,"=-28")&" between -15 and -28 and
"&COUNTIFS($A$1:$A$17,"<=-85",$A$1:$A$17,"=-100")&" between -85 and -100"


"AJ" wrote:

I have a column of numbers ranging from 0 to -100
I need to count the number of cells with in the ranges
0 to -6
-7 to -14
-15 to -28
-85 to -100

Can you tell me the best way to get these figures, I would imagine I have to
put this formula into 4 different cells.
Thanks AJ

Thanks Ron, I have been trying to do this for 2 days... got close but your
solution is brilliant.

Do you know MS Access?

"Ron@Buy" wrote:

Using Excel 2007
Supposing your values are in column A rows 1 to 17 try this:
Four seperate cells:
=COUNTIFS($A$1:$A$17,"<=0",$A$1:$A$17,"=-6")
=COUNTIFS($A$1:$A$17,"<=-7",$A$1:$A$17,"=-14")
=COUNTIFS($A$1:$A$17,"<=-15",$A$1:$A$17,"=-28")
=COUNTIFS($A$1:$A$17,"<=-85",$A$1:$A$17,"=-100")
and one cell:
=COUNTIFS($A$1:$A$17,"<=0",$A$1:$A$17,"=-6")&" between 0 and -6,
"&COUNTIFS($A$1:$A$17,"<=7",$A$1:$A$17,"=-14")&" between -7 and -14,
"&COUNTIFS($A$1:$A$17,"<=-15",$A$1:$A$17,"=-28")&" between -15 and -28 and
"&COUNTIFS($A$1:$A$17,"<=-85",$A$1:$A$17,"=-100")&" between -85 and -100"


"AJ" wrote:

I have a column of numbers ranging from 0 to -100
I need to count the number of cells with in the ranges
0 to -6
-7 to -14
-15 to -28
-85 to -100

Can you tell me the best way to get these figures, I would imagine I have to
put this formula into 4 different cells.
Thanks AJ

Can I add another two selections to this?
I have 4 locations and 3 priority categories
How would I select 1 location and then one category with the 4 columns
I have a filter on but it is not changing the numbers in the countif you sent.

"AJ" wrote:

Thanks Ron, I have been trying to do this for 2 days... got close but your
solution is brilliant.

Do you know MS Access?

"Ron@Buy" wrote:

Using Excel 2007
Supposing your values are in column A rows 1 to 17 try this:
Four seperate cells:
=COUNTIFS($A$1:$A$17,"<=0",$A$1:$A$17,"=-6")
=COUNTIFS($A$1:$A$17,"<=-7",$A$1:$A$17,"=-14")
=COUNTIFS($A$1:$A$17,"<=-15",$A$1:$A$17,"=-28")
=COUNTIFS($A$1:$A$17,"<=-85",$A$1:$A$17,"=-100")
and one cell:
=COUNTIFS($A$1:$A$17,"<=0",$A$1:$A$17,"=-6")&" between 0 and -6,
"&COUNTIFS($A$1:$A$17,"<=7",$A$1:$A$17,"=-14")&" between -7 and -14,
"&COUNTIFS($A$1:$A$17,"<=-15",$A$1:$A$17,"=-28")&" between -15 and -28 and
"&COUNTIFS($A$1:$A$17,"<=-85",$A$1:$A$17,"=-100")&" between -85 and -100"


"AJ" wrote:

I have a column of numbers ranging from 0 to -100
I need to count the number of cells with in the ranges
0 to -6
-7 to -14
-15 to -28
-85 to -100

Can you tell me the best way to get these figures, I would imagine I have to
put this formula into 4 different cells.
Thanks AJ