Функция 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 находится в группе из восьми функций, которые разбивают логические критерии на две части, синтаксис немного сложен.
Каждое условие требует отдельного диапазона и критериев, а операторы необходимо заключать в двойные кавычки («»). В таблице ниже приведены некоторые распространенные примеры:
Цель | Критерии |
---|---|
Ячейки больше 75 | «>75» |
Ячейки равные 100 | 100 или «100» |
Ячейки меньше или равные 100 | «<=100» |
Ячейки, равные «Red» | «red» |
Ячейки не равны «Red» | «<>red» |
Пустые ячейки «» | «» |
Непустые ячейки _ | «<>» |
Ячейки, начинающиеся с «X» | «x*» |
Ячейки меньше A1 | «<«&A1 |
Ячейки меньше, чем сегодня | «<«&TODAY() |
Обратите внимание, что в последних двух примерах используется конкатенация с символом амперсанда (&). Когда аргумент критерия включает значение из другой ячейки или результат формулы, логические операторы, такие как «<«, должны быть объединены конкатенацией. Это связано с тем, что 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.
- Подстановочные знаки «?» и «*» можно использовать в критериях. Знак вопроса (?) соответствует любому одному символу, а звездочка (*) соответствует любой последовательности символов (ноль или более).
- Чтобы соответствовать буквальному вопросительному знаку (?) или звездочке (*), используйте тильду (~), например (~?, ~*).