Calculate a total holiday price based upon different nightly room rates

Oops Sorry !

OK then, I'll start again, I really need some help here with Excel 2000 and hope someone can help.

I've created a worksheet of rooms (shown in columns) and nightly prices (shown in rows) per the attached copy of my worksheet.

I would like to create a facility where I can enter an arrival date (e.g. 04/04/12) in cell A20 and a departure date (e.g. 09/04/12) in cell A21 and, for each column, (or room) it shows the total price (e.g. 249 for column D, 264 for column F and so on and so on).

Any ideas ?

Thanks

Thanks for the reply, you're quite right, the excel spreadsheet I attached isn't consistent with the PDF (I had to remove some data from the one seen in PDF) and the actual spreadsheet attached is an example.

I've entered the formula you suggest into cell D13, and entered arrival and departure dates into cells A13 and A14 but it tells me it's a circular reference and there's no total price shown ?

Thanks

Thanks but it's now returning a "#NAME?" error message.

Ihed the spreadhseet with the formula entered into those cells so you can see what I mean.

That's brilliant thank you, I've had to change the formula slightly to...

=SUMPRODUCT(D$16:D$449,--($A$16:$A$449>=A13),--($A$16:$A$449<A14))

...as for an arrival on (for example) 04/04/12 and a departure on 09/04/12 it totals the prices for 6 nights as opposed to 5 nights which it should be; have I got the formula change correct please ?

Thank you so much, that's brilliant and you've been really helpful and I'll mark it as solved and click to show my appreciation.