1

I want to create a single formula that combines the two formulas together:
=IF(E1>500,500,E1)
and
=SUMIF(A1:A10,"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?

Improve this question

1 Answer 1

Reset to default
2

You may follow different approaches here:

  1. use an array formula:
    =SUM((A1:A10="y")*IF(E1:E10>500,500,E1:E10))
    this is an array formula, so after typing it you need to finish by pressing CTRL+SHIFT+ENTER
  2. split your task and cover different scenarios with different functions:
    =SUMIFS(E1:E10,A1:A10,"y",E1:E10,"<500")+COUNTIFS(A1:A10,"y",E1:E10,">=500")*500
    Here SUMIFS calculates only sum of entries below 500, and COUNTIFS counts entries above 500

enter image description here

Improve this answer
1
  • 1
    Wow, thank you so much! This is exactly what I need :)
    – Trixie
    Commented Jan 7, 2019 at 9:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .