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...

Charting

excel

Formulas and Functions

office 365

SergeiBaklan

Feb 07, 2022

keltzjd

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.

UploadTables.xlsx23 KB

keltzjd

Brass Contributor

Feb 09, 2022

SergeiBaklan

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.

TableUploadZone.xlsx19 KB

SergeiBaklan

Diamond Contributor

Feb 10, 2022

keltzjd

You may add one more multiplier