Forum Discussion

Misleiloee's avatar
Misleiloee
Copper Contributor
Jun 19, 2023

Function EDATE in SUMPRODUCT

Hello,   I want to have a SUMPRODUCT function with several matrice, and one of them is comparing 2 dates but i wanna add 1 month to one of them: =SUMPRODUCT(Range;([Actual end date range]<EDATE([P...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jun 19, 2023

Misleiloee 

=SUMPRODUCT(B4:D20*(EDATE(44850,1)<F4:F20))

Is this similar to what you are looking for?

 

  • Misleiloee's avatar
    Misleiloee
    Copper Contributor
    Jun 19, 2023
    Not really, as I'm trying to compare 2 dates while in your example I only see 1 date ?
    Also I've attached an example excel sheet so it's easier to understand what I'm looking for
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Jun 19, 2023

      Misleiloee 

      =SUMPRODUCT(('Raw Data'!$B$2:$B$116="Closed")*('Raw Data'!$G$2:$G$116="Critic")*('Raw Data'!$D$2:$D$116<'Raw Data'!$I$2:$I$116))

      I'd insert an additional column in sheet "Raw Data" for the EDATE, in the attached file it's column I. Then the above formula returns the intended result. Column A in sheet "Raw Data" isn't required since SUMPRODUCT counts the number of occurrences.

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jun 19, 2023

      Misleiloee 

      EDATE works with single cells only, not with a range of multiple cells.

      Workaround: add a column with EDATE formulas to the raw data.

      See the attached version.

Resources