Sumifs

[adrienmeszaros]

Hi from Belgium!

Could an expert please have a look at the attached document?

sample file.xlsx

On the left (columns A to I), a list of hotel reservations for a specific company.

On the right, I would like to summarize the revenue generated by this specific company on each stay date.

My formula in column L is working, but not the one in column M (it gives wrong values)

Eg. on the 01/09/2014, in cell M2, I would like the result to be 20.75 (there is one reservation that stayed on the 01/09, and the average room rate (REAL) in column I is equal to 20.75.

This is my formula:
=SUMIFS(I:I,A:A, "<="&K2,B:B, ">="&K2)

Could someone have a look?

Thanks a lot!

Kind Regards,

[mcmahobt]

As you currently have your formula setup, the SUMIFS formula is not giving incorrect values. That is because the dates in cells A2:B7 all satisfy the conditions you stated (1 Sept. >= 31 Aug. and 1 Sept / 1 Sept. <= 1 Sept. and 2 Sept.)

What needs retooling is your logic behind your formula.

[adrienmeszaros]

As you currently have your formula setup, the SUMIFS formula is not giving incorrect values. That is because the dates in cells A2:B7 all satisfy the conditions you stated (1 Sept. >= 31 Aug. and 1 Sept / 1 Sept. <= 1 Sept. and 2 Sept.)

What needs retooling is your logic behind your formula.

Thanks a lot for your help!

What formula should I put if I do not want to take into account the cells from A2:B6?
These guests have stayed the night of the 31/08, not the night of the 01/09.

Thanks again!!!

Adrien

[underscoreBadger]

Adrien,

You could try changing the formula in column L to:

Please Login or Register  to view this content.

This simply removes the '=' from the second condition.

The logic is that this sums the average room rate where arrival date is on or before that night, and departure date is after (but not on) that night.

I hope this helps.