A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Look up value range in column and then count



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2010, 04:44 AM posted to microsoft.public.excel.misc
aj
external usenet poster
 
Posts: 333
Default 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  
Old April 20th, 2010, 07:13 AM posted to microsoft.public.excel.misc
Ron@Buy
external usenet poster
 
Posts: 348
Default 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  
Old April 20th, 2010, 11:50 PM posted to microsoft.public.excel.misc
aj
external usenet poster
 
Posts: 333
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.