This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi.
I need help with a formula.
I currently use the SumProduct formula that basically gives me a number from a range is a cell is in between two values for the following table:
=SUMPRODUCT(($H$17=Ranges!$F$2:$F$200)*(G159>=Ranges!$G$2:$G$200)*(G159<=Ranges!$H$2:$H$200)*(Ranges!$I$2:$I$200))
| Supervision | Range1 | Range2 | Hours |
|---|---|---|---|
| Competitive Quote | $0.01 | $1000.00 | 1 |
| Competitive Quote | $1000.01 | $3000.00 | 3 |
| Competitive Quote | $3000.01 | $5000.00 | 5 |
| Competitive Quote | $5000.01 | $11,000.00 | 7 |
| Competitive Quote | $11,000.01 | $15,000.00 | 11 |
Now my ranges have changed and I need a new formula for the following table:
I need it to say that if my price is between Range1 and Range2, and I have a certain amount of trades which is identified in cell B160, return the hours Value.....
| Supervision | Range1 | Range2 | 1-2 Trades Hours | 3-4 Trades Hours | 5-9 Trades Hours | 10+ Trades Hours |
|---|---|---|---|---|---|---|
| Competitive Quote | $0.01 | $1000.00 | 1 | 2 | 0 | 0 |
| Competitive Quote | $1000.01 | $3000.00 | 2 | 3 | 0 | 0 |
| Competitive Quote | $3000.01 | $5000.00 | 3 | 4 | 0 | 0 |
| Competitive Quote | $5000.01 | $11,000.00 | 5 | 7 | 4 | 5 |
| Competitive Quote | $11,000.01 | $15,000.00 | 7 | 9 | 8 | 12 |
Thanks in advance!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
To: Monica
re: table data
I altered your table and came up with this...
The free Custom_Functions add-in has 20 new Excel functions including the X_vLookUp function
Download from (no ads) ... *http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents*
To: Monica
re: formula has error
Yes, I am having trouble understanding what you have displayed.
(my normal state of mind)
What are the values in range D1:N1 ?
They should be in ascending order with no duplicates and the last entry (N1) should be larger than any possible lookup value. N1 is used as a road block; it is where the road ends.
Hi there!!
Everything you have provided is great but I seem to have found an issue when I input more than 5 of the same trade type in a column. I think the screen shots will be better to show you whats happening.
and when the trade type roofer is added to that highlighted box, the supervision disappears.
I've narrowed it down to the highlighted section of the formula below as when I open the formula box, the Column_num = #N/A
As soon as I remove that 6th trade type, it can find the column number
I hope the above makes sense.
Thanks
To: Monica
You are welcome. Glad you can use it.
Thank you so much!