Forum Discussion
Solved
Formula to Return a Sum Between Dates when Date Matches Column Header and Name Matches
Hello! I'm trying to build an interactive connect between two tables for the end goal of project management and deconfliction. Below are the examples of my tables: Table 1 - Project Management Ov...
That could be like
=SUM( MMULT( --(Table578[[PM]:[CM5]] = Table10[@[Name]:[Name]] ), SEQUENCE( COLUMNS( Table578[[PM]:[CM5]] ), , 1,0) ) * Table578[[SUM]:[SUM]] * ( Table578[[Start Date]:[Start Date]] <= DATEVALUE(Table10[[#Headers],[2022-01-01]] ) ) * ( Table578[[End Date]:[End Date]] >= DATEVALUE(Table10[[#Headers],[2022-01-01]] ) ) )I renamed first two columns to use DATEVALUE() in my regional format, most probably is not needed in your case.
I tried this one but it gave me a Spill error 😞 Here are my two tables with the sensitive data removed. I do have the two tables in the same sheet - maybe that's causing issues?
(Table name is Table 578)
| Name | Start Date | End Date | Size | Size Rating | Complexity | Complexity Rating | Management | Management Rating | SUM | PM | Asst PM | CM1 | CM2 | CM3 | CM4 | CM5 |
| Project 1 | 3/1/2020 | 1/1/2022 | Small | 1.0 | Average | 2.0 | Easy | 0.50 | 3.50 | PM1 | CM1 | CM6 | ||||
| Project 2 | 7/1/2020 | 1/1/2022 | Small | 1.0 | Average | 2.0 | Average | 1.00 | 4.00 | PM1 | CM2 | |||||
| Project 3 | 4/1/2021 | 3/1/2022 | Small | 1.0 | Average | 2.0 | Very Easy | 0.25 | 3.25 | PM2 | CM1 | |||||
| Project 4 | 9/1/2019 | 4/1/2022 | Large | 3.5 | High | 3.5 | Very Hard | 2.00 | 9.00 | APM/CM1 | CM2 | CM6 | ||||
| Project 5 | 5/1/2019 | 6/1/2022 | Very Large | 5.0 | Extreme | 5.0 | Very Hard | 2.00 | 12.00 | PM3 | APM/CM2 | CM2 | CM1 | CM3 | ||
| Project 6 | 10/1/2021 | 9/1/2022 | Medium | 2.0 | Average | 2.0 | Very Easy | 0.25 | 4.25 | PM4 | CM1 | |||||
| Project 7 | 10/1/2021 | 9/1/2022 | Small | 1.0 | Low | 1.0 | Very Easy | 0.25 | 2.25 | |||||||
| Project 8 | 11/23/2021 | 12/1/2022 | Medium | 2.0 | Low | 1.0 | Easy | 0.50 | 3.50 | PM5 | CM3 | |||||
| Project 9 | 10/1/2020 | 9/1/2023 | Very Large | 5.0 | Extreme | 5.0 | Very Hard | 2.00 | 12.00 | PM3 | APM/CM3 | CM3 | CM1 | CM2 | ||
| Project 10 | 12/23/2022 | 9/28/2023 | Large | 3.5 | Average | 2.0 | Average | 1.00 | 6.50 | PM3 | APM/CM4 | CM3 | CM1 | CM6 | ||
| Project 11 | 2/1/2022 | 11/1/2023 | Small | 1.0 | Low | 1.0 | Very Easy | 0.25 | 2.25 | PM5 | CM4 | |||||
| Project 12 | 7/1/2022 | 12/1/2023 | Small | 1.0 | Low | 1.0 | Very Easy | 0.25 | 2.25 | PM5 | CM1 | |||||
| Project 13 | 7/1/2022 | 12/1/2023 | Small | 1.0 | Average | 2.0 | Very Easy | 0.25 | 3.25 | PM6 | CM4 | |||||
| Project 14 | 2/21/2023 | 6/20/2024 | Large | 3.5 | Average | 2.0 | Average | 1.00 | 6.50 | PM6 | CM4 | |||||
| Project 15 | 6/21/2023 | 6/23/2024 | Small | 1.0 | High | 3.5 | Average | 1.00 | 5.50 | PM7 | CM1 | |||||
| Project 16 | 2/1/2023 | 11/1/2024 | Small | 1.0 | Low | 1.0 | Very Easy | 0.25 | 2.25 | PM7 | CM2 | |||||
| Project 17 | 5/17/2023 | 11/16/2024 | Medium | 2.0 | Average | 2.0 | Very Easy | 0.25 | 4.25 | PM3 | CM2 | |||||
| Project 18 | 6/7/2024 | 12/6/2025 | Medium | 2.0 | Average | 2.0 | Very Easy | 0.25 | 4.25 | PM7 | CM1 | |||||
| Project 19 | 6/7/2024 | 1/6/2026 | Very Large | 5.0 | Average | 2.0 | Hard | 2.00 | 9.00 | PM7 | CM5 | CM3 | ||||
| Project 20 | 11/17/2023 | 4/18/2026 | Medium | 2.0 | Low | 1.0 | Average | 1.00 | 4.00 | PM6 | CM5 | CM2 | ||||
| Project 21 | 7/16/2024 | 3/7/2026 | Medium | 2.0 | Extreme | 5.0 | Hard | 2.00 | 9.00 | PM7 | CM1 | |||||
| Project 22 | 5/17/2025 | 4/2/2026 | Small | 1.0 | Average | 2.0 | Average | 1.00 | 4.00 | PM2 | CM1 | |||||
| Project 23 | 1/17/2024 | 3/2/2026 | Medium | 2.0 | High | 3.5 | Hard | 2.00 | 7.50 | PM7 | CM5 | CM1 | CM1 | |||
| Project 24 | 5/17/2024 | 5/16/2026 | Medium | 2.0 | Average | 2.0 | Very Easy | 0.25 | 4.25 | PM5 | CM5 | |||||
| Project 25 | 10/7/2024 | 6/5/2026 | Large | 3.5 | Extreme | 5.0 | Hard | 2.00 | 10.50 | PM4 | CM4 | CM1 | ||||
| Project 26 | 4/6/2025 | 6/5/2026 | Small | 1.0 | Average | 2.0 | Hard | 2.00 | 5.00 | PM7 | CM2 | |||||
| Project 27 | 11/17/2023 | 6/16/2026 | Medium | 2.0 | High | 3.5 | Easy | 0.50 | 6.00 | PM5 | CM2 | CM1 | ||||
| Project 28 | 5/17/2025 | 6/30/2026 | Small | 1.0 | Average | 2.0 | Easy | 0.50 | 3.50 | PM7 | CM1 | |||||
| Project 29 | 1/1/2023 | 7/1/2026 | Medium | 2.0 | Average | 2.0 | Easy | 0.50 | 4.50 | PM7 | CM5 | |||||
| Project 30 | 9/1/2022 | 7/1/2026 | Very Large | 5.0 | Extreme | 5.0 | Very Hard | 2.00 | 12.00 | PM6 | APM/CM5 | CM6 | CM2 | CM3 | CM1 | |
| Project 31 | 1/1/2025 | 7/1/2026 | Medium | 2.0 | Average | 2.0 | Easy | 0.50 | 4.50 | PM7 | CM4 | |||||
| Project 32 | 5/17/2024 | 7/17/2026 | Large | 3.5 | Extreme | 5.0 | Hard | 2.00 | 10.50 | PM2 | CM1 | CM2 | CM3 | |||
| Project 33 | 2/4/2025 | 10/5/2026 | Large | 3.5 | Extreme | 5.0 | Hard | 2.00 | 10.50 | PM5 | CM3 | |||||
| Project 34 | 5/17/2024 | 10/16/2026 | Large | 3.5 | High | 3.5 | Hard | 2.00 | 9.00 | PM7 | CM1 | CM2 | CM3 | |||
| Project 35 | 12/17/2024 | 10/16/2026 | Medium | 2.0 | High | 3.5 | Hard | 2.00 | 7.50 | PM3 | CM2 | CM1 | ||||
| Project 36 | 5/17/2025 | 1/16/2027 | Large | 3.5 | High | 3.5 | Very Hard | 2.00 | 9.00 | PM7 | CM1 | CM3 | ||||
| Project 37 | 10/6/2025 | 1/20/2027 | Large | 3.5 | High | 3.5 | Hard | 2.00 | 9.00 | PM3 | CM1 | |||||
| Project 38 | 2/1/2025 | 2/1/2027 | Large | 3.5 | High | 3.5 | Hard | 2.00 | 9.00 | PM2 | CM3 | |||||
| Project 39 | 4/6/2025 | 4/20/2027 | Large | 3.5 | High | 3.5 | Easy | 0.50 | 7.50 | PM3 | APM/CM2 | CM6 | ||||
| Project 40 | 1/6/2026 | 4/20/2027 | Medium | 2.0 | Average | 2.0 | Hard | 2.00 | 6.00 | PM7 | CM6 |
(Table Name is Table10)
| Name | Position | 1/1/2022 | 4/1/2022 | 7/1/2022 | 10/1/2022 | 1/1/2023 | 4/1/2023 | 7/1/2023 | 10/1/2023 | 1/1/2024 | 4/1/2024 | 7/1/2024 | 10/1/2024 | 1/1/2025 | 4/1/2025 | 7/1/2025 | 10/1/2025 | 1/1/2026 | 4/1/2026 | 7/1/2026 | 10/1/2026 | 1/1/2027 | 4/1/2027 | 7/1/2027 | 10/1/2027 | 1/1/2028 | 4/1/2028 | 7/1/2028 | 10/1/2028 | 1/1/2029 | 4/1/2029 | 7/1/2029 | 10/1/2029 | 1/1/2030 | 4/1/2030 | 7/1/2030 | 10/1/2030 |
| CM1 | CM | ||||||||||||||||||||||||||||||||||||
| CM2 | CM | ||||||||||||||||||||||||||||||||||||
| CM3 | CM | ||||||||||||||||||||||||||||||||||||
| CM4 | CM | ||||||||||||||||||||||||||||||||||||
| CM5 | CM | ||||||||||||||||||||||||||||||||||||
| CM6 | CM | ||||||||||||||||||||||||||||||||||||
| PM1 | PM | ||||||||||||||||||||||||||||||||||||
| PM2 | PM | ||||||||||||||||||||||||||||||||||||
| PM3 | PM | ||||||||||||||||||||||||||||||||||||
| PM4 | PM | ||||||||||||||||||||||||||||||||||||
| PM5 | PM | ||||||||||||||||||||||||||||||||||||
| PM6 | PM | ||||||||||||||||||||||||||||||||||||
| PM7 | PM | ||||||||||||||||||||||||||||||||||||
| APM1 | APM/CM | ||||||||||||||||||||||||||||||||||||
| APM2 | APM/CM | ||||||||||||||||||||||||||||||||||||
| APM3 | APM/CM | ||||||||||||||||||||||||||||||||||||
| APM4 | APM/CM |
Sorry, here is the table in an attachment as well.
Please check in attached. No errors but no result as well since column Name in one table has nothing common with column Name in another table. It's not clear what shall match with what.
- So the names referencing should be:
Column "Name" in Table 10 and Columns "PM, Asst PM, CM1, CM2, CM3, CM4, and CM5" in Table 578
Do I need to unpivot the table maybe? I'd prefer not to if at all possibleThat could be like
=SUM( MMULT( --(Table578[[PM]:[CM5]] = Table10[@[Name]:[Name]] ), SEQUENCE( COLUMNS( Table578[[PM]:[CM5]] ), , 1,0) ) * Table578[[SUM]:[SUM]] * ( Table578[[Start Date]:[Start Date]] <= DATEVALUE(Table10[[#Headers],[2022-01-01]] ) ) * ( Table578[[End Date]:[End Date]] >= DATEVALUE(Table10[[#Headers],[2022-01-01]] ) ) )I renamed first two columns to use DATEVALUE() in my regional format, most probably is not needed in your case.