Help with a Formula that for SUM of count using three categories (two with single "text", and the third has variable text

[RobertJ]

Hi, only a newbie to excel here. I've created a booking card for our small B&B. We offer FULL BOARD and B&B, to which I am trying to run a billing section at the bottom. (screen shot link in this thread)

Col E7:E18 [Guest Type] Col F7:F18 [Catering Type] Col G7:G18 [Room Type] Col H7:H18 [Date of Arrival] Col J7:J18 [Date of Departure] Col L7:1 [No. of Nights; simple J-H]

Lists


· [Guest Type]; "ADULT", "CHILD", "INF" (Inf is non chargeable so there is no requirement for billing)
· [Catering type]; "FULL", "B&B"
· [Room Type]; "DBL","TWN", "SGL", "FOLDAWAY","SOFA BED", "COT INF" (again the COT Inf has no billing requirement)

A B C D E F G H J L M N O GUEST INFORMATION OFFICE USE GUEST TYPE CATERING TYPE ROOM TYPE ARRIVAL DEPARTURE NIGHTS ROOM CHECKEDIN CHECKED OUT GUEST NAME 7 1 Mr John Doe ADULT FULL SGL 27/01/19 30/01/19 3 201 8 Mobile SUNDAY WEDNESDAY 9 2 MS Jane Joe CHILD FULL SGL 27/01/19 29/01/19 2 202 10 Mobile SUNDAY TUESDAY 11 3 Ms A BCDE ADULT FULL DBL 27/01/19 30/01/19 3 211 12 Mobile SUNDAY WEDNESDAY 13 4 Miss J BCDE CHILD FULL FOLDAWAY 27/01/19 30/01/19 3 211 14 Mobile SUNDAY WEDNESDAY 15 5 Mr U VWXYZ ADULT B&B SGL 01/03/19 03/03/19 2 309 16 Mobile FRIDAY SUNDAY 17 6 Mr L MNOPQ CHILD B&B SGL 01/03/19 02/03/19 1 304 18 Mobile FRIDAY SATURDAY <tbody> </tbody>

<tbody>
</tbody>

<tbody>
</tbody>

I have an billing section that needs to work out the total number of nights for:
· Single or Shared person rate
· Adult or Child
· Full board or Bed and Breakfast.

25 GUEST INFORMATION OR BOOKING NOTES REGARDING THIS BOOKING (SHARING INFORMATION) 26 27 28 29 OFFICE USE ONLY 30 CATERING TRACKER CONFIRM LATE ARR 31 BILLING DETAILS 32 ITEMS FB GUESTS RATE PPPN NIGHTS TOTAL ITEMS BB GUESTS RATE PPPN NIGHTS TOTAL ITEM TOTAL 33 ADULTS SHARE FULL 1 £48.25 £0.00 ADULTS SHARE B&B 0 £32.00 £0.00 LOST KEYS £20.00 GRAND TOTAL 34 CHILDRN SHARE FULL 1 £24.13 £0.00 CHILDREN SHARE B&B 0 £16.00 £0.00 LATE FEES £50.00 35 ADULTS SINGLE FULL 1 £54.25 3 £162.75 ADULTS SINGLE B&B 1 £38.00 2 £76.00 MISC £0.00 £382.01 36 CHILDRN SNGLE FULL 1 £27.13 2 £54.26 CHILDREN SINGLE B&B 1 £19.00 1 £19.00 OTHER TOTAL FULL 4 5 £217.01 TOTAL B&B 2 £95.00 TOTAL £70.00 A B C D E F G H I J K L M N O <tbody> </tbody>

<tbody>
</tbody>

I can get some of the cells to easily work given there are simple equations

Cell c33 is "=SUM(COUNTIFS(G7:G18,{"DBL","TWN","FOLDAWAY","SOFA BED"},E7:E18,{"ADULT"},F7:F18,{"FULL"}))" Cell c35 is "=SUM(COUNTIFS(G7:G18,{"SGL"},E7:E18,{"ADULT"},F7:F18,{"FULL"}))" Cell i33 is "=SUM(COUNTIFS(G7:G18,{"DBL","TWN","FOLDAWAY","SOFA BED"},E7:E18,{"ADULT"},F7:F18,{"B&B"}))" Cell E35 is "=SUMIFS(L7:L18,E7:E18,"ADULT",F7:F18,"FULL",G7:G18,"SGL")" Cell K36 is "=SUMIFS(L7:L18,E7:E18,"CHILD",F7:F18,"B&B",G7:G18,"SGL")" <tbody> </tbody>

<tbody>
</tbody>

Where I am struggling to find the correct formula for cells: E33, E34, and K33, K34 (the highlight in yellow). I am trying to sum count L7:L18 based on criteria in E7:E18, F7:F18, and G7:G18 (which are all lists). As you can see from above the E/F Lists are ok, where a single item is in G7:G18 i.e. "SGL", however, G7:G18 is giving me problems where I need to check against multiple various items. I can't work out how to array or check against the multiple optional information that can be displayed in G row. FYI the count is only needed to check against the matached criteria for Row G in the orange items highlighted to the below. Any help would be greatly appreciated. Thank you Excel masterers!

ROW G DBL TWN SGL FOLDAWAY SOFA BED COT (inf) <tbody> </tbody>

<tbody>
</tbody>

Many many thanks in advance for any help

Screen shot of the full sheet here
https://drive.google.com/open?id=1O-iknSwFV0equZh7FncllwZnBaZdQZmt

 

[steve the fish]

If C33 is working for you then E33 is:

=SUM(SUMIFS(L7:L18,G7:G18,{"DBL","TWN","FOLDAWAY","SOFA BED"},E7:E18,"ADULT",F7:F18,"FULL"))

The rest should be straightforward knowing you can also use SUM to sum the 4 SUMIFS results ('DBL,ADULT,FULL','TWN,ADULT,FULL' etc in this instance).

 

[RobertJ]

Steve the fish! Thanks Bro, I genuinely thought I had tried that formula, ... Ha! It must be my eyes... Greatly appreciate you responding and a cheeky copy and paste, and few copy edits and it has fixed my problem!

Keep being awesome!

R