How do I set a data cap in a SUMIF function?

I want to create a single formula that combines the two formulas together: =IF(E1>500,500,E1) and =SUMIF(A1:M10,”Y”,E1:E10)

Basically, I want to sum items from columns E1 to E10 when the value of a row in column A is equal to “Y”. I want to set a data cap wherein if one of the values to be added is greater than 500 (for example: 589), then the formula will read add the cell value as 500 instead of 589. If the value is 500 or less, we are adding the value as is.

How do I this?

By: Trixie

Leave a Reply

Your email address will not be published. Required fields are marked *