I have a large database, so I'll give a small example of my issue:

I have three rows of data in three four columns for guests of the ABC Hotel:

Name Revenue Arrival Departure

Jones $350 1/2/2013 1/3/2013
Smith $150 1/1/2013 1/4/2013
Wilson $200 1/1/2013 1/2/2013

I want to count the nights stayed, and add up the revenue for all of those who stayed between 1/1/2013 and 1/2/2013 (both days).

For simplicity, my example will focus on just adding the revenue. Note the departing date does not count as a stay date, so the total should come out to:

Jones stayed on 1/2, so add $350
Smith stayed on 1/1 and 1/2, so add $150x2 = $300
Wilson stayed on 1/1, so add $200

Total = $850

I usually use COUNTIFS or SUMIFS a lot, but can't seem to figure out how to account for rows of data (like Smith) which need to have a field summed multiple times, since they stayed both of the days in question. I want something likes this:

=SUMIFS(Revenue, Arrival,">= 1/1/2013", Departure,"< 1/2/2013") ' but this doesn't sum the Smith row twice, to account that Smith stayed both days .

I need a formula or array or something that will basically say sum all Revenue for each qualifying date between arrival and departure for each record in the database. Maybe SUMPRODUCT, but I'm at a loss how to put this together.

Any help appreciated!

Mark