I am using the attached list and I want to calculate the total picked up revenue in B9 by utilizing the formula: =(B13*D13)+(B14*D14)+(B15*D15)
However, sometimes we will need to delete row 14 for example and then the above formula shows a ref error. How can I rewrite this formula to avoid the ref error from occuring.
I have made this much simplier and in truth I will have a very long list of room names in which I will be deleting rows at random.
Thank you!
Cate
Replace
=(B13*D13)+(B14*D14)
With
=SUMPRODUCT(B13:B14,D13:D14)
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
thank you!
Also is there away in the above spreadsheet to add a column in the table that automatically calculates the number of nights they stay based on the inputted arrival date (column C) and departure date (column D)
= DepartureDate - ArrivalDate
Format it as a number, it will probably default to Date Format which will give you something like "Jan-5 1900". Changing to Number will give you 5.00 in that scenario.
thank you agan!
Hopefully the last question...
When the room type is selected in column H, I need the total number of picked up room nights for that type of room to calculate in the Picked Up Room Nights column (E) for that room type.
In the attached example, the guest has chosen a DTB 1 Queen Standard NUR for 9 nights. In cell E12 the number 9 needs to populate not 1.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks