Look up value range in column and then count

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

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"
 
Thanks Ron, I have been trying to do this for 2 days... got close but your
solution is brilliant.

Do you know MS Access? (e-mail address removed)
 
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.
 
Back
Top