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)
<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.
<tbody>
</tbody>
I can get some of the cells to easily work given there are simple equations
<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!
<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
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)
<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.
<tbody> </tbody> |
<tbody>
</tbody>
I can get some of the cells to easily work given there are simple equations
<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!
<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