Функция Excel SUMIFS

Функция СУММЕСЛИМН в Excel позволяет суммировать значения ячеек по нескольким критериям. В этом руководстве приведены синтаксис функции СУММЕСЛИМН и примеры её использования.

• Пример 1: базовое использование функции СУММЕСЛИМН
• Пример 2: функция СУММЕСЛИМН с датами в Excel
• Пример 3: функция СУММЕСЛИМН с пустыми и непустыми ячейками
• Пример 4: функция СУММЕСЛИМН с несколькими критериями и логикой ИЛИ

 Синтаксис:

Синтаксис функции СУММЕСЛИМН в Excel:

 Аргументы:

• sum_range: Обязательно. Укажите ячейки или диапазон ячеек, которые нужно просуммировать. В расчёт попадают только ячейки с числами — пустые ячейки и текстовые значения автоматически игнорируются.
• criteria_range1: обязательно. Первый диапазон, к которому применяется критерий1.
• criteria1: обязательно. Первое условие, применяемое к диапазону1.
• criteria_range2, criteria2, … : необязательно. Дополнительные диапазоны и соответствующие им критерии.

Примечания:

• 1. Функция СУММЕСЛИМН поддерживает до 127 пар аргументов «диапазон_условия» и «условие».
• 2. Аргумент «диапазон_критериев» должен содержать столько же строк и столбцов, сколько и аргумент «диапазон_суммирования».
• 3. Критерий может быть числом, логическим выражением, ссылкой на ячейку, текстом, датой или другой функцией Excel. Если критерий — это текстовая строка или выражение, его необходимо заключить в двойные кавычки.
• 4. Функция СУММЕСЛИМН не учитывает регистр: например, текстовые строки «excel» и «EXCEL» считаются одинаковыми.
• 5. Функция СУММЕСЛИМН работает по логике «И»: ячейка из диапазона суммирования учитывается только если она соответствует всем заданным критериям.

 Результат:

Возвращает сумму чисел из списка, соответствующих всем заданным критериям.

 Примеры:

Пример 1: базовое использование функции СУММЕСЛИМН

Предположим, у вас есть таблица со столбцами «Товар», «Имя», «Продажи» и «Цена», как показано на рисунке ниже. Теперь вы хотите определить сумму общей цены для товара KTE, где объём продаж превышает 200.

Примените следующую формулу в пустую ячейку:

• Советы: в приведённой выше формуле:
• D2:D12: диапазон ячеек, которые нужно просуммировать.
• A2:A12, «KTE»: первый диапазон критериев и критерий, по которым выполняется суммирование значений.
• C2:C12, «>200»: второй диапазон критериев и сам критерий, по которым выполняется суммирование значений.

Затем нажмите клавишу Enter, чтобы получить нужный результат. См. рисунок:

Примечание: вместо условия в приведённой выше формуле можно использовать ссылку на ячейку, например так: =SUMIFS(D2:D12, A2:A12, F2, C2:C12, G2).

Пример 2: функция СУММЕСЛИМН с датами в Excel

Если у вас есть список дат и нужно просуммировать значения ячеек между двумя заданными датами, функция СУММЕСЛИМН справится с этой задачей быстро и легко.

Введите следующую формулу в пустую ячейку:

• Советы: в приведённой выше формуле:
• C2:C12: диапазон ячеек, которые нужно просуммировать.
• A2:A12, «>=4/1/2019»: первый диапазон критериев и сам критерий, по которым выполняется суммирование значений.
• A2:A12, «<=5/31/2019»: второй диапазон критериев и критерий, по которым выполняется суммирование значений.

Затем нажмите клавишу Enter — и общая цена за указанный период будет рассчитана. См. рисунок:

Примечание: вместо условия в приведённой выше формуле можно использовать ссылку на ячейку, например так: =SUMIFS(C2:C12, A2:A12, ">="&,E2, A2:A12, "<="&,F2).

Пример 3: функция СУММЕСЛИМН с пустыми и непустыми ячейками

Предположим, у вас есть диапазон данных, как показано на рисунке ниже. Как просуммировать общую цену при условии, что ячейки в столбце B не пустые, а ячейки в столбце C — пустые?

Чтобы решить эту задачу, используйте следующую формулу:

Затем нажмите клавишу Enter, чтобы получить нужный результат. См. рисунок:

Пример 4: функция СУММЕСЛИМН с несколькими критериями и логикой ИЛИ

По умолчанию функция СУММЕСЛИМН применяет только логику «И», но иногда возникает необходимость суммировать значения ячеек по нескольким критериям с использованием логики «ИЛИ» в Excel.

Например, если у вас есть диапазон данных, как показано на рисунке ниже, и вы хотите просуммировать общую сумму продаж от Дэвида, Джейн или Санни, где объём продаж превышает 200.

Формула 1: SUMIFS() + SUMIFS() + SUMIFS() + …

Чтобы решить эту задачу, объедините несколько функций СУММЕСЛИМН для суммирования ячеек по логике ИЛИ. Используйте следующую формулу:

Затем нажмите клавишу Enter, чтобы получить правильный результат:

Формула 2: SUM(SUMIFS({}))

Когда применяется несколько условий с логикой ИЛИ, приведённая выше формула становится довольно громоздкой. Чтобы упростить её, можно использовать формулу массива на основе функций СУММ и СУММЕСЛИМН.

Введите или скопируйте следующую формулу в пустую ячейку:

Затем одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы получить результат, как показано на рисунке ниже: