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.
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 possible
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 possible
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.
You may add one more multiplier
I added to the formula in the attached file, but didn't test since need for that rename columns making dates as in my regional format. Hope works.
Modification request for you! Would it be possible to add a zone matching filter to the formula? I've attached a sample dataset to look at, but the goal is to modify the formula where if the zone matches in table 1 and table 2, then only calculate that score.
keltzjd , you are welcome
- It works perfectly, thank you so much!!