Date range compare to date range table

Leatherworks

New Member
Joined
Sep 26, 2016
Messages
6
I have the following problem:


The tables:

The prices per date range are in Columns G & H, prices in column K, per night.

I have a house where clients rent per night and they stay from dates in Column B to C.


Objective:

To compare the client's stay range to a price table to result in correct pricing per stay in column E.


The problem:

The Sumproduct array formula is resulting incorrectly when the start of the client's stay is on the last day of the month - all the rest are correct.

I know Sumproduct is a last resort, but I'm open to any other option. The final file has many more pages and many more dates so it would be best to be an in sheet formula and not in VBA.

Thank you.

Access to file: https://www.dropbox.com/s/hs1a63w2jj40sz9/Date range problem.xlsm?dl=0

Preview:
https://ctrlv.cz/yGR9



 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
mytable
termrent
1 - 79001900
start01/10/20168 - 148508850
end15/11/201615 - 2882515825
=days45< < < < C629 - 5680029800
rent80056 - 500078556785
5000785
rent formula
=VLOOKUP(C6,mytable,2)
I never download so I hope this helps

<colgroup><col span="2"><col><col span="10"></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top