Mutliple conditions in table & looking up cost thereon

[sleek12]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]single[/TD]
[TD]twin[/TD]
[TD]triple[/TD]
[TD]single[/TD]
[TD]twin[/TD]
[TD]triple[/TD]
[TD]single[/TD]
[TD]twin[/TD]
[TD]triple[/TD]
[/TR]
[TR]
[TD]view[/TD]
[TD]normal[/TD]
[TD]normal[/TD]
[TD]normal[/TD]
[TD]best[/TD]
[TD]best[/TD]
[TD]best[/TD]
[TD]VIP[/TD]
[TD]VIP[/TD]
[TD]VIP[/TD]
[/TR]
[TR]
[TD]11/04/2017[/TD]
[TD]95[/TD]
[TD]190[/TD]
[TD]261[/TD]
[TD]130[/TD]
[TD]225[/TD]
[TD]296[/TD]
[TD]145[/TD]
[TD]240[/TD]
[TD]311[/TD]
[/TR]
[TR]
[TD]12/04/2017[/TD]
[TD]145[/TD]
[TD]240[/TD]
[TD]311[/TD]
[TD]180[/TD]
[TD]275[/TD]
[TD]346[/TD]
[TD]195[/TD]
[TD]250[/TD]
[TD]361[/TD]
[/TR]
[TR]
[TD]13/04/2017[/TD]
[TD]145[/TD]
[TD]240[/TD]
[TD]311[/TD]
[TD]180[/TD]
[TD]275[/TD]
[TD]346[/TD]
[TD]195[/TD]
[TD]250[/TD]
[TD]361[/TD]
[/TR]
[TR]
[TD]14/04/2017[/TD]
[TD]95[/TD]
[TD]190[/TD]
[TD]261[/TD]
[TD]130[/TD]
[TD]225[/TD]
[TD]296[/TD]
[TD]145[/TD]
[TD]240[/TD]
[TD]311[/TD]
[/TR]
</tbody>[/TABLE]

Objective:-
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]from[/TD]
[TD]to[/TD]
[TD]no of days[/TD]
[TD]no of pax[/TD]
[TD]room type[/TD]
[TD]additional[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]11/04/2017[/TD]
[TD]12/04/2017[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]single[/TD]
[TD]VIP[/TD]
[TD]1700[/TD]
[/TR]
[TR]
[TD]13/04/2017[/TD]
[TD]14/04/2017[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]triple[/TD]
[TD]normal[/TD]
[TD]1716[/TD]
[/TR]
</tbody>[/TABLE]
I want to get the cost using formula or macro even,for room type & addtional i can do the drop down lists from data validation

1700=(145x5)+(195x5),1716=(311x3)+(261x3)

Any help would be appreciated !!!

 

[DRSteele]

I think this oughta do it.

Copy C10 and G10 as necessary.

	A	B	C	D	E	F	G	H	I	J	K
single	VIP
triple	normal
twin	best

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]bed:[/TD]
[TD="bgcolor: #FFF2CC"]single[/TD]
[TD="bgcolor: #FFF2CC"]twin[/TD]
[TD="bgcolor: #FFF2CC"]triple[/TD]
[TD="bgcolor: #FFF2CC"]single[/TD]
[TD="bgcolor: #FFF2CC"]twin[/TD]
[TD="bgcolor: #FFF2CC"]triple[/TD]
[TD="bgcolor: #FFF2CC"]single[/TD]
[TD="bgcolor: #FFF2CC"]twin[/TD]
[TD="bgcolor: #FFF2CC"]triple[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFF2CC"]Date[/TD]
[TD="bgcolor: #FFF2CC"]view:[/TD]
[TD="bgcolor: #FFF2CC"]normal[/TD]
[TD="bgcolor: #FFF2CC"]normal[/TD]
[TD="bgcolor: #FFF2CC"]normal[/TD]
[TD="bgcolor: #FFF2CC"]best[/TD]
[TD="bgcolor: #FFF2CC"]best[/TD]
[TD="bgcolor: #FFF2CC"]best[/TD]
[TD="bgcolor: #FFF2CC"]VIP[/TD]
[TD="bgcolor: #FFF2CC"]VIP[/TD]
[TD="bgcolor: #FFF2CC"]VIP[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Apr 11, 2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]261[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]296[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]311[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Apr 12, 2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]311[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]346[/TD]
[TD="align: right"]195[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]361[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]Apr 13, 2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]311[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]346[/TD]
[TD="align: right"]195[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]361[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Apr 14, 2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]261[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]296[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]311[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FCE4D6"]from[/TD]
[TD="bgcolor: #FCE4D6"]to[/TD]
[TD="bgcolor: #FCE4D6"]no of days[/TD]
[TD="bgcolor: #FCE4D6"]no of pax[/TD]
[TD="bgcolor: #FCE4D6"]room type[/TD]
[TD="bgcolor: #FCE4D6"]additional[/TD]
[TD="bgcolor: #FCE4D6"]Cost[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]Apr 11, 2017[/TD]
[TD="align: right"]Apr 12, 2017[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #E2EFDA, align: right"]1700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]Apr 13, 2017[/TD]
[TD="align: right"]Apr 14, 2017[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #E2EFDA, align: right"]1716[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]Apr 12, 2017[/TD]
[TD="align: right"]Apr 14, 2017[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #E2EFDA, align: right"]775[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet54​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C10[/TH]
[TD="align: left"]=B10-A10+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G10[/TH]
[TD="align: left"]{=SUM(IFNA(INDEX($C$3:$K$6,N(IF(1,MATCH((($A$3:$A$6>=A10)*($A$3:$A$6<=B10))*$A$3:$A$6,$A$3:$A$6,0))),MATCH(E10&F10,$C$1:$K$1&$C$2:$K$2,0)),0))*D10}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]

 

[sleek12]

Thank you for your help,
Let me try it right away !!
The computation of no. of days was not an issue, but the cost which is varying over easter & xmas & other holidays was the main problem.

 

[sleek12]

Can the formulae be modified & be put into another worksheet ? In the above example it is in the same worksheet !!
Mind it , it works very well !!