| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells with values x and <y
Is there a way to count cells with values say, greater than 0.5 and less than 1.0? I tried using COUNTIF function but it only allows one set of criteria. -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=558194 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count cells with values x and <y
=COUNTIF(A:A,".5")-COUNTIF(A:A,"=1")
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jimbob" wrote in message ... Is there a way to count cells with values say, greater than 0.5 and less than 1.0? I tried using COUNTIF function but it only allows one set of criteria. -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=558194 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count cells with values x and <y
Have a look at the DCount function. This lets you set the criteria for a
particular field in your database and count records matching that criteria HTH Sheila www.c-i-m-s.com "jimbob" wrote: Is there a way to count cells with values say, greater than 0.5 and less than 1.0? I tried using COUNTIF function but it only allows one set of criteria. -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=558194 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count cells with values x and <y
"jimbob" wrote in
message ... Is there a way to count cells with values say, greater than 0.5 and less than 1.0? I tried using COUNTIF function but it only allows one set of criteria. One option is =COUNTIF(A:A,"0.5")-COUNTIF(A:A,"=1.0") -- David Biddulph |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count cells with values x and <y
For some reason that is not working. When I use COUNTIF()-COUNTIF(), Im getting numbers that are obviously way off. Could it be becouse the col with the numbers was calculated on copied down formula? -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=558194 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count cells with values x and <y
Maybe there is a rounding problem
Try =SUMPRODUCT(--(ROUND(A5:A105,1)0.5),--(ROUND(A5:A105,1)<1.0)) Change ranges as needed but do not use A:A best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "jimbob" wrote in message ... For some reason that is not working. When I use COUNTIF()-COUNTIF(), Im getting numbers that are obviously way off. Could it be becouse the col with the numbers was calculated on copied down formula? -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=558194 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count cells with values x and <y
What do you get for COUNT(range)? Both COUNTIF and COUNT will ignore text
that only looks like a number. What is the formula that you are using? TRUNCATION error could be an issue as Bernard has suggested, but usually that would not throw counts "way off." If that is the issue, the D2D function from http://groups.google.com/group/micro...06871cf92f8465 may help you debug it. Jerry "jimbob" wrote: For some reason that is not working. When I use COUNTIF()-COUNTIF(), Im getting numbers that are obviously way off. Could it be becouse the col with the numbers was calculated on copied down formula? -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=558194 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
