Forum Discussion

keltzjd's avatar
keltzjd
Brass Contributor
Feb 04, 2022
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...
Charting
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 06, 2022

keltzjd 

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.

  • keltzjd's avatar
    keltzjd
    Brass Contributor
    Feb 06, 2022
    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
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      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.

      • keltzjd's avatar
        keltzjd
        Brass Contributor
        Feb 07, 2022
        It works perfectly, thank you so much!!