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

R

roland

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.
 
R

RagDyer

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
?
 
T

T. Valko

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,$A2>499),"",IF(B1=LOOKUP($A2,$B1:$F1),1,""))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top