Lookup number of values within a range

[sanwar1]

Hello all, I have a task that I need help with. Let's say on one cell I have the value of "267.0-268.3". And I have a master list that might be like something below:

267.0
267.1
267.3
267.8
268.9

I need a formula that will look at "267.0-268.3" and return the number of codes on master list that fall between those two numbers. So in this particular example, it will return the value of "4".

Any help is appreciated.

 

[RonB1111]

With codes in A1:A5, and cell A9 containing 267.0-268.3, use:

=SUMPRODUCT(--(A1:A5>=--LEFT(A9,FIND("-",A9)-1)),--(A1:A5<=--RIGHT(A9,LEN(A9)-FIND("-",A9))))

 

[sanwar1]

Hello Ron,

Thank you for the response. I tried this, but it doesn't seem to work. It's returning a value of 0.

 

[RonB1111]

It works on my spreadsheet. Would you post the data the way it's set-up in your worksheet. Do you have the data in cells A1 thru A5. Exactly how is the 267.0-268.3 entered in A9?

 

[sanwar1]

Okay, I think my issue is the formatting of data in my original table. I'll clean that up and try again. I just did what you suggested in a new sheet and it worked fine. Thank you so much for the help. That was exactly what I was looking for.

 

[RonB1111]

You're welcome. Thank you for providing feedback.