Spreadsheet: 778703d1651233876-sum-with-conditions-work-out-cost-of-building-construction-based-on-prerequisites-help-ans
Sheet: Sheet1

BUILDING DETAILS |  |  |  |  |  | How i would like excel to work
BUILDING Type | BUILDING LEVEL | prerequisites | cost |  | BUILDING TYPE | Current Level of building | target building | cost
Appartment | Appartment Stage1 |  | 100 |  | Appartment | Appartment Stage2 | Appartment Stage3 |  | Stage 1 of appartment is already completed. You know that because in column G the current level given is stage 2.  So only the cells D4 and D5 were added
Appartment | Appartment Stage2 | Appartment Stage1 | 200 |  | Bakery | Bakery Stage 1 | Bakery Stage 3 |  | Apartement stages are a requirement for all buildings. Cell G3 tells us that apartment is already completed at stage 2. So in order to go from bakery stage 1 to stage 3 i need to include the cost of construction for the apartment but only for stage 3 because stage 2 is already completed (1st set of cells in formula brackets) + the cost of bakery at all stages (second set of cells in formula brackets)
Appartment | Appartment Stage3 | Appartment Stage2 | 300 |  | Classroom | Classroom Stage1 | Classroom Stage3 |  | Cell G5 tell me classroom stage one is completed. So to build classroom stage 2 i only need to complete Bakery stage 2 (D7) because bakery stage 1 is completed (see cell G4) + Classroom stage 2 and 3  (D10+D11)
Bakery | Bakery Stage 1 | Appartment Stage1 | 100 |  | Dome |  | Dome stage3 |  | Cell G6 tells me Dome has not been started. So it needs to be built from stage 1 included.  - Apartment stage 3 (D5) because it is a requirement for Dome stage 3 (see [NAME]) - Classroom stage 2 and 3 (D10+D11) because G5 tells us classroom is completed to stage 1 only - Bakery stage 2 (D7) because Bakery stage 1 and 2 are a requirements for Classroom 2 and 3 (see cells C10 and C11) but cell G4 tells us bakery stage 1 is already completed so no need to include cost of bakery stage 1 - Dome stage 1, 2 and 3 (D12+D13+D14)
Bakery | Bakery Stage 2 | Appartment Stage2 and Bakery Stage 1 | 200
Bakery | Bakery Stage 3 | Appartment Stage3 and Bakery stage 2 | 300
Classroom | Classroom Stage1 | Appartment Stage2 and classroom stage 1 | 100 |  |  |  | Correct TOTAL |  | I wish to know the total cost of building the targeted building.  If i do a simple sum like i did in cell i10 some of the costs will be duplicated: for example the appartment requirements D5 will be counted multiple times when it really only needs to be counted once The only building that is not required for the construction of selected buildings (column H) is bakery stage 3 which is not a target and also not a requirement to other buildings.
Classroom | Classroom Stage2 | Appartment Stage2 and classroom stage 1 and bakery stage 1 | 200 |  |  |  | wrong total
Classroom | Classroom Stage3 | Appartment Stage3 and classroom stage 2 and bakery stage 2 | 300
Dome | Dome stage1 | Appartment Stage1 and classroom stage 2 | 100
Dome | Dome stage2 | Appartment Stage2 and Dome stage 1 | 200
Dome | Dome stage3 | Appartment Stage3 and dome stage 2 and classroom stage 3 | 300
Appartment Stage3 | 300
Bakery Stage 2 & 3 | 500
Classroom Stage 2 & 3 | 500
Dome stage 1 &2 & 3 | 600