SUMIFS but only if value is less than another cell

[Lancer7]

Hey everyone, I currently have a formula =SUMIFS(Contracts!J:J,Contracts!E:E,"Lg. Green Lentil",Contracts!N:N,"2021") It works well for summing numbers based off of criteria on another worksheet, BUT I want to add a couple things 1) A max that the number can be so the current formula is in cell F45 and the answer I'm getting back is 26,455, but in cell C45 I have a number 17,254, I want the formula to work as is unless the number is equal to or greater than the number in C45 then it just stays at the value in cell C45. And 2) If C45 is blank then it just works as it was before. First time posting I'm not great on excel but usually between forums or videos I can figure out something that works but this one I just can't seem to figure out

 

[Alex Blakenburg]

Give this a try:

=IF(C45="",
           SUMIFS(Contracts!J:J,Contracts!E:E,"Lg. Green Lentil",Contracts!N:N,"2021"),
           MIN(SUMIFS(Contracts!J:J,Contracts!E:E,"Lg. Green Lentil",Contracts!N:N,"2021"),C45))

You can also use the LET command and make it something like:

=LET(xsumif,SUMIFS(Contracts!J:J,Contracts!E:E,"Lg. Green Lentil",Contracts!N:N,"2021"),
          IF(C45="",xsumif,MIN(xsumif,C45)))

 

[Lancer7]

Thank you!! Worked perfectly

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.