hotel system
Posted by Catherine on January 29, 2002 1:59 PM
I am trying to set up a hotel booking/invoice system (assignment) on one worksheetI have columns set out as
Date Room No Service Amount
The different services are food, drink, laundry,payTv
what I want to do is when someone checks out I want to add up all the different services for the room checking out eg I want a total for food, drinks, laundry etc. I know I can use sumif to sum the individual categories but how do I link with the room numbers. Or does anyone have a working solution for a 20 room hotel.
Posted by Aladin Akyurek on January 29, 2002 3:19 PM
Catherine --
Try PivotTables or use a system of formulas like the one that follows.
Lets say that your have the label Date in A1, Room No in B1, Service in C1, and Amount in D1.
In F1 enter: =MATCH(9.99999999999999E+307,A:A)
Make a list of room numbers in F from F2 on.
In G1:K1 enter: Food, Drink, Laundry, PayTV, Total.
In G2 enter: =SUMPRODUCT((OFFSET($B$2,0,0,$F$1,1)=$F2)+0,(OFFSET($C$2,0,0,$F$1,1)=G$1)+0,(OFFSET($D$2,0,0,$F$1,1)))
Copy this across till J2.
In K2 enter: =SUM(G2:J2)
Select G2:K2 and copy down the selection till the last room number in F.
PS. If interested in getting a copy of the workbook showing the above, just drop me a line.
Aladin
======
