Formula help to subtract number in another sheet

KelJoy

I have a sheet that is adding the nights booked and another sheet that I want to use a formula that will subtract the nights booked from the fixed contracted number. The criteria is to match the hotel name in the sheet to that in the "nights booked" sheet, and for this hotel, the contracted nights is 110. Error message is #Unparseable.

This is the "Room Nights Booked" sheet:

This is the "Remaining Nights" where I want [110-3] to calculate

Here is the formula I have

=SUMIF({Room Nights Booked Hotel}, =[Primary Column]@row, [110-{Room Nights Booked 15JUL}])

Michael Drayton

Hi friend, Maybe I'm reading too much into this, but I'm guessing that each hotel has a different contract length.
Sofitel is 110, Les Haras is 80, A Loft is 100, etc…

Sheet 1: "Contracted Nights" Sheet: This sheet contains the fixed contracted number for each hotel.
"Contracted Nights" Column: Contains the fixed contracted number of nights (e.g., 110), in this way you formula doesn't have to do the hard work.

Sheet 2: "Nights Booked" Sheet: This sheet contains the data about room nights booked for each hotel.

• "Hotel Name" Column: Contains the name of the hotel.
• "Room Nights Booked" Column: Contains the number of room nights booked for that hotel.
  
  So in Sheet 1, th formula will look like this:
  =[Contracted Nights]@row - SUMIF({Room Nights Booked Hotel}, [Hotel Name]@row, {Room Nights})
  

Let me know if that worked. Good luck!

Janae G.

Hello!

Your formula is close- I think you just have to move where you are doing the subtraction to outside of the SUMIF. The way it is set up now, the [110-{Room Nights Booked 15JUL}] part is expecting a range, not a value. Smartsheet also doesn't know what to do with subtracting a range from 110.

This should fix it:

=110 - SUMIF({Room Nights Booked Hotel}, =[Primary Column]@row, {Room Nights Booked 15JUL})

If you are only expecting it to be summing one cell (as in there is only one row for each hotel), you could do an INDEX MATCH instead. Either works, so use whichever you prefer.

=110 - INDEX({Room Nights Booked 15JUL}, MATCH([Primary Column]@row, {Room Nights Booked Hotel}, 0))

KelJoy

Drat - I still get the same error message :(

KelJoy

Oh, wait - I figured out one error - I had [Primary Column]@row instead of [Hotel]@row.

Now I get an #incorrect argument set.

=110 - SUMIF({Room Nights Booked Hotel}, Hotel@row, {Room Nights Booked 15JUL})

KelJoy

Thanks Janae - you helped me solve it. The last change I needed to make was to edit the reference of "Room Nights Booked Hotel" to not have the column selected but instead just the applicable rows in that column.

BINGO - Error Solved!