1

I have a situation where room reservations can be managed by checkboxes. You can see an example in the screenshot below. I've consolidated several sheets into one to provide visualization but the data lives in separate sheets in the actual document...

 1. Columns A-L are a "Manage Reservations" sheet.
 2. Columns N-O are a "Pricing" sheet.
 3. Columns Q-R would be the output on a "Reservation
    Calendar" sheet.

Example Data Consolidated

I have two questions:

1. Is there a better way to calculate revenue in column L?

The method I'm using is working but it's a beast to manage, doesn't feel efficient and I'm sure there's some swankier way to do a lookup and SUM of the checkboxes / room number -> room rate.

2. How can I output the data in columns B-K (that represents the reservations) into what is shown in column Q-R?

I can't seem to figure out a way to QUERY or PIVOT the checkboxes and dates they represent into individual rows in another sheet. I had first tried some PIVOT tables but was running into issues with, what I gather is the source data formatting.

I'd like to keep the formatting of columns A-L, if possible. It's easy to manage and visualize. That said, if it needs to change to optimize the calculations and allow for me to get the output of columns O-P on another sheet... I'll take that as a lesson in better spreadsheet architecting.

Sample Reference Data Sheet

Any help would be appreciated!

4
  • As it stands, we don't even know what you have tried or what you are currently using. In addition, to even start, someone here would have to manually enter all of your data into a personal sheet, which is a deterrent considering this is a volunteer-run site. The most efficient and effective way to receive help is to share a link to a copy of your spreadsheet, being sure when you create the link to set its permissions to "Anyone with the link..." and "Editor." In short, there are efficient ways to achieve your goals. Beyond that, we'd need a link. – Erik Tyler Mar 28 at 0:46
  • Fair enough! I've added a link to the sample data in the original question. Viewer only though as that should be sufficient to copy but not allow people to change data. – CVEEP Mar 28 at 15:53
  • Since you have marked the solutions offered by Tedinoz as "Best Answer," it seems you are now all set. – Erik Tyler Mar 28 at 19:29
  • I will only add that you don't need a drag-down formula in Column L. Delete everything in Column L (including the header) and place this in L1: =ArrayFormula({"Revenue";IF(B2:B="",,MMULT(IF(C2:K<>TRUE,0,TRANSPOSE(O2:O10)),SEQUENCE(COLUMNS(C:K),1,1,0)))}) – Erik Tyler Mar 28 at 19:36
1

You want an efficient alternative to calculate revenue in column L.

Try this formula in the Cell L3 (based on the sample data below:

=arrayformula(sumproduct(C3:K3,pricing!$D$2:$L$2))

Sumproduct multiples, then sums, the values in multiple arrays. Note that the pricing data has been transposed so that the data is taken row-wise.

Unfortunately, SUMPRODUCT can't be used in with ARRAYFORMULA, so you have to copy the formula down as many rows as required.


You want an alternative formula for displaying the data in Columns Q-R.

Try this formula: in cell N2 (based on the sample data below:

=query({"Date","Room","";ARRAYFORMULA({IFERROR(SPLIT(FLATTEN(IF(B3:B="",,B3:B&"♦"&C2:E2&"♦"&C3:E)), "♦"))})},"select Col1, Col2 format Col1 'dd-MMM-yyyy'")

This formula automatically populates the output for each row


Sample data and results

sample

4
  • Thank you for taking the time to assist! Your first formula worked pretty well (needed to tweak it a bit) and is something I'm going to dig into more as I haven't leveraged SUMPRODUCT very often. If you have a moment, would you mind walking me through the second formula a bit? I'm trying to understand the special characters and some of the other logic in the FLATTEN statement. Again, this was very helpful. Thank you! – CVEEP Mar 28 at 17:29
  • To be clear, it's this part that I'm having trouble following: IF(B3:B="",,B3:B&"♦"&C2:K2&"♦"&C3:K). – CVEEP Mar 28 at 17:54
  • Managed to decipher it all! The delimiter characters were throwing me off and a conditional was needed in the query to get it to only spill dates that had a TRUE value for the room. I think I've got it working now. I've updated the sample data! – CVEEP Mar 28 at 18:12
  • Flatten takes the value from one or more ranges and creates a single column. This is the method used to obtain the room number that is associated with a given value of TRUE. There are actually three columns generated, - Date, Room# and Room value (though Room value is not includes in the "select" clause. Split separates the three values - any unique value could be used as a separator; I found this method in research (I forget where but props to whoever it was) and I used a separator value that was originally used. – Tedinoz Mar 28 at 18:22
1

Since you're open to slight changes to your design, you can try this:

REVENUE:

=ARRAYFORMULA(IF(LEN(B4:B),MMULT(C2:K2*C4:K,TRANSPOSE(COLUMN(C2:K2)^0)), ))

SUMMARY BY DATES & ROOMS:

=ARRAYFORMULA(IF(LEN(B4:B),
sort(split(TO_DATE(B4:B)&"😊"&REGEXREPLACE(TRIM(
TRANSPOSE(QUERY(TRANSPOSE(
 IF((B4:K<>"")*(B4:K=TRUE), B3:K3&"-", )),,999^99))),
 "-$", )&"😊"&L4:L,"😊"),1,true),""))

enter image description here

Summary by month:

For the fun you can also add this:

= {
    query(
        query({
            ArrayFormula(
                if (len(N4: N), PROPER(text(N4: N, "MMMM yyyy")), "")),
            P4: P
        }),
        "Select Col1 ,sum(Col2) where Col1 is not null 
        group by Col1 Order By Col1 desc label sum(Col2)
        'Revenue', Col1 'Months'
        "); {
            "TOTAL",
            SUM(P4: P)
        }
    }

enter image description here

SPREADSHEET DEMO: HERE

2
  • Unfortunately, this did not end up being what I was looking for but I wanted to thank you for taking the time to answer and show all of the detailed work. As I was working through some of your formulas to understand how they were working, I actually learned quite a bit so, while it may not have answered my questions in the way I was looking for, it was extremely helpful in understanding some other ways to approach things. Thank you! – CVEEP Mar 28 at 18:16
  • No problem. But I'm not sure if the desired output in your columns Q-R is the best approach to display data, specially when the data starts growing dow to the bottom of the sheet. That's why I've suggested the above approach for a better visibility of your data by date. Anyway, remember that's sometimes although we receive an answer that solves our initial question, very often we improve our thinking and go looking for another solution,and so on – Nabnub Mar 28 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.