If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Look up value range in column and then count
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 |
#2
|
|||
|
|||
Look up value range in column and then count
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 |
#4
|
|||
|
|||
Look up value range in column and then count
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 |
Thread Tools | |
Display Modes | |
|
|