Forum Discussion
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 Overview:
This is the table where I need the formula to automatically sum the values in my other table:
Table 2 - Project Modifications:
My criteria are:
Sum the values from Table 2 SUM column IF the cell (in table 1 header) falls in on or between the start date and completion date (table 2) IF the Name from the end columns at the end of Table 2 match the Name it's assigned to in Table 1.
I tried using a SUMIFS formula, but I got a spill error and I'm not even sure that's the appropriate formula for achieving this.
Your help would be MOST appreciated! Thank you!
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.
13 Replies
=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
