Sumifs that also compares 2 columns and uses latest date

[Jamieandjo]

Hi Everyone

Wonder if you can help with a formula. Basically needs to total the amount of "In Plan" that are due to end in the months H3-J3. As you can see some of the End Dates have been Rescheduled with another column showing this so need something to only take the latest end date. I have tried incorporating a MAX but only get errors

Below is where I got to but have also attached a workbook to make things easier
=SUMIFS(B:B,"Permenant",C:C,"In Plan"&MAX(D:E)&H2)

I am not precious over the formula so if it can be done in another way I would be happy to hear it!

Many thanks for your help!

Jamie

[AliGW]

Are you really still using the now ancient Excel 2003??? If not, would you please update your user profile. Thanks.

[Glenn Kennedy]

are you still using Excel 2003? SUMIFS was not available in that version.

[Jamieandjo]

Woops sorry, we are using office 365 at work but couldn't email externally so put it together quick here so an example could be available. I only have 2003 on my laptop as not updated it so a formula for the newer version would be preferred, will update my profile now!

[Glenn Kennedy]

Updated to XLSX. One possibility:

=SUMPRODUCT(($B$2:$B$20="Permenant")*(IF($E$2:$E$20="",$D$2:$D$20,$E$2:$E$20)>=H2)*(IF($E$2:$E$20="",$D$2:$D$20,$E$2:$E$20)<=EOMONTH(H2,0)))

There is no 30th February, even in leap years (cell D19... changed in this file).

[Jamieandjo]

You sir are an absolute legend! Thank you so much, it's really appreciated