Spreadsheet: 672040d1586700410-a-proper-formula-to-calculate-depending-several-criteria-eg6-zrs-
Sheet: Sayfa1

 | YELLOW ROWS are with Data Validation
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Disc. Children
 | Room Type | Standard Land View |  | From | 2020-05-28 00:00:00 | To | 2020-06-05 00:00:00 | Nights |  | Board | BO | Adult # | 3 |  | Chd-1 (y.o) | 8 |  | Chd-2 (y.o) | 11 |  | Chd-3 (y.o) |  | Chd-4 (y.o) |  |  | SUM
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | BB | HB | FB | AI
 |  |  |  |  |  |  |  |  |  | From |  | To |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Board | PP | 1 Adt | 2 Adt | 3 Adt | 4 Adt | BB Supp | HB [NAME] | FB Supp | AI Supp | 1st Child Age |  | 2nd Child Age |  | 1st Child Age |  |  | 2nd Child Age |  |  |  |  |  | 28-29-30-31 (4 Nights)
 | Rates are on | [NAME] |  | Base Board | BB |  |  | Period #: |  | 2020-05-01 00:00:00 | - | 2020-05-31 00:00:00
 | Room Type | Min & Max Accommodations |  |  | Price/PP | [NAME] (Room Rates) |  |  |  | PP Meal Supplements |  |  | Cumulative | Free Child Ages |  |  |  |  |  | Discounted Child Ages |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Disc.
 |  | Min Adt | [NAME] | [NAME] |  | 1 Adt | 2 Adt | 3 Adt | 4 Adt | BB Supp | HB [NAME] | FB Supp | AI Supp | 1st Child Age |  |  | 2nd Child Age |  |  | 1st Child Age |  |  | -% (PP) | 2nd Child Age |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | days | Adults | Children | Board
 | Standard Land View | 1 | 4 | 5 | 10 | 15 | 20 | 27 | 32 | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Should be 30 because 1st Disc. Chd 10*50% + 2nd Disc. Chd 10*50%= 10 * 3 Nights
 | Std. Pool View | 1 | 3 | 3 | 15 | 22.5 | 30 | 40.5 |  | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Side Sea View | 1 | 3 | 4 | 20 | 30 | 40 | 54 |  | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | My experimental formula here affects the calculation
 | Std. Sea View | 1 | 4 | 4 | 25 | 50 | 50 | 67.5 | 80 | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | based on the selection on AW5 (dynamic use as [NAME] or Discount Percentage from X8)
 |  |  |  |  |  | 0 | 0 | 0 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | X8 & AB8 are always with same text but still have problem how to calculate
 |  |  |  |  |  | 0 | 0 | 0 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | if discounted children amount is more than 2 (up to 4)
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 | FEATURES |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 01-02-03-04 (4 Nights) 05 is check-out day not considered in calculation
 | Rates are on | [NAME] |  | Base Board | BO |  |  | Period #: |  | 2020-06-01 00:00:00 | - | 2020-06-20 00:00:00
 | Room Type | Min & Max Accommodations |  |  | Price/PP | [NAME] (Room Rates) |  |  |  | PP Meal Supplements |  |  | Cumulative | Free Child Ages |  |  |  |  |  | Discounted Child Ages
 |  | Min Adt | [NAME] | [NAME] |  | 1 Adt | 2 Adt | 3 Adt | 4 Adt | BB Supp | HB [NAME] | FB Supp | AI Supp | 1st Child Age |  |  | 2nd Child Age |  |  | 1st Child Age |  |  | -% (PP) | 2nd Child Age
 | Standard Land View | 1 | 4 | 5 | 15 | 22.5 | 30 | 40.5 | 48 | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Pool View | 1 | 3 | 3 | 20 | 30 | 40 | 54 | 0 | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Side Sea View | 1 | 3 | 4 | 25 | 37.5 | 50 | 67.5 | 0 | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 | FEATURES
 | Rates are on | [NAME] |  | Base Board | BO |  |  | Period #: |  | 2020-06-21 00:00:00 | - | 2020-07-07 00:00:00
 | Room Type | Min & Max Accommodations |  |  | Price/PP | [NAME] (Room Rates) |  |  |  | PP Meal Supplements |  |  | Cumulative | Free Child Ages |  |  |  |  |  | Discounted Child Ages
 |  | Min Adt | [NAME] | [NAME] |  | 1 Adt | 2 Adt | 3 Adt | 4 Adt | BB Supp | HB [NAME] | FB Supp | AI Supp | 1st Child Age |  |  | 2nd Child Age |  |  | 1st Child Age |  |  | -% (PP) | 2nd Child Age
 | Standard Land View | 1 | 4 | 5 | 20 | 30 | 40 | 54 | 64 | 3 | 6 | 12 | 20 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Pool View | 1 | 3 | 3 | 25 | 37.5 | 50 | 67.5 | 0 | 3 | 6 | 12 | 20 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Side Sea View | 1 | 3 | 4 | 30 | 45 | 60 | 81 | 0 | 3 | 6 | 12 | 20 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Sea View | 1 | 4 | 4 | 35 | 70 | 70 | 94.5 | 112 | 3 | 6 | 12 | 20 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Garden View | 1 | 3 | 3 | 25 | 37.5 | 50 | 67.5 | 0 | 3 | 6 | 12 | 20 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 | FEATURES