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)


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1166"]
<tbody>[TR]
[TD="width: 71"][/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]A[/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]B[/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]C[/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]D[/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]E[/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]F[/TD]
[TD="class: xl139, width: 84, bgcolor: #F2F2F2, align: center"]G[/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]H[/TD]
[TD="class: xl139, width: 88, bgcolor: #F2F2F2, align: center"][/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]J[/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"][/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]L[/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]M[/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]N[/TD]
[TD="class: xl139, width: 71, bgcolor: #F2F2F2, align: center"]O[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #F2F2F2, align: center"][/TD]
[TD="class: xl137, bgcolor: black, colspan: 10, align: center"]GUEST INFORMATION[/TD]
[TD="class: xl74, bgcolor: black, colspan: 5, align: center"]OFFICE USE[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #F2F2F2, align: center"][/TD]
[TD="class: xl71, bgcolor: black, align: center"][/TD]
[TD="class: xl67, bgcolor: black, align: center"][/TD]
[TD="class: xl67, bgcolor: black, align: center"][/TD]
[TD="class: xl67, bgcolor: black, align: center"][/TD]
[TD="class: xl98, width: 71, bgcolor: black, align: center"]GUEST TYPE[/TD]
[TD="class: xl98, width: 71, bgcolor: black, align: center"]CATERING TYPE[/TD]
[TD="class: xl98, width: 84, bgcolor: black, align: center"]ROOM TYPE[/TD]
[TD="class: xl77, bgcolor: black, colspan: 2, align: center"]ARRIVAL[/TD]
[TD="class: xl77, bgcolor: black, colspan: 2, align: center"]DEPARTURE[/TD]
[TD="class: xl77, bgcolor: black, align: center"]NIGHTS[/TD]
[TD="class: xl77, bgcolor: black, align: center"]ROOM[/TD]
[TD="class: xl79, width: 71, bgcolor: black, align: center"]CHECKEDIN[/TD]
[TD="class: xl81, width: 71, bgcolor: black, align: center"]CHECKED OUT[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #F2F2F2, align: center"][/TD]
[TD="class: xl72, bgcolor: black, align: center"]GUEST[/TD]
[TD="class: xl66, bgcolor: black"]NAME[/TD]
[TD="class: xl66, bgcolor: black"][/TD]
[TD="class: xl66, bgcolor: black"][/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]7[/TD]
[TD="class: xl129, width: 71, bgcolor: black, align: center"]1[/TD]
[TD="class: xl117, colspan: 3"]Mr John Doe[/TD]
[TD="class: xl88, align: center"]ADULT[/TD]
[TD="class: xl88, align: center"]FULL[/TD]
[TD="class: xl127, width: 84, bgcolor: #FFC000, align: center"]SGL[/TD]
[TD="class: xl86, colspan: 2, align: center"]27/01/19[/TD]
[TD="class: xl86, colspan: 2, align: center"]30/01/19[/TD]
[TD="class: xl123, bgcolor: #92D050, align: center"]3[/TD]
[TD="class: xl96, align: center"]201[/TD]
[TD="class: xl92, align: center"][/TD]
[TD="class: xl84, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]8[/TD]
[TD="class: xl65"]Mobile[/TD]
[TD="class: xl94, colspan: 2"][/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]SUNDAY[/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]WEDNESDAY[/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]9[/TD]
[TD="class: xl131, width: 71, bgcolor: black, align: center"]2[/TD]
[TD="class: xl120, bgcolor: #D9D9D9, colspan: 3"]MS Jane Joe[/TD]
[TD="class: xl100, bgcolor: #D9D9D9, align: center"]CHILD[/TD]
[TD="class: xl100, bgcolor: #D9D9D9, align: center"]FULL[/TD]
[TD="class: xl127, width: 84, bgcolor: #FFC000, align: center"]SGL[/TD]
[TD="class: xl87, bgcolor: #D9D9D9, colspan: 2, align: center"]27/01/19[/TD]
[TD="class: xl87, bgcolor: #D9D9D9, colspan: 2, align: center"]29/01/19[/TD]
[TD="class: xl125, bgcolor: #92D050, align: center"]2[/TD]
[TD="class: xl109, bgcolor: #D9D9D9, align: center"]202[/TD]
[TD="class: xl103, bgcolor: #D9D9D9, align: center"][/TD]
[TD="class: xl105, bgcolor: #D9D9D9, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]10[/TD]
[TD="class: xl69, bgcolor: #D9D9D9"]Mobile[/TD]
[TD="class: xl115, bgcolor: #D9D9D9, colspan: 2"][/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]SUNDAY[/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]TUESDAY[/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]11[/TD]
[TD="class: xl131, width: 71, bgcolor: black, align: center"]3[/TD]
[TD="class: xl117, colspan: 3"]Ms A BCDE[/TD]
[TD="class: xl88, align: center"]ADULT[/TD]
[TD="class: xl88, align: center"]FULL[/TD]
[TD="class: xl127, width: 84, bgcolor: #FFC000, align: center"]DBL[/TD]
[TD="class: xl86, colspan: 2, align: center"]27/01/19[/TD]
[TD="class: xl86, colspan: 2, align: center"]30/01/19[/TD]
[TD="class: xl125, bgcolor: #92D050, align: center"]3[/TD]
[TD="class: xl111, align: center"]211[/TD]
[TD="class: xl90, align: center"][/TD]
[TD="class: xl107, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]12[/TD]
[TD="class: xl65"]Mobile[/TD]
[TD="class: xl94, colspan: 2"][/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]SUNDAY[/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]WEDNESDAY[/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]13[/TD]
[TD="class: xl133, width: 71, bgcolor: black, align: center"]4[/TD]
[TD="class: xl120, bgcolor: #D9D9D9, colspan: 3"]Miss J BCDE[/TD]
[TD="class: xl100, bgcolor: #D9D9D9, align: center"]CHILD[/TD]
[TD="class: xl100, bgcolor: #D9D9D9, align: center"]FULL[/TD]
[TD="class: xl127, width: 84, bgcolor: #FFC000, align: center"]FOLDAWAY[/TD]
[TD="class: xl87, bgcolor: #D9D9D9, colspan: 2, align: center"]27/01/19[/TD]
[TD="class: xl87, bgcolor: #D9D9D9, colspan: 2, align: center"]30/01/19[/TD]
[TD="class: xl125, bgcolor: #92D050, align: center"]3[/TD]
[TD="class: xl109, bgcolor: #D9D9D9, align: center"]211[/TD]
[TD="class: xl103, bgcolor: #D9D9D9, align: center"][/TD]
[TD="class: xl105, bgcolor: #D9D9D9, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]14[/TD]
[TD="class: xl70, bgcolor: #D9D9D9"]Mobile[/TD]
[TD="class: xl115, bgcolor: #D9D9D9, colspan: 2"][/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]SUNDAY[/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]WEDNESDAY[/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]15[/TD]
[TD="class: xl135, width: 71, bgcolor: black, align: center"]5[/TD]
[TD="class: xl117, colspan: 3"]Mr U VWXYZ[/TD]
[TD="class: xl88, align: center"]ADULT[/TD]
[TD="class: xl88, align: center"]B&B[/TD]
[TD="class: xl127, width: 84, bgcolor: #FFC000, align: center"]SGL[/TD]
[TD="class: xl86, colspan: 2, align: center"]01/03/19[/TD]
[TD="class: xl86, colspan: 2, align: center"]03/03/19[/TD]
[TD="class: xl125, bgcolor: #92D050, align: center"]2[/TD]
[TD="class: xl111, align: center"]309[/TD]
[TD="class: xl90, align: center"][/TD]
[TD="class: xl107, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]16[/TD]
[TD="class: xl65"]Mobile[/TD]
[TD="class: xl94, colspan: 2"][/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]FRIDAY[/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]SUNDAY[/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]17[/TD]
[TD="class: xl135, width: 71, bgcolor: black, align: center"]6[/TD]
[TD="class: xl120, bgcolor: #D9D9D9, colspan: 3"]Mr L MNOPQ[/TD]
[TD="class: xl100, bgcolor: #D9D9D9, align: center"]CHILD[/TD]
[TD="class: xl100, bgcolor: #D9D9D9, align: center"]B&B[/TD]
[TD="class: xl127, width: 84, bgcolor: #FFC000, align: center"]SGL[/TD]
[TD="class: xl87, bgcolor: #D9D9D9, colspan: 2, align: center"]01/03/19[/TD]
[TD="class: xl87, bgcolor: #D9D9D9, colspan: 2, align: center"]02/03/19[/TD]
[TD="class: xl125, bgcolor: #92D050, align: center"]1[/TD]
[TD="class: xl109, bgcolor: #D9D9D9, align: center"]304[/TD]
[TD="class: xl103, bgcolor: #D9D9D9, align: center"][/TD]
[TD="class: xl105, bgcolor: #D9D9D9, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl136, bgcolor: #F2F2F2, align: center"]18[/TD]
[TD="class: xl68, bgcolor: #D9D9D9"]Mobile[/TD]
[TD="class: xl115, bgcolor: #D9D9D9, colspan: 2"][/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]FRIDAY[/TD]
[TD="class: xl83, bgcolor: black, colspan: 2, align: center"]SATURDAY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1166"]
<tbody>[TR]
[TD="class: xl134, width: 71, bgcolor: #F2F2F2, align: center"]25[/TD]
[TD="class: xl88, width: 1095, bgcolor: black, colspan: 15, align: center"]GUEST INFORMATION OR BOOKING NOTES REGARDING THIS BOOKING (SHARING INFORMATION)[/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]26[/TD]
[TD="class: xl92, colspan: 15"][/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]27[/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]28[/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]29[/TD]
[TD="class: xl88, bgcolor: black, colspan: 15, align: center"]OFFICE USE ONLY[/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]30[/TD]
[TD="class: xl132, bgcolor: black, align: center"]CATERING[/TD]
[TD="class: xl66, align: center"][/TD]
[TD="class: xl67, align: center"][/TD]
[TD="class: xl68, bgcolor: black, align: center"]TRACKER[/TD]
[TD="class: xl66, align: center"][/TD]
[TD="class: xl69, align: center"][/TD]
[TD="class: xl68, bgcolor: black, align: center"]CONFIRM[/TD]
[TD="class: xl66, align: center"][/TD]
[TD][/TD]
[TD="class: xl69, align: center"][/TD]
[TD="class: xl68, bgcolor: black, align: center"]LATE ARR[/TD]
[TD="class: xl66, align: center"][/TD]
[TD][/TD]
[TD="class: xl69, align: center"][/TD]
[TD="class: xl79"][/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]31[/TD]
[TD="class: xl94, bgcolor: black, colspan: 15, align: center"]BILLING DETAILS[/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]32[/TD]
[TD="class: xl100, bgcolor: black, colspan: 2"]ITEMS FB[/TD]
[TD="class: xl70, bgcolor: black, align: center"]GUESTS[/TD]
[TD="class: xl70, bgcolor: black, align: center"]RATE PPPN[/TD]
[TD="class: xl70, bgcolor: black, align: center"]NIGHTS[/TD]
[TD="class: xl70, bgcolor: black, align: center"]TOTAL[/TD]
[TD="class: xl100, bgcolor: black, colspan: 2"]ITEMS BB[/TD]
[TD="class: xl70, bgcolor: black, align: center"]GUESTS[/TD]
[TD="class: xl70, bgcolor: black, align: center"]RATE PPPN[/TD]
[TD="class: xl70, bgcolor: black, align: center"]NIGHTS[/TD]
[TD="class: xl70, bgcolor: black, align: center"]TOTAL[/TD]
[TD="class: xl70, bgcolor: black, align: center"]ITEM[/TD]
[TD="class: xl70, bgcolor: black, align: center"]TOTAL[/TD]
[TD="class: xl80, bgcolor: black, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]33[/TD]
[TD="class: xl97, colspan: 2"]ADULTS SHARE FULL[/TD]
[TD="class: xl72, align: center"]1[/TD]
[TD="class: xl73, align: center"]£48.25[/TD]
[TD="class: xl124, bgcolor: yellow, align: center"][/TD]
[TD="class: xl123, bgcolor: #F2F2F2, align: center"]£0.00[/TD]
[TD="class: xl112, colspan: 2"]ADULTS SHARE B&B[/TD]
[TD="class: xl114, align: center"]0[/TD]
[TD="class: xl115, align: center"]£32.00[/TD]
[TD="class: xl128, bgcolor: yellow, align: center"][/TD]
[TD="class: xl116, bgcolor: #F2F2F2, align: center"]£0.00[/TD]
[TD="class: xl104"]LOST KEYS[/TD]
[TD="class: xl76, bgcolor: #F2F2F2, align: center"]£20.00[/TD]
[TD="class: xl136, width: 71, bgcolor: black, align: center"]GRAND TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]34[/TD]
[TD="class: xl133, colspan: 2"]CHILDRN SHARE FULL[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl71, align: center"]£24.13[/TD]
[TD="class: xl125, bgcolor: yellow, align: center"][/TD]
[TD="class: xl117, bgcolor: #F2F2F2, align: center"]£0.00[/TD]
[TD="class: xl101, colspan: 2"]CHILDREN SHARE B&B[/TD]
[TD="class: xl65, align: center"]0[/TD]
[TD="class: xl71, align: center"]£16.00[/TD]
[TD="class: xl129, bgcolor: yellow, align: center"][/TD]
[TD="class: xl117, bgcolor: #F2F2F2, align: center"]£0.00[/TD]
[TD="class: xl105"]LATE FEES[/TD]
[TD="class: xl77, bgcolor: #F2F2F2, align: center"]£50.00[/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]35[/TD]
[TD="class: xl90, colspan: 2"]ADULTS SINGLE FULL[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl71, align: center"]£54.25[/TD]
[TD="class: xl126, align: center"]3[/TD]
[TD="class: xl117, bgcolor: #F2F2F2, align: center"]£162.75[/TD]
[TD="class: xl101, colspan: 2"]ADULTS SINGLE B&B[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl71, align: center"]£38.00[/TD]
[TD="class: xl130, align: center"]2[/TD]
[TD="class: xl117, bgcolor: #F2F2F2, align: center"]£76.00[/TD]
[TD="class: xl105"]MISC[/TD]
[TD="class: xl77, bgcolor: #F2F2F2, align: center"]£0.00[/TD]
[TD="class: xl138, width: 71, align: center"]£382.01[/TD]
[/TR]
[TR]
[TD="class: xl134, bgcolor: #F2F2F2, align: center"]36[/TD]
[TD="class: xl91, colspan: 2"]CHILDRN SNGLE FULL[/TD]
[TD="class: xl74, align: center"]1[/TD]
[TD="class: xl75, align: center"]£27.13[/TD]
[TD="class: xl127, align: center"]2[/TD]
[TD="class: xl102, bgcolor: #F2F2F2, align: center"]£54.26[/TD]
[TD="class: xl118, colspan: 2"]CHILDREN SINGLE B&B[/TD]
[TD="class: xl120, align: center"]1[/TD]
[TD="class: xl121, align: center"]£19.00[/TD]
[TD="class: xl131, align: center"]1[/TD]
[TD="class: xl122, bgcolor: #F2F2F2, align: center"]£19.00[/TD]
[TD="class: xl106"]OTHER[/TD]
[TD="class: xl78, bgcolor: #F2F2F2, align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl98, bgcolor: black, colspan: 2, align: right"]TOTAL FULL[/TD]
[TD="class: xl82, bgcolor: black, align: center"]4[/TD]
[TD="class: xl83, bgcolor: black"][/TD]
[TD="class: xl84, bgcolor: black, align: center"]5[/TD]
[TD="class: xl85, bgcolor: black, align: center"]£217.01[/TD]
[TD="class: xl107, bgcolor: black, colspan: 2, align: right"]TOTAL B&B[/TD]
[TD="class: xl109, bgcolor: black, align: center"]2[/TD]
[TD="class: xl110, bgcolor: black"][/TD]
[TD="class: xl111, bgcolor: black"][/TD]
[TD="class: xl103, bgcolor: black, align: center"]£95.00[/TD]
[TD="class: xl86, bgcolor: black, align: right"]TOTAL[/TD]
[TD="class: xl87, bgcolor: black, align: center"]£70.00[/TD]
[TD="class: xl81, width: 71, bgcolor: black"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]A[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]B[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]C[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]D[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]E[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]F[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]G[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]H[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]I[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]J[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]K[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]L[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]M[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]N[/TD]
[TD="class: xl135, bgcolor: #F2F2F2, align: center"]O[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 852"]
<tbody>[TR]
[TD="class: xl66, width: 852, colspan: 12"]Cell c33 is "=SUM(COUNTIFS(G7:G18,{"DBL","TWN","FOLDAWAY","SOFA BED"},E7:E18,{"ADULT"},F7:F18,{"FULL"}))"[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl65, colspan: 12"]Cell c35 is "=SUM(COUNTIFS(G7:G18,{"SGL"},E7:E18,{"ADULT"},F7:F18,{"FULL"}))"[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl66, colspan: 12"]Cell i33 is "=SUM(COUNTIFS(G7:G18,{"DBL","TWN","FOLDAWAY","SOFA BED"},E7:E18,{"ADULT"},F7:F18,{"B&B"}))"[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl65, colspan: 12"]Cell E35 is "=SUMIFS(L7:L18,E7:E18,"ADULT",F7:F18,"FULL",G7:G18,"SGL")"[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl65, colspan: 12"]Cell K36 is "=SUMIFS(L7:L18,E7:E18,"CHILD",F7:F18,"B&B",G7:G18,"SGL")"[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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!

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl67, width: 142, bgcolor: black, colspan: 2, align: center"]ROW G[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #FFC000, colspan: 2, align: center"]DBL[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #FFC000, colspan: 2, align: center"]TWN[/TD]
[/TR]
[TR]
[TD="class: xl66, colspan: 2, align: center"]SGL[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #FFC000, colspan: 2, align: center"]FOLDAWAY[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #FFC000, colspan: 2, align: center"]SOFA BED[/TD]
[/TR]
[TR]
[TD="class: xl66, colspan: 2, align: center"]COT (inf)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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