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.
=SUMIFS($D$2:$D$16,$A$2:$A$16,A20,$B$2:$B$16,">="&B20,$C$2:$C$16,"<="&C20)
=SUMPRODUCT((A20=$A$2:$A$16)*(B20<=$B$2:$B$16)*(C20>=$C$2:$C$16)*$D$2:$D$16)
Maybe with these formulas as shown in the attached file.
OliverScheurich Hey! Thank you for the response - would you be able to break out that formula by which table you're referencing please and when, please?
Thank you!1
As variant
=SUMIFS( Table2[[SUM]:[SUM]], Table2[[Name]:[Name]], Table1[@[Name]:[Name]], Table2[[Start Date]:[Start Date]], "<=" &Table1[[#Headers],[2022-01-01]], Table2[[End Date]:[End Date]], ">=" & Table1[[#Headers],[2022-01-01]] )and drag it to other columns
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.
You're welcome. Can you upload your two tables with non-confidential data so I can see which column the data is in. Unfortunately the two tables in your first post are too small to see enough.