Greetings,
I am somewhat lost how to approach this problem that I have. In essence I want to calculate demands per material for specific date based on the number of projects on that date. for this I have 3 tables
Project Consumption Table (showing how much of each material is consumed for each project):
Please note that new projects and material will be added in the future and the table will update accordingly.
Production Plan Table (shows how many times are the projects being produced on a specific date):
This table shows how many times a specific project will be produced on that day. As with the project consumption table it is updated with new projects.
Material Demands table is where I need calculation help (Total material demands on specific date):
I would love to have an elegant solution without summing different lookups (eg. Index match match + index match match) as in reality there are multiple dozens of different projects and hundreds of materials, so preferably adding new projects and materials to tables would not "break" the formula. I believe it should be possible to achieve this with a clever use of SUMPRODUCT, but I have rarely used it, so I don't know how to approach it.
If you need any additional info I'd be glad to provide it.
Thanks so much for your help!
Mazi
I am somewhat lost how to approach this problem that I have. In essence I want to calculate demands per material for specific date based on the number of projects on that date. for this I have 3 tables
Project Consumption Table (showing how much of each material is consumed for each project):
Material | Project A | Project B |
Material 1 | 10 | 5 |
Material 2 | 3 | 1 |
Production Plan Table (shows how many times are the projects being produced on a specific date):
Project | 1.1.2023 | 1.2.2023 | 1.3.2023 |
Project A | 1 | 2 | 3 |
Project B | 2 | 0 | 3 |
Material Demands table is where I need calculation help (Total material demands on specific date):
Material | 1.1.2023 | 1.2.2023 | 1.3.2023 |
Material 1 | Formula? Result would be 20 | 20 | 45 |
Material 2 | 5 | 6 | 12 |
If you need any additional info I'd be glad to provide it.
Thanks so much for your help!
Mazi