Функция СУММЕСЛИМН в Excel суммирует значения, которые подходят сразу под несколько условий: =СУММЕСЛИМН(C2:C100;A2:A100;"Север";B2:B100;"Стол") — сумма продаж столов в регионе «Север». Первым идёт диапазон, который нужно сложить, а дальше пары «где проверять; что искать» — до 127 пар. Условия бывают текстовыми ("Стол", "*стол*"), числовыми (">50000") и датами (">="&ДАТА(2024;1;1)), а строка попадает в сумму, только если выполнены все условия. На английском функция называется SUMIFS.
| Раздел функций | Математические |
| Название и описание на английском | SUMIFS |
| Волатильность | Не волатильная |
| Похожие функции | СЧЁТЕСЛИМН, СУММЕСЛИ, СУММПРОИЗВ |
Функция СУММЕСЛИМН — одна из самых полезных и мощных функций Excel для анализа данных. Она позволяет суммировать значения из определенного диапазона, которые соответствуют нескольким критериям одновременно. Если вы работаете с большими объемами данных и вам нужно выполнять сложные вычисления с условиями, эта функция станет вашим незаменимым помощником.
Что такое функция СУММЕСЛИМН?
СУММЕСЛИМН — это функция, которая суммирует ячейки в диапазоне, удовлетворяющие нескольким заданным условиям. В отличие от СУММЕСЛИ, которая работает только с одним критерием, СУММЕСЛИМН может проверять до 127 различных условий в разных диапазонах.
Синтаксис функции СУММЕСЛИМН
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; диапазон_условия2; условие2; ...)- диапазон_суммирования — диапазон ячеек, которые нужно суммировать
- диапазон_условия1 — первый диапазон, к которому применяется условие
- условие1 — первое условие в форме числа, выражения или текста
- диапазон_условия2, условие2, ... — дополнительные пары диапазонов и условий
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Товар | Менеджер | Месяц | Сумма | Условие | Итог | |
| 2 | Север | Стол | Иванов | Январь | 62000 | Север | 139000=СУММЕСЛИМН(E2:E8;A2:A8;"Север") | |
| 3 | Юг | Стол | Петрова | Январь | 18000 | Север + Стол | 103000=СУММЕСЛИМН(E2:E8;A2:A8;"Север";B2:B8;"Стол") | |
| 4 | Север | Стул | Иванов | Февраль | 9000 | Север + Стол + Январь | 62000=СУММЕСЛИМН(E2:E8;A2:A8;"Север";B2:B8;"Стол";D2:D8;"Январь") | |
| 5 | Север | Стол | Петрова | Февраль | 41000 | |||
| 6 | Центр | Кресло | Иванов | Январь | 75000 | |||
| 7 | Юг | Комод | Сидоров | Февраль | 33000 | |||
| 8 | Север | Компьютерный стол | Сидоров | Январь | 27000 |
Каждая новая пара «диапазон; условие» сужает выборку: строка суммируется, только если подходит под все условия сразу. «Компьютерный стол» не попал в «Стол» — условие без звёздочек требует точного совпадения.
В английском Excel: =SUMIFS(E2:E8,A2:A8,"Север",B2:B8,"Стол")
Особенности функции СУММЕСЛИМН
Кратко перечислю важные правила и особенности формул с функцией СУММЕСЛИМН.
Размеры диапазонов
Все диапазоны в функции СУММЕСЛИМН должны быть одинакового размера. Если диапазон суммирования имеет размер 10 строк, то все диапазоны условий также должны содержать 10 строк.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Регион | Сумма | Формула | Итог | |
| 2 | Север | 100 | одинаковые | 300=СУММЕСЛИМН(B2:B4;A2:A4;"Север") | |
| 3 | Юг | 50 | разные | #ЗНАЧ!=СУММЕСЛИМН(B2:B4;A2:A3;"Север") | |
| 4 | Север | 200 |
В отличие от СУММЕСЛИ, которая молча подгоняет диапазоны, СУММЕСЛИМН при несовпадении размеров возвращает #ЗНАЧ!.
Текстовые критерии
Текстовые критерии должны быть заключены в двойные кавычки. Если вы ссылаетесь на ячейку с критерием, кавычки не нужны.
=СУММЕСЛИМН(B2:B100; A2:A100; D1)| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Сумма | Регион? | Итог | Порог | Больше порога | ||
| 2 | Север | 100 | Север | 300=СУММЕСЛИМН(B2:B5;A2:A5;D2) | 90 | 450=СУММЕСЛИМН(B2:B5;B2:B5;">"&G2) | ||
| 3 | Юг | 50 | ||||||
| 4 | Север | 200 | ||||||
| 5 | юг | 150 |
Ссылка на ячейку пишется без кавычек. Если к ней нужен оператор, он в кавычках, а ячейка приклеивается амперсандом: ">"&G2. Регистр не учитывается: «юг» и «Юг» — одно и то же.
В английском Excel: =SUMIFS(B2:B5,B2:B5,">"&G2)
Чувствительность к регистру
Функция СУММЕСЛИМН не чувствительна к регистру. Условия "ТОВАР", "товар" и "Товар" будут обрабатываться одинаково.
Примеры использования СУММЕСЛИМН
Скачать файл со всеми примерами можно по ссылке.
Пример 1: Сумма продаж по региону и товару
Предположим, у нас есть таблица с продажами, и мы хотим узнать сумму продаж товара "Стол" в регионе "Север".
=СУММЕСЛИМН(C2:C100; A2:A100; "Север"; B2:B100; "Стол")| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Товар | Менеджер | Месяц | Сумма | Условие | Итог | |
| 2 | Север | Стол | Иванов | Январь | 62000 | Север и Стол | 103000=СУММЕСЛИМН(E2:E8;A2:A8;"Север";B2:B8;"Стол") | |
| 3 | Юг | Стол | Петрова | Январь | 18000 | |||
| 4 | Север | Стул | Иванов | Февраль | 9000 | |||
| 5 | Север | Стол | Петрова | Февраль | 41000 | |||
| 6 | Центр | Кресло | Иванов | Январь | 75000 | |||
| 7 | Юг | Комод | Сидоров | Февраль | 33000 | |||
| 8 | Север | Компьютерный стол | Сидоров | Январь | 27000 |
Суммируются строки 2 и 5: регион «Север» и товар ровно «Стол».
В английском Excel: =SUMIFS(E2:E8,A2:A8,"Север",B2:B8,"Стол")
Пример 2: Сумма продаж выше определенного значения
Допустим, нам нужно посчитать сумму всех продаж, которые больше 50000 рублей и были совершены менеджером "Иванов".
=СУММЕСЛИМН(D2:D100; D2:D100; ">50000"; E2:E100; "Иванов")| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Товар | Менеджер | Месяц | Сумма | Условие | Итог | |
| 2 | Север | Стол | Иванов | Январь | 62000 | > 50000 и Иванов | 137000=СУММЕСЛИМН(E2:E8;E2:E8;">50000";C2:C8;"Иванов") | |
| 3 | Юг | Стол | Петрова | Январь | 18000 | |||
| 4 | Север | Стул | Иванов | Февраль | 9000 | |||
| 5 | Север | Стол | Петрова | Февраль | 41000 | |||
| 6 | Центр | Кресло | Иванов | Январь | 75000 | |||
| 7 | Юг | Комод | Сидоров | Февраль | 33000 | |||
| 8 | Север | Компьютерный стол | Сидоров | Январь | 27000 |
Диапазон суммирования может одновременно быть и диапазоном условия — так отбираются крупные продажи.
В английском Excel: =SUMIFS(E2:E8,E2:E8,">50000",C2:C8,"Иванов")
СУММЕСЛИМН с операторами сравнения
В критериях СУММЕСЛИМН можно использовать различные операторы сравнения:
Пример 3: Сумма продаж в определенном диапазоне
Найдем сумму продаж от 10000 до 50000 рублей за январь.
=СУММЕСЛИМН(B2:B100; B2:B100; ">=10000"; B2:B100; "<=50000"; C2:C100; "Январь")| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Товар | Менеджер | Месяц | Сумма | Условие | Итог | |
| 2 | Север | Стол | Иванов | Январь | 62000 | 10000–50000, Январь | 45000=СУММЕСЛИМН(E2:E8;E2:E8;">=10000";E2:E8;"<=50000";D2:D8;"Январь") | |
| 3 | Юг | Стол | Петрова | Январь | 18000 | 10000–50000, всего | 119000=СУММЕСЛИМН(E2:E8;E2:E8;">=10000";E2:E8;"<=50000") | |
| 4 | Север | Стул | Иванов | Февраль | 9000 | |||
| 5 | Север | Стол | Петрова | Февраль | 41000 | |||
| 6 | Центр | Кресло | Иванов | Январь | 75000 | |||
| 7 | Юг | Комод | Сидоров | Февраль | 33000 | |||
| 8 | Север | Компьютерный стол | Сидоров | Январь | 27000 |
Интервал задаётся двумя условиями на один и тот же диапазон: нижняя и верхняя граница.
В английском Excel: =SUMIFS(E2:E8,E2:E8,">=10000",E2:E8,"<=50000")
Пример 4: Сумма продаж, исключая определенный товар
Посчитаем сумму всех продаж, кроме товара "Стул".
=СУММЕСЛИМН(D2:D100; B2:B100; "<>Стул")| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Товар | Менеджер | Месяц | Сумма | Условие | Итог | |
| 2 | Север | Стол | Иванов | Январь | 62000 | не Стул | 256000=СУММЕСЛИМН(E2:E8;B2:B8;"<>Стул") | |
| 3 | Юг | Стол | Петрова | Январь | 18000 | не Стул и не Стол | 135000=СУММЕСЛИМН(E2:E8;B2:B8;"<>Стул";B2:B8;"<>Стол") | |
| 4 | Север | Стул | Иванов | Февраль | 9000 | |||
| 5 | Север | Стол | Петрова | Февраль | 41000 | |||
| 6 | Центр | Кресло | Иванов | Январь | 75000 | |||
| 7 | Юг | Комод | Сидоров | Февраль | 33000 | |||
| 8 | Север | Компьютерный стол | Сидоров | Январь | 27000 |
«<>» — «не равно». Несколько исключений — несколько пар с тем же диапазоном.
В английском Excel: =SUMIFS(E2:E8,B2:B8,"<>Стул")
СУММЕСЛИМН с подстановочными знаками
В критериях СУММЕСЛИМН можно использовать подстановочные знаки:
- * — любое количество любых символов
- ? — один любой символ
Пример 5: Сумма продаж товаров, начинающихся на определенную букву
Найдем сумму продаж всех товаров, название которых начинается на "К".
=СУММЕСЛИМН(C2:C100; A2:A100; "К*")| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Товар | Менеджер | Месяц | Сумма | Условие | Итог | |
| 2 | Север | Стол | Иванов | Январь | 62000 | "К*" | 135000=СУММЕСЛИМН(E2:E8;B2:B8;"К*") | |
| 3 | Юг | Стол | Петрова | Январь | 18000 | "К*" и Север | 27000=СУММЕСЛИМН(E2:E8;B2:B8;"К*";A2:A8;"Север") | |
| 4 | Север | Стул | Иванов | Февраль | 9000 | |||
| 5 | Север | Стол | Петрова | Февраль | 41000 | |||
| 6 | Центр | Кресло | Иванов | Январь | 75000 | |||
| 7 | Юг | Комод | Сидоров | Февраль | 33000 | |||
| 8 | Север | Компьютерный стол | Сидоров | Январь | 27000 |
«Кресло», «Комод» и «Компьютерный стол» начинаются на «К». Звёздочка — любое количество символов.
В английском Excel: =SUMIFS(E2:E8,B2:B8,"К*")
Пример 6: Сумма продаж товаров с определенным шаблоном в названии
Найдем сумму продаж товаров, в названии которых есть "стол" в любом месте.
=СУММЕСЛИМН(D2:D100; B2:B100; "*стол*")| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Товар | Менеджер | Месяц | Сумма | Условие | Итог | |
| 2 | Север | Стол | Иванов | Январь | 62000 | "*стол*" | 148000=СУММЕСЛИМН(E2:E8;B2:B8;"*стол*") | |
| 3 | Юг | Стол | Петрова | Январь | 18000 | "Стол" точно | 121000=СУММЕСЛИМН(E2:E8;B2:B8;"Стол") | |
| 4 | Север | Стул | Иванов | Февраль | 9000 | |||
| 5 | Север | Стол | Петрова | Февраль | 41000 | |||
| 6 | Центр | Кресло | Иванов | Январь | 75000 | |||
| 7 | Юг | Комод | Сидоров | Февраль | 33000 | |||
| 8 | Север | Компьютерный стол | Сидоров | Январь | 27000 |
С звёздочками с обеих сторон учитывается и «Компьютерный стол». Без них — только точное совпадение.
В английском Excel: =SUMIFS(E2:E8,B2:B8,"*стол*")
Пример 7: Анализ продаж по датам и категориям
Предположим, нам нужно посчитать сумму продаж категории "Электроника" за первое полугодие 2024 года.
=СУММЕСЛИМН(E2:E1000; C2:C1000; "Электроника"; D2:D1000; ">=01.01.2024"; D2:D1000; "<=30.06.2024")| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Категория | Дата | Сумма | С | По | Итог | |
| 2 | Электроника | 05.01.2024 | 12000 | 01.01.2024 | 30.06.2024 | 42000=СУММЕСЛИМН(C2:C6;A2:A6;"Электроника";B2:B6;">="&E2;B2:B6;"<="&F2) | |
| 3 | Мебель | 14.02.2024 | 30000 | 42000=СУММЕСЛИМН(C2:C6;A2:A6;"Электроника";B2:B6;">="&ДАТА(2024;1;1);B2:B6;"<="&ДАТА(2024;6;30)) | |||
| 4 | Электроника | 30.06.2024 | 8000 | ||||
| 5 | Электроника | 01.07.2024 | 15000 | ||||
| 6 | Электроника | 20.03.2024 | 22000 |
Дата 30.06.2024 попадает в сумму («<=»), 01.07.2024 — уже нет. Даты границ удобнее держать в ячейках (E2, F2) или задавать ДАТА(): запись ">=01.01.2024" в кавычках зависит от региональных настроек.
В английском Excel: =SUMIFS(C2:C6,A2:A6,"Электроника",B2:B6,">="&E2,B2:B6,"<="&F2)
Пример 8: Суммирование с несколькими исключениями
Посчитаем сумму продаж всех регионов, кроме "Центр" и "Юг".
=СУММЕСЛИМН(F2:F500; A2:A500; "<>Центр"; A2:A500; "<>Юг")| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Товар | Менеджер | Месяц | Сумма | Условие | Итог | |
| 2 | Север | Стол | Иванов | Январь | 62000 | не Центр и не Юг | 139000=СУММЕСЛИМН(E2:E8;A2:A8;"<>Центр";A2:A8;"<>Юг") | |
| 3 | Юг | Стол | Петрова | Январь | 18000 | |||
| 4 | Север | Стул | Иванов | Февраль | 9000 | |||
| 5 | Север | Стол | Петрова | Февраль | 41000 | |||
| 6 | Центр | Кресло | Иванов | Январь | 75000 | |||
| 7 | Юг | Комод | Сидоров | Февраль | 33000 | |||
| 8 | Север | Компьютерный стол | Сидоров | Январь | 27000 |
Осталось только «Север». Исключения складываются через И: строка должна не равняться ни одному из значений.
В английском Excel: =SUMIFS(E2:E8,A2:A8,"<>Центр",A2:A8,"<>Юг")
СУММЕСЛИМН с условием ИЛИ
Все условия СУММЕСЛИМН работают через И. Чтобы сложить «Север ИЛИ Юг», передайте значения массивом и оберните в СУММ — функция посчитает сумму для каждого значения отдельно и сложит результаты.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Товар | Менеджер | Месяц | Сумма | Условие | Итог | |
| 2 | Север | Стол | Иванов | Январь | 62000 | Север или Юг | 190000=СУММ(СУММЕСЛИМН(E2:E8;A2:A8;{"Север";"Юг"})) | |
| 3 | Юг | Стол | Петрова | Январь | 18000 | (Север или Юг) и Стол | 121000=СУММ(СУММЕСЛИМН(E2:E8;A2:A8;{"Север";"Юг"};B2:B8;"Стол")) | |
| 4 | Север | Стул | Иванов | Февраль | 9000 | |||
| 5 | Север | Стол | Петрова | Февраль | 41000 | |||
| 6 | Центр | Кресло | Иванов | Январь | 75000 | |||
| 7 | Юг | Комод | Сидоров | Февраль | 33000 | |||
| 8 | Север | Компьютерный стол | Сидоров | Январь | 27000 |
Фигурные скобки задают массив констант. В русском Excel элементы разделяются точкой с запятой. Не используйте массив с двумя разными условиями одновременно — сложатся пары, а не все комбинации.
В английском Excel: =SUM(SUMIFS(E2:E8,A2:A8,{"Север","Юг"}))
Чем СУММЕСЛИМН отличается от СУММЕСЛИ
СУММЕСЛИ принимает одно условие, и диапазон суммирования в ней стоит в конце, а в СУММЕСЛИМН — в начале. Поэтому СУММЕСЛИМН удобна всегда: с одним условием она работает так же, а новые условия добавляются без перестановки аргументов.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Регион | Товар | Менеджер | Месяц | Сумма | Условие | Итог | |
| 2 | Север | Стол | Иванов | Январь | 62000 | СУММЕСЛИ | 139000=СУММЕСЛИ(A2:A8;"Север";E2:E8) | |
| 3 | Юг | Стол | Петрова | Январь | 18000 | СУММЕСЛИМН | 139000=СУММЕСЛИМН(E2:E8;A2:A8;"Север") | |
| 4 | Север | Стул | Иванов | Февраль | 9000 | |||
| 5 | Север | Стол | Петрова | Февраль | 41000 | |||
| 6 | Центр | Кресло | Иванов | Январь | 75000 | |||
| 7 | Юг | Комод | Сидоров | Февраль | 33000 | |||
| 8 | Север | Компьютерный стол | Сидоров | Январь | 27000 |
Результат одинаковый — отличается только порядок аргументов.
В английском Excel: =SUMIF(A2:A8,"Север",E2:E8)
Почему СУММЕСЛИМН возвращает 0 или ошибку
- #ЗНАЧ!. Диапазоны условий и суммирования разного размера — проверьте, что все начинаются и заканчиваются на одних и тех же строках.
- Результат 0. Лишние пробелы в данных, числа, сохранённые как текст, или условие без звёздочек там, где нужно частичное совпадение.
- Неверные даты в условии. Даты, сохранённые как текст, не сравниваются с ">=". Держите границы в ячейках или используйте ДАТА().
- Условие с ячейкой не работает. Оператор — в кавычках, ссылка — снаружи: ">"&G2.
- Перепутан порядок аргументов. В СУММЕСЛИМН диапазон суммирования первый, в СУММЕСЛИ — последний.
Функция СУММЕСЛИМН — частые вопросы
Как работает СУММЕСЛИМН?
Складывает значения первого диапазона в тех строках, где выполнены все условия: =СУММЕСЛИМН(C2:C100;A2:A100;"Север";B2:B100;"Стол").
Сколько условий можно задать?
До 127 пар «диапазон условия; условие».
Как сделать СУММЕСЛИМН с условием ИЛИ?
Массивом и СУММ: =СУММ(СУММЕСЛИМН(C2:C100;A2:A100;{"Север";"Юг"})).
Как суммировать за период дат?
Двумя условиями на столбец дат: =СУММЕСЛИМН(C2:C100;B2:B100;">="&E1;B2:B100;"<="&F1).
Как СУММЕСЛИМН называется на английском?
SUMIFS. Разделитель аргументов в английском Excel — запятая.
Суммировать и агрегировать данные по нескольким условиям, группировать по значениям столбцов и считать итоги без формул можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: функция СУММЕСЛИМН в Excel
| Задача | Формула |
|---|---|
| Два условия | =СУММЕСЛИМН(C:C;A:A;"Север";B:B;"Стол") |
| Больше числа | =СУММЕСЛИМН(C:C;C:C;">50000") |
| Интервал | =СУММЕСЛИМН(C:C;C:C;">=10000";C:C;"<=50000") |
| Кроме значения | =СУММЕСЛИМН(C:C;B:B;"<>Стул") |
| Содержит текст | =СУММЕСЛИМН(C:C;B:B;"*стол*") |
| Период дат | =СУММЕСЛИМН(C:C;D:D;">="&E1;D:D;"<="&F1) |
| ИЛИ | =СУММ(СУММЕСЛИМН(C:C;A:A;{"Север";"Юг"})) |
Надеюсь, на этой странице вы нашли то, что искали. СУММЕСЛИМН — основной инструмент для сумм по нескольким условиям: запомните порядок «что суммировать, где проверять, что искать».