SUMIF only IF

[Citrusandsage]

Hi there,
I am stuck on a formula. So I have a sheet that has multiple components for commission so I need to be able to add up all the rows by a SUMIFS by Quarter. However there was just another element thrown in where if they are on extended leave they will receive $X for a leave bonus. Here is where I am stuck. If the value of the Sum if is < $X Leave Bonus than they get the $X leave bonus, If the Value of the Sumifs is > $X leave Bonus they get the value of the Sumifs. I cant figure out how to do logic around the result of the Sumifs. Help!!! Sorry could only provide Pic

IF the result of SUMIFS($B6:$J6,$B$5:$J$5,Q$5) is > L6 , SUMIFS($B6:$J6,$B$5:$J$5,Q$5) otherwise L6

 

[Cubist]

Try:

=MAX(SUMIFS($B6:$J6,$B$5:$J$5,Q$5),L6)

 

[SanjayGMusafir]

IF the result of SUMIFS($B6:$J6,$B$5:$J$5,Q$5) is > L6 , SUMIFS($B6:$J6,$B$5:$J$5,Q$5) otherwise L6

Check this and revert -

=Let(SA,SUMIFS($B6:$J6,$B$5:$J$5,Q$5),IFS(SA>L6,SA,True,L6))

 

[Citrusandsage]

it worked!!!!!! Thank you So much!!!

ChUpload Imageeck this and revert -

=Let(SA,SUMIFS($B6:$J6,$B$5:$J$5,Q$5),IFS(SA>L6,SA,True,L6))

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback. But must try @Cubist version in #2. It's smarter way.