Date before another date (cell range) with SUMIFS

[Misleiloee]

 

Hello,

I would like to have the sum of closed topics before or at the targeted closure date. I tried to use SUMIFS but I don’t know how to put those closed before the targeted closure date, because I can’t put another formula inside SUMIFS formula like this:

=SUMIFS($A2:$A7 ; $B2:$B7 ; « Closed »;$C2:$C7;"<="&$D2:$D7)

How can I make it work ? I understand I need either to use another formula or adding/substracting with other formulas but I’m not sure what and how…

Thanks for any help!

edit: I made a sample excel sheet with column and type of data I have here

[#2]

@Misleiloee 

Try

=SUMPRODUCT(A2:A7; (B2:B7="Closed")*(C2:C7<=D2:D7))

[#3]

I tried but it didn’t work :(

[#4]

@Misleiloee 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#5]

@Hans Vogelaar 

i made an excel sheet with all important columns I have on my actual sheet for better example, here

[#6 · Accepted solution]

@Misleiloee 

See the attached version.

[#7]

Ohh, so that's how this formula works.
Thank you so much !
Have a great day/night