Forum Discussion

Brass Contributor

May 21, 2022

Solved

SUM IF start date and end date are found in a particular month

Hello, here is a tricky one does anyone have an solution for this formula? I attach the excel file bellow here is how should work: I will insert in table1 the Name, the Start date and the End date...

Book112.xlsx15 KB

excel

Formulas and Functions

HansVogelaar

May 21, 2022

Alecs

In I3:

=MIN(SUMPRODUCT(($A$3:$A$37=$H3)*IF(IF($C$3:$C$37<=EOMONTH(I$2,0),$C$3:$C$37,EOMONTH(I$2,0))-IF($B$3:$B$37>=I$2,$B$3:$B$37,I$2)>=0,IF($C$3:$C$37<=EOMONTH(I$2,0),$C$3:$C$37,EOMONTH(I$2,0))-IF($B$3:$B$37>=I$2,$B$3:$B$37,I$2)+1,0)),EOMONTH(I$2,0)-I$2+1)

If you have Microsoft 365 or Office 2021, the following is more readable:

=LET(
    Names,$A$3:$A$37,
    TheName,$H3,
    StartDate,$B$3:$B$37,
    EndDate,$C$3:$C$37,
    StartMonth,I$2,
    EndMonth,EOMONTH(StartMonth,0),
    DaysInMonth,EndMonth-StartMonth+1,
    ActualStart,IF(StartDate>=StartMonth,StartDate,StartMonth),
    ActualEnd,IF(EndDate<=EndMonth,EndDate,EndMonth),
    Duration,ActualEnd-ActualStart,
    ActualDuration,IF(Duration>=0,Duration+1,0),
    Total,SUMPRODUCT((Names=TheName)*ActualDuration),
    MIN(Total,DaysInMonth)
)

HansVogelaar

MVP

May 21, 2022

Alecs

In I3:

=MIN(SUMPRODUCT(($A$3:$A$37=$H3)*IF(IF($C$3:$C$37<=EOMONTH(I$2,0),$C$3:$C$37,EOMONTH(I$2,0))-IF($B$3:$B$37>=I$2,$B$3:$B$37,I$2)>=0,IF($C$3:$C$37<=EOMONTH(I$2,0),$C$3:$C$37,EOMONTH(I$2,0))-IF($B$3:$B$37>=I$2,$B$3:$B$37,I$2)+1,0)),EOMONTH(I$2,0)-I$2+1)

If you have Microsoft 365 or Office 2021, the following is more readable:

=LET(
    Names,$A$3:$A$37,
    TheName,$H3,
    StartDate,$B$3:$B$37,
    EndDate,$C$3:$C$37,
    StartMonth,I$2,
    EndMonth,EOMONTH(StartMonth,0),
    DaysInMonth,EndMonth-StartMonth+1,
    ActualStart,IF(StartDate>=StartMonth,StartDate,StartMonth),
    ActualEnd,IF(EndDate<=EndMonth,EndDate,EndMonth),
    Duration,ActualEnd-ActualStart,
    ActualDuration,IF(Duration>=0,Duration+1,0),
    Total,SUMPRODUCT((Names=TheName)*ActualDuration),
    MIN(Total,DaysInMonth)
)

Alecs
Brass Contributor
May 30, 2022
HansVogelaar
I've forgot about an important step.
Is it possible to do the same calculation with the same formula that has one more condition? I do not understand your formula to be able to reproduce it in this way.
I have added column B with Confirmation: YES or NO.
So, if it is YES than do the calculation. IF it is another status than don't do it. I've attached here the updated file.

Let's use this formula because other users that I work with have the 2019 excel version
=MIN(SUMPRODUCT(($A$3:$A$37=$H3)*IF(IF($C$3:$C$37<=EOMONTH(I$2,0),$C$3:$C$37,EOMONTH(I$2,0))-IF($B$3:$B$37>=I$2,$B$3:$B$37,I$2)>=0,IF($C$3:$C$37<=EOMONTH(I$2,0),$C$3:$C$37,EOMONTH(I$2,0))-IF($B$3:$B$37>=I$2,$B$3:$B$37,I$2)+1,0)),EOMONTH(I$2,0)-I$2+1)

Best regards,
Alex
Book112.xlsx16 KB
- HansVogelaar
  MVP
  May 30, 2022
  Alecs
  
  It would be
  
  =MIN(SUMPRODUCT(($A$3:$A$37=$I3)*($B$3:$B$37="YES")*IF(IF($D$3:$D$37<=EOMONTH(J$2,0),$D$3:$D$37,EOMONTH(J$2,0))-IF($C$3:$C$37>=J$2,$C$3:$C$37,J$2)>=0,IF($D$3:$D$37<=EOMONTH(J$2,0),$D$3:$D$37,EOMONTH(J$2,0))-IF($C$3:$C$37>=J$2,$C$3:$C$37,J$2)+1,0)),EOMONTH(J$2,0)-J$2+1)
  - Alecs
    Brass Contributor
    May 30, 2022
    It worked. Many thanks!
Alecs
Brass Contributor
May 21, 2022
HansVogelaar
Hans, as always... very helpful and good-hearted man. This is exactly what I've needed. I send you my best wishes. Many thanks!
Alex