Как установить ограничение данных в функции SUMIF?

Question

Я хочу создать одну формулу, которая объединяет две формулы вместе:
=IF(E1>500,500,E1)
а также
=SUMIF(A1:A10,"Y",E1:E10)

По сути, я хочу суммировать элементы от столбцов E1 до E10, когда значение строки в столбце A равно "Y".

Я хочу установить ограничение данных, при котором, если одно из добавляемых значений больше 500 (например, 589), тогда формула будет читать добавление значения ячейки как 500 вместо 589.
Если значение равно 500 или меньше, мы добавляем значение как есть.

Как мне это?

Answer 1

Вы можете придерживаться разных подходов здесь:

1. используйте формулу массива:
   =SUM((A1:A10="y")*IF(E1:E10>500,500,E1:E10))
   это формула массива, поэтому после ее ввода нужно завершить, нажав CTRL+SHIFT+ENTER
2. разбить свою задачу и охватить различные сценарии с различными функциями:
   =SUMIFS(E1:E10,A1:A10,"y",E1:E10,"<500")+COUNTIFS(A1:A10,"y",E1:E10,">=500")*500
   Здесь SUMIFS рассчитывает только сумму записей ниже 500, а COUNTIFS считает записи более 500