Spreadsheet: 156039d1337158794-sumif-adding-associated-values-new-take-off-sheet-test-annotated
Sheet: Take-Off

 | Project | xxx
 | CRM | 2440
 | Equipment |  |  |  |  | System_Integration | Ballast_Controllers | Sensors | Relay_Controllers | Leading_Trailing_Edge_Controllers | DR2PE_Scene_Plates | DPNE_Scene_Plates | DPWE_Scene_Plates | Timeclocks
 | Drawing Number | Revision | Level |  |  | DNG100BT | DDBC1200-MO | DUS804C | DDRC1220FR-GL | DLE405-NB | DR2PE AXX-X-S-SS-P-BO-X-S | DPNE981DN-SF | DPWE970-WE-SS-ENG | DDTC001
 | xxxxx | xx | Basement |  |  | 1 |  |  | 2
 | xxxxx | xx | Ground Floor |  |  | 1 | 3 | 52 | 3 | 1 | 5 | 2 | 6 | 1
 | xxxxx | xx | 1st Floor |  |  |  |  |  |  |  |  | 6
 | xxxxx | xx | 2nd Floor |  |  |  | 2 | 3 | 3 |  | 4 |  | 7 | 5
 | xxxxx | xx | 3rd Floor |  |  |  |  |  |  | 3 |  | 6
 | xxxxx | xx | 4th Floor |  |  | 1
 | xxxxx | xx | 5th Floor |  |  |  |  |  |  |  | 56 |  | 5 |  |  |  |  | You will notice that the levels on both tables are the same.   The second table is just an extension of the first
 | xxxxx | xx | 6th Floor |  |  |  |  |  |  |  |  |  |  |  |  |  |  | This is because I wanted to view everything without having to scroll acorss.
 | xxxxx | xx | 7th Floor
 | Totals |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I want to flag up a level that goes below 80 (in the P column), but both levels in both tables should be flagged
 | Need More Power |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | as they are the same level
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Im using the following formula in conditional formatting:   =$P5<80....obviously this isn't working properly
 | Equipment |  |  |  |  | Touchscreens | Timeclocks | Sensors | System_Integration | Network_Acessories | Ballast_Controllers | Remote_Controls | Fixture_Couplers | Network_Acessories
 | Drawing Number | Revision | Level |  |  | DTP100-BG | DDTC001 | DUS704W | DMNG232 | DTK622-USB-J | DDBC320-DALI | DTK510 | DMRC210 | DDNP1501 |  |  |  | The arrows to the left show that the top table is not refelcting the bottom as three of the rows are
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | above 80 and should be grey
 |  |  |  |  |  | 6 |  | 10 |  | 1
 |  |  |  |  |  |  | 4 | 6 | 3
 |  |  |  |  |  |  |  |  |  |  | 6 | 4 | 6 | 1
 |  |  |  |  |  | 6 |  | 5 |  | 3
 |  |  |  |  |  |  | 3 |  | 6 |  | 4 | 56
 |  |  |  |  |  |  |  |  |  |  |  |  | 2
 | Totals
 |  | Days |  | Product Code | Part Number | Quantity | Ind. Com | Total Commisioin | Trade Price | Total Price | Please add the following Equipment
 | Configuration & Programming | 0
 | Separate Training | 0
 | Applications | 1
 | Other Engineering | 0
 | [NAME] (Y/N)
 | [NAME] (Y/N)
 | DALI emergency feedback (Y/N)
 | Dynalite non-DALI commissioning
 | Dynalite DALI commissioning
 | Total Days
 | Total Cost
 | Total Sell
 | DALI calculator
 | No. of ballasts
 | No. of universes
 | Configuration Cost / Day | 270
 | Configuration Sell / Day | 500 |  | Total Equipment Sell Price
 | Out of hours (type no. of days) | 0 |  | Total Inc Commissioning
 |  |  |  | Inc VAT
 |  |  |  |  | Please confirm if my understanding is correct:
 |  |  |  |  | You want to sum any values in the "Data" tab, Column G and show the totals in the "Take-off" tab, cells G5:O13 and G19:O27, based on the changing Levels and Equipment.
 |  |  |  |  | If this is correct, I have two questions: (1) Which column on the "Data" tab describes the Equipment? (2) Which column on the "Data" tab describes the Level?
 |  |  |  |  | If you can answer these questions for me, I can give you a formula.
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME]: |  |  | sub total
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | No of ballasts
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | No universes
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Total | No of days
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Commissioning costs
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Baseline Commissioning costs
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | The greater of the two total costs/days should
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | be charged
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Non-Dali |  | Value | Days
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Total commisioning days
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Total Value ex. DALI |  | 0
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Greater should be
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | charged