COUNT IF A VALUE FALLS WITHIN A RANGE IN A SINGLE CELL
 

example:
H/S 100's 50's 200's 300's 400's
57 1
is there a formula that can automatically sort the figure under H/S into the
respective groupings, e.g 57 would fall under 50's. return that figure also
as 1 as illustrated above.

COUNT IF A VALUE FALLS WITHIN A RANGE IN A SINGLE CELL
 

What happens if the number is less then 50?
What happens if the number is greater then 499?
Can the fields be in numerical order without the < 's ,
50 100 200 300 400
?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"roland" wrote in message
...
example:
H/S 100's 50's 200's 300's 400's
57 1
is there a formula that can automatically sort the figure under H/S into
the
respective groupings, e.g 57 would fall under 50's. return that figure
also
as 1 as illustrated above.

COUNT IF A VALUE FALLS WITHIN A RANGE IN A SINGLE CELL
 

Try this:

List the boundaries in ascending order and drop the "s" :

...........A.....B......C......D.......E.....F
1...............50...100...200...300...400
2.......57........................................ ....

Enter this formula in B2 and copy across to F2:

=IF(OR($A2<$B1,$A2499),"",IF(B1=LOOKUP($A2,$B1:$F 1),1,""))

--
Biff
Microsoft Excel MVP


"roland" wrote in message
...
example:
H/S 100's 50's 200's 300's 400's
57 1
is there a formula that can automatically sort the figure under H/S into
the
respective groupings, e.g 57 would fall under 50's. return that figure
also
as 1 as illustrated above.