СУММЕСЛИ и СУММЕСЛИМН: условное суммирование в Excel

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

В этом руководстве обе функции рассматриваются с самого начала: синтаксис, реальные примеры, типичные ошибки и практический сценарий, которому вы можете следовать шаг за шагом. Независимо от того, отслеживаете ли вы продажи, управляете бюджетами или анализируете данные по проекту, условное суммирование избавит вас от огромного объёма ручной работы.

Что такое СУММЕСЛИ?

СУММЕСЛИ суммирует значения в диапазоне только в том случае, если соответствующая ячейка другого диапазона удовлетворяет заданному условию. Функция идеально подходит для одного критерия — например, «суммировать все продажи по региону Восток» или «сложить расходы, превышающие 500 $».

Синтаксис СУММЕСЛИ

=SUMIF(range, criteria, [sum_range])

• range — диапазон ячеек, проверяемых по условию.
• criteria — условие, которому должна отвечать ячейка. Может быть числом, текстом, ссылкой на ячейку или выражением.
• sum_range — (необязательный) диапазон ячеек, значения которых суммируются. Если аргумент опущен, Excel суммирует значения в самом диапазоне range.

Базовые примеры СУММЕСЛИ

Предположим, в столбце A указаны категории товаров, а в столбце B — суммы продаж. Чтобы суммировать все продажи по категории «Электроника»:

=SUMIF(A2:A100, "Electronics", B2:B100)

Чтобы суммировать все значения в столбце B, превышающие 1000:

=SUMIF(B2:B100, ">1000")

Обратите внимание: если диапазон проверки и диапазон суммирования совпадают, третий аргумент можно не указывать. Также учтите, что операторы сравнения, такие как >, <, >= и <>, необходимо заключать в кавычки.

Что такое СУММЕСЛИМН?

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

Синтаксис СУММЕСЛИМН

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range — диапазон ячеек для суммирования (всегда первый аргумент).
• criteria_range1 — первый диапазон для проверки.
• criteria1 — условие для первого диапазона.
• criteria_range2, criteria2 — дополнительные пары «диапазон/условие» (до 127 пар).

Базовый пример СУММЕСЛИМН

Используя тот же набор данных, суммируем продажи по категории «Электроника» в регионе «Восток» (при условии, что в столбце C указаны названия регионов):

=SUMIFS(B2:B100, A2:A100, "Electronics", C2:C100, "East")

Формула проверяет каждую строку: если в столбце A указано «Электроника» И в столбце C — «Восток», соответствующее значение из столбца B включается в итог.

Практическое руководство: анализ данных о продажах

Рассмотрим реалистичный сценарий. Представьте, что вы ведёте отчёт о продажах со следующими столбцами:

A: Продавец	B: Регион	C: Товар	D: Месяц	E: Выручка
Алиса	Восток	Ноутбуки	Январь	$4 200
Боб	Запад	Телефоны	Январь	$3 800
Алиса	Восток	Телефоны	Февраль	$2 900
Кэрол	Восток	Ноутбуки	Февраль	$5 100
Боб	Запад	Ноутбуки	Февраль	$4 400

Данные расположены со строки 2 по строку 500. Вот формулы для ответов на типичные бизнес-вопросы:

Общая выручка Алисы:

=SUMIF(A2:A500, "Alice", E2:E500)

Общая выручка по региону Восток:

=SUMIF(B2:B500, "East", E2:E500)

Общая выручка от продажи ноутбуков в регионе Восток:

=SUMIFS(E2:E500, C2:C500, "Laptops", B2:B500, "East")

Общая выручка Алисы от продажи ноутбуков в январе:

=SUMIFS(E2:E500, A2:A500, "Alice", C2:C500, "Laptops", D2:D500, "January")

Обратите внимание: каждое дополнительное условие сужает результат. Такой анализ вручную занял бы несколько минут, а с СУММЕСЛИМН выполняется мгновенно. Если вы создаёте полноценный инструмент отчётности, эти приёмы отлично сочетаются с методами из руководства Панель продаж в Excel: отслеживание KPI и результативности.

Использование ссылок на ячейки вместо жёстко заданного текста

Указывать критерии прямо в формуле удобно для разовых вычислений, но в дашбордах и отчётах ссылки на ячейки делают формулы динамическими и простыми в обновлении.

Введите «Alice» в ячейку H2, а «Laptops» — в ячейку H3. Формула примет вид:

=SUMIFS(E2:E500, A2:A500, H2, C2:C500, H3)

Измените H2 на «Bob» — и формула мгновенно пересчитается для продаж ноутбуков Боба. Такой подход необходим для интерактивных дашбордов. Понимание ссылок на ячейки в Excel — относительных и абсолютных поможет вам избежать неожиданного смещения ссылок при копировании формул.

Подстановочные знаки в СУММЕСЛИ и СУММЕСЛИМН

Обе функции поддерживают подстановочные знаки, особенно полезные при неоднородных данных:

• * — заменяет любую последовательность символов. "Ноут*" соответствует «Ноутбук», «Ноутбучная сумка» и т. д.
• ? — заменяет один любой символ. "Бо?" соответствует «Боб», «Бог», «Бор».
• ~ — символ экранирования. Используйте ~*, чтобы найти буквальную звёздочку.

Пример — суммируем выручку по всем товарам, название которых начинается с «Lap»:

=SUMIF(C2:C500, "Lap*", E2:E500)

Условное суммирование по датам

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

При условии, что столбец D содержит реальные значения дат (не текст), для суммирования выручки с 1 января по 31 марта 2024 года:

=SUMIFS(E2:E500, D2:D500, ">="&DATE(2024,1,1), D2:D500, "<="&DATE(2024,3,31))

Оператор & объединяет оператор сравнения (как текст) с результатом функции DATE. Это очень распространённый приём, который стоит запомнить.

Распространённые ошибки и способы их избежать

Несовпадающие размеры диапазонов

Все диапазоны в СУММЕСЛИМН должны быть одинакового размера. Если диапазон суммирования содержит 500 строк, а диапазон условия — 499, Excel вернёт ошибку. Всегда проверяйте согласованность диапазонов.

Отсутствие кавычек вокруг операторов

Запись вида =SUMIF(B2:B100, >500, B2:B100) приведёт к ошибке. Операторы и текстовые критерии необходимо заключать в кавычки: ">500" или "Электроника".

Порядок аргументов в СУММЕСЛИ и СУММЕСЛИМН

В СУММЕСЛИ диапазон суммирования является третьим аргументом. В СУММЕСЛИМН — первым. Путаница в порядке аргументов — частая причина неверных результатов: проверяйте его каждый раз.

Числа, сохранённые как текст (и наоборот)

Если столбец диапазона условий содержит числа, сохранённые в текстовом формате, числовой критерий не совпадёт с ними. Возможно, потребуется предварительно очистить данные. В статье Power Query: импорт и преобразование данных как профессионал описано, как эффективно решать подобные проблемы качества данных.

СУММЕСЛИ и СУММЕСЛИМН в сравнении с другими подходами

В Excel есть и другие способы условного суммирования, и полезно знать, когда применять каждый из них:

• Сводные таблицы — отлично подходят для исследовательского анализа и быстрых сводок, но результат не является живой формулой, которую можно встроить в ячейку дашборда. Когда этот подход предпочтительнее, смотрите в руководстве Сводные таблицы: полное руководство для начинающих.
• СУММПРОИЗВ — гибче СУММЕСЛИМН при сложных условиях, связанных с массивами или вычисляемыми критериями, но медленнее на больших наборах данных.
• ЕСЛИ + СУММ (формула массива) — устаревший подход с вводом через Ctrl+Shift+Enter. СУММЕСЛИМН чище и быстрее для стандартных сценариев.

Для большинства задач бизнес-отчётности СУММЕСЛИМН — оптимальный инструмент: быстрый, читаемый и покрывающий подавляющее большинство сценариев условного суммирования. При создании комплексного финансового обзора объединение СУММЕСЛИМН с приёмами из статьи Шаблон бюджета в Excel: учёт личных и корпоративных финансов формирует мощную и гибкую систему отчётности.

Сочетание СУММЕСЛИМН с другими функциями

СУММЕСЛИМН становится ещё мощнее в сочетании с другими формулами:

Вычисление доли от итога:

=SUMIFS(E2:E500, B2:B500, "East") / SUM(E2:E500)

Сравнение двух условных сумм:

=SUMIFS(E2:E500, B2:B500, "East") - SUMIFS(E2:E500, B2:B500, "West")

Использование с ЕСЛИ для корректной обработки пустого критерия:

=IF(H2="", SUM(E2:E500), SUMIFS(E2:E500, A2:A500, H2))

Если вы хотите углубить навыки работы с логическими формулами, следующим шагом станет статья Функция ЕСЛИ: логические проверки и вложенные ЕСЛИ.

Как быстрее получить правильную формулу

Если вы смотрите на сложную формулу СУММЕСЛИМН с четырьмя-пятью критериями и никак не можете понять, почему она возвращает ноль, попробуйте описать задачу простыми словами — инструменты вроде ExcelGPT сгенерируют нужную формулу по описанию наподобие «суммировать выручку, где регион — Восток, товар — ноутбуки, а дата — в первом квартале 2024 года», мгновенно предоставив точный синтаксис для проверки и использования.

Часто задаваемые вопросы

Может ли СУММЕСЛИ работать с несколькими условиями?

Напрямую — нет. СУММЕСЛИ рассчитана на одно условие. Для двух и более условий используйте СУММЕСЛИМН. Однако можно обойти это ограничение, складывая результаты нескольких СУММЕСЛИ, когда критерии применяются к одному диапазону и вам нужно условие ИЛИ (например, суммировать строки, где регион — «Восток» или «Запад»).

Почему СУММЕСЛИМН возвращает 0, хотя я знаю, что совпадающие данные есть?

Наиболее распространённые причины: числа, сохранённые как текст в диапазоне суммирования или диапазоне условий; лишние пробелы в значениях ячеек; несовпадение размеров диапазонов. Регистр символов не важен — СУММЕСЛИМН нечувствительна к нему. Используйте функцию СЖПРОБЕЛЫ или шаги по очистке данных для устранения проблем с пробелами.

Работает ли СУММЕСЛИМН с датами?

Да, при условии, что даты хранятся в виде реальных значений дат Excel (не текста). Используйте операторы сравнения вместе с функцией DATE или прямыми ссылками на даты: =SUMIFS(E2:E500, D2:D500, ">="&H1, D2:D500, "<="&H2), где H1 и H2 содержат начальную и конечную даты.

Есть ли ограничение на количество условий в СУММЕСЛИМН?

Excel допускает до 127 пар «диапазон условий/условие» в одной формуле СУММЕСЛИМН — значительно больше, чем когда-либо понадобится на практике. При очень больших наборах данных и многочисленных критериях производительность может снизиться, однако для типичных бизнес-данных (десятки тысяч строк) СУММЕСЛИМН остаётся быстрой и надёжной.