Функция SUMIFS в Excel

Функция SUMIFS (СУММЕСЛИМН) в Excel возвращает сумму ячеек, которые соответствуют нескольким условиям, называемым критериями. Для определения критериев функция SUMIFS поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?,~) и может использоваться с ячейками, содержащими даты, числа и текст.

Что делает функция SUMIFS в Excel?

Функция SUMIFS в Excel суммирует ячейки в диапазоне, соответствующем критериям.

Возвращаемое значение

Сумма ячеек, соответствующих всем критериям

Аргументы

• sum_range — суммируемый диапазон.
• range1 — первый диапазон для оценки.
• criteria1 — критерии для диапазона1.
• range2 — [необязательный] Второй диапазон для оценки.
• criteria2 — [необязательный] Критерий для использования в диапазоне2.

Синтаксис

=SUMIFS(sum_range, range1, criteria1, [range2], [criteria2], …)

Примечания по использованию

Функция SUMIFS суммирует ячейки в диапазоне, которые соответствуют нескольким условиям, называемым критериями. Каждое условие имеет отдельный диапазон и критерии. Для определения критериев SUMIFS поддерживает различные логические операторы (>,<,<>,=) и подстановочные знаки (*,?,~).

Функция SUMIFS — это широко используемая функция в Excel, которую можно использовать для суммирования ячеек на основе дат, текстовых значений и чисел. Однако синтаксис, используемый для применения условий, немного сложен, потому что он необычен для Excel.

Синтаксис

Синтаксис функции SUMIFS зависит от оцениваемых критериев. Каждое условие имеет отдельный диапазон и критерии. Общий синтаксис для SUMIFS (СУММЕСЛИМН) выглядит следующим образом:

=SUMIFS(sum_range,range1,criteria1) // 1 condition
=SUMIFS(sum_range,range1,criteria1,range2,criteria2) // 2 conditions

Первый аргумент, диапазон_суммирования, представляет собой диапазон ячеек для суммирования, который должен содержать числовые значения. Второй аргумент range1 — это диапазон, к которому должно применяться первое условие. Третий аргумент, критерии1, содержит условие, которое должно быть применено к диапазону1, вместе с любыми логическими операторами . Дополнительные условия применяются путем предоставления дополнительных аргументов диапазона/критериев. При использовании СУММЕСЛИМН помните следующее:

• Чтобы быть включенными в окончательный результат, все условия должны быть TRUE.
• Все диапазоны должны быть одного размера, иначе СУММЕСЛИМН вернет ошибку #VALUE! ошибка.
• Критерии должны включать логические операторы (>,<,<>,<=,>=) по мере необходимости.
• Каждое новое условие требует отдельного диапазона и критериев.

Применение критериев

Функция СУММЕСЛИМН поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного совпадения. Поскольку SUMIFS находится в группе из восьми функций, которые разбивают логические критерии на две части, синтаксис немного сложен.

Каждое условие требует отдельного диапазона и критериев, а операторы необходимо заключать в двойные кавычки («»). В таблице ниже приведены некоторые распространенные примеры:

Обратите внимание, что в последних двух примерах используется конкатенация с символом амперсанда (&). Когда аргумент критерия включает значение из другой ячейки или результат формулы, логические операторы, такие как «<«, должны быть объединены конкатенацией. Это связано с тем, что Excel необходимо сначала оценить ссылки на ячейки и формулы, чтобы получить значение, прежде чем это значение можно будет соединить с оператором.

Основное использование

С числами в диапазоне A1:A10 вы можете использовать SUMIFS для суммирования ячеек больше 5 следующим образом:

=SUMIFS(A1:A10,A1:A10,">5")

Если диапазон B1:B10 содержит названия цветов, такие как «red», «blue», and «green», вы можете использовать SUMIFS для суммирования чисел в A1:A10, когда цвет в B1:B10 «red», например:

=SUMIFS(A1:A10,B1:B10,"red")

Обратите внимание, что A1:A10 остается диапазоном суммы , но диапазон1 и критерии1 были изменены, чтобы применить условие, согласно которому ячейки в B1:B10 должны быть равны «red«. Чтобы суммировать ячейки в A1:A10, которые больше 5, только когда ячейки в B1:B10 равны «красному», вы можете использовать SUMIFS следующим образом:

=SUMIFS(A1:A10,A1:A10,">5",B1:B10,"red")

SUMIFS теперь использует пять аргументов: диапазон_суммы — это A1:A10, диапазон1 — это A1:A10, а критерий1 — «>5», а диапазон2 — это B1:B10, а критерий2 — «red».

Пример рабочего листа

На показанном листе есть две формулы SUMIFS. В первом примере (I5) функция СУММЕСЛИМН возвращает сумму значений в столбце F, где цвет столбца C — «red». Во втором примере (I6) функция суммирует значения в столбце F, когда цвет — «red», а штат — Техас (TX):

=SUMIFS(F5:F15,C5:C15,"red") // color="red"
=SUMIFS(F5:F15,C5:C15,"red",D5:D15,"TX") // color="red", state="TX"

Обратите внимание, что знак равенства (=) не требуется при построении критерия «равно». Также обратите внимание, что SUMIFS не чувствителен к регистру; вы можете использовать «красный» или «красный», и «TX» или «tx».

Ограничения

Функция SUMIFS имеет некоторые ограничения, о которых вам следует знать:

• Условия соединяются логическим AND. Другими словами, все условия должны быть TRUE, чтобы ячейка была включена в сумму. Для суммирования ячеек с помощью логики OR в простых случаях можно использовать обходной путь.
• Все диапазоны должны быть одного размера. Если вы укажете диапазоны, которые не совпадают, вы получите ошибку #VALUE.
• Функция требует фактических диапазонов для всех аргументов диапазона; нельзя использовать массив. Это означает, что вы не можете делать такие вещи, как извлечение года из дат внутри функции СУММЕСЛИМН. Для изменения значений, которые появляются в аргументе диапазона перед применением критериев, функция SUMPRODUCT является гибким решением.
• SUMIFS не чувствителен к регистру. Чтобы суммировать значения на основе условия с учетом регистра, вы можете использовать формулу, основанную на функции SUMPRODUCT с функцией EXACT.

Самый распространенный способ обойти вышеуказанные ограничения — использовать функцию СУММПРОИЗВ. В текущей версии Excel другим вариантом является использование более новых функций BYROW и BYCOL .

Примечания

• Несколько условий применяются с использованием логического AND, т.е. условие 1 AND условие 2 и т. д.
• Все диапазоны должны быть одного размера. Если вы укажете диапазоны, которые не совпадают, вы получите ошибку #VALUE.
• Нечисловые критерии должны быть заключены в двойные кавычки (например, «<100», «>32», «TX»).
• Ссылки на ячейки в критериях не заключаются в кавычки, т.е. «<«&A1.
• Подстановочные знаки «?» и «*» можно использовать в критериях. Знак вопроса (?) соответствует любому одному символу, а звездочка (*) соответствует любой последовательности символов (ноль или более).
• Чтобы соответствовать буквальному вопросительному знаку (?) или звездочке (*), используйте тильду (~), например (~?, ~*).