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

RobertJ

New Member
Joined
Jan 28, 2019
Messages
2
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)


ABCDEFGHJLMNO
GUEST INFORMATIONOFFICE USE
GUEST TYPECATERING TYPEROOM TYPEARRIVALDEPARTURENIGHTSROOMCHECKEDINCHECKED OUT
GUESTNAME
71Mr John DoeADULTFULLSGL27/01/1930/01/193201
8MobileSUNDAYWEDNESDAY
92MS Jane JoeCHILDFULLSGL27/01/1929/01/192202
10MobileSUNDAYTUESDAY
113Ms A BCDEADULTFULLDBL27/01/1930/01/193211
12MobileSUNDAYWEDNESDAY
134Miss J BCDECHILDFULLFOLDAWAY27/01/1930/01/193211
14MobileSUNDAYWEDNESDAY
155Mr U VWXYZADULTB&BSGL01/03/1903/03/192309
16MobileFRIDAYSUNDAY
176Mr L MNOPQCHILDB&BSGL01/03/1902/03/191304
18MobileFRIDAYSATURDAY

<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.


25GUEST INFORMATION OR BOOKING NOTES REGARDING THIS BOOKING (SHARING INFORMATION)
26
27
28
29OFFICE USE ONLY
30CATERINGTRACKERCONFIRMLATE ARR
31BILLING DETAILS
32ITEMS FBGUESTSRATE PPPNNIGHTSTOTALITEMS BBGUESTSRATE PPPNNIGHTSTOTALITEMTOTAL
33ADULTS SHARE FULL1£48.25£0.00ADULTS SHARE B&B0£32.00£0.00LOST KEYS£20.00GRAND TOTAL
34CHILDRN SHARE FULL1£24.13£0.00CHILDREN SHARE B&B0£16.00£0.00LATE FEES£50.00
35ADULTS SINGLE FULL1£54.253£162.75ADULTS SINGLE B&B1£38.002£76.00MISC£0.00£382.01
36CHILDRN SNGLE FULL1£27.132£54.26CHILDREN SINGLE B&B1£19.001£19.00OTHER
TOTAL FULL45£217.01TOTAL B&B2£95.00TOTAL£70.00
ABCDEFGHIJKLMNO

<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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 28, 2019
Messages
2
Steve the fish! Thanks Bro, I genuinely thought I had tried that formula, :eek: ... 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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,132
Messages
5,628,891
Members
416,349
Latest member
salmanb74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top