if statements, and statements
=SUMPRODUCT(--(--LEFT(A2:A5,FIND("-",A2:A5)-1)<=C1),--(--MID(A2:A5,FIND("-",
A2:A5)+1,255)=C1),B2:B5)
you will need to change the first range to 0-100, and have unique ranges
that do not overlap.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Sum Limit and marking" wrote
in message ...
I have the following data:
QTY PRICE
<100 100
100-500 95
501-1000 90
1000-2500 85
A function that I am looking for is if I type in a quantity of 550 in a
cell, I want it to be able to retreive the correct pricing for whatever
quantity I request. In this case I want it to give me a price of 95. But
I
also want to be able to use the same cell to request a quantity of 1000
and
to be able to give me back the correct price of 85.
Does anyone have a suggestion?
|