Условная агрегация: СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИМН

Примеры функции.xlsx

Сумма по условию: какую функцию выбрать

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

A: регион	B: товар	C: сумма
Север	Чай	100
Юг	Чай	200
Север	Кофе	300

При заголовках в строке 1 сумма по Северу равна 400:

=СУММЕСЛИ(A2:A4;"Север";C2:C4)

Сумма по Северу только для чая равна 100:

=СУММЕСЛИМН(C2:C4;A2:A4;"Север";B2:B4;"Чай")

Диапазоны условий должны соответствовать строкам суммируемого диапазона. Если нужно количество подходящих строк, используйте СЧЁТЕСЛИМН. Здесь и далее формулы записаны для русских имён функций с разделителем ;.

В прошлом уроке вы научились проверять условие через ЕСЛИ и комбинировать несколько проверок через И и ИЛИ. Та же идея — «отфильтровать строки по условию» — теперь применяется не к отдельной ячейке, а сразу к целому диапазону: функции СУММЕСЛИ, СУММЕСЛИМН и СЧЁТЕСЛИМН пробегают по столбцу, отбирают нужные строки и считают по ним сумму или количество.

Синтаксис СУММЕСЛИ: три аргумента и их роли

СУММЕСЛИ принимает два обязательных аргумента и один необязательный:

=СУММЕСЛИ(диапазон_условия; условие; [диапазон_суммирования])

Представьте таблицу продаж: в столбце A — регион, в столбце C — сумма сделки. Задача — посчитать выручку только по региону «Север».

=СУММЕСЛИ(A2:A10; "Север"; C2:C10)

Функция идёт по A2:A10, и каждый раз, когда в ячейке написано «Север», добавляет к итогу число из той же строки столбца C.

Первый аргумент — диапазон, где проверяется условие (A2:A10). Именно здесь функция ищет совпадения.

Второй аргумент — само условие. В данном случае текст «Север» в кавычках.

Третий аргумент — диапазон с числами, которые нужно сложить (C2:C10).

Критически важно: первый и третий диапазоны должны быть одинакового размера. Если первый занимает A2:A10 (9 строк), третий тоже должен быть ровно 9 строк — например, C2:C10. Если размеры не совпадают, Excel начинает с верхней левой ячейки диапазона суммирования и фактически использует область размера диапазона условия. В расчёт могут попасть не те ячейки, поэтому задавайте диапазоны одинакового размера и формы.

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

Запись условия: текст, число, оператор сравнения, ссылка на ячейку

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

Точный текст. Текстовое значение берётся в двойные кавычки:

=СУММЕСЛИ(A2:A10; "Север"; C2:C10)

Функция отберёт только те строки, где ячейка равна слову «Север» — регистр не важен.

Точное число. Число пишется без кавычек:

=СУММЕСЛИ(B2:B10; 5; C2:C10)

Здесь функция ищет строки, где в B стоит ровно 5.

Оператор сравнения. Когда нужно не точное совпадение, а диапазон значений, оператор и число объединяются в одну текстовую строку в кавычках:

=СУММЕСЛИ(C2:C10; ">500"; C2:C10)

Доступные операторы: >, <, >=, <=, <> (не равно).

Оператор + ссылка на ячейку. Если порог хранится в ячейке (например, в E2 написано 500 и пользователь может его менять), оператор и ссылку соединяют через амперсанд:

=СУММЕСЛИ(C2:C10; ">"&E2; C2:C10)

Excel сначала возьмёт значение из E2, затем склеит его с оператором и получит строку ">500" — ту же, что в предыдущем примере.

Частая ошибка — написать >E2 напрямую, без кавычек и амперсанда:

=СУММЕСЛИ(C2:C10; >E2; C2:C10)   ← неверно, вернёт ошибку

Excel не понимает >E2 как выражение внутри аргумента функции: для него это бессмысленный набор символов. Кавычки вокруг оператора и амперсанд перед ссылкой — обязательны.

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

Синтаксис СУММЕСЛИМН: диапазон суммирования первым и пары условий

Когда нужно наложить сразу два или больше условий, СУММЕСЛИ не справится — у неё только одно условие. Для нескольких используют СУММЕСЛИМН.

Синтаксис выглядит так:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; диапазон_условия2; условие2; ...)

Пример: таблица с регионом в столбце A, категорией товара в столбце B и суммой продаж в столбце C. Нужно посчитать выручку по региону «Север» и категории «Электроника»:

=СУММЕСЛИМН(C2:C100; A2:A100; "Север"; B2:B100; "Электроника")

Функция пройдёт по каждой строке, проверит оба условия одновременно и добавит значение из C только там, где выполнены оба.

Главное отличие от СУММЕСЛИ — диапазон суммирования стоит первым, а не третьим.

Это не опечатка и не случайность. В СУММЕСЛИ логика читается как «где проверяем → что ищем → что суммируем». В СУММЕСЛИМН после диапазона суммирования идут пары «где проверяем / что ищем», и таких пар может быть от одной до 127. Так определён синтаксис СУММЕСЛИМН: первый аргумент — единственный диапазон суммирования, затем идут пары «диапазон условия / условие». Этот порядок нужно проверять отдельно от СУММЕСЛИ.

Что произойдёт, если перепутать порядок и написать диапазон условия первым:

=СУММЕСЛИМН(A2:A100; "Север"; C2:C100; B2:B100; "Электроника")   ← неверно

Excel попробует суммировать значения из A2:A100 (где находятся текстовые названия регионов), а «Север» воспримет как диапазон условия. Для этой записи Excel вернёт ошибку: текст "Север" оказался на месте диапазона условия.

Как не запутаться: перед тем как вводить формулу, сначала определите столбец с числами для сложения — он всегда идёт первым аргументом. Все остальные аргументы — это пары «столбец для проверки / условие».

Как и в СУММЕСЛИ, все диапазоны — суммирования и условий — должны быть одинакового размера. Если диапазон суммирования C2:C100 (99 строк), то A2:A100 и B2:B100 тоже должны быть по 99 строк.

СЧЁТЕСЛИМН: подсчёт строк по нескольким одновременным условиям

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

Для этого используют СЧЁТЕСЛИМН:

=СЧЁТЕСЛИМН(диапазон_условия1; условие1; диапазон_условия2; условие2; ...)

Та же таблица: регион в A, категория в B, сумма в C. Нужно узнать, сколько сделок было в регионе «Север» по категории «Электроника»:

=СЧЁТЕСЛИМН(A2:A100; "Север"; B2:B100; "Электроника")

Функция не складывает числа — она просто считает строки, где одновременно выполнились оба условия. Результат — целое число: количество совпавших строк.

Главное структурное отличие от СУММЕСЛИМН: у СЧЁТЕСЛИМН нет аргумента «диапазон суммирования». Функция начинается сразу с первой пары «диапазон / условие». Это логично: нечего суммировать — нужно только посчитать.

Как выбрать между двумя функциями на практике:

• Нужна сумма значений по отобранным строкам → СУММЕСЛИМН
• Нужно количество строк, удовлетворяющих условиям → СЧЁТЕСЛИМН

Например, из одной таблицы можно получить сразу два ответа:

=СУММЕСЛИМН(C2:C100; A2:A100; "Север"; B2:B100; "Электроника")

Вернёт суммарную выручку по северным сделкам с электроникой.

=СЧЁТЕСЛИМН(A2:A100; "Север"; B2:B100; "Электроника")

Вернёт количество таких сделок.

Разделив одно на другое, можно получить среднюю сумму сделки — но это уже арифметика поверх двух формул, а не отдельная функция.

Как посчитать «Север» ИЛИ «Юг»

СУММЕСЛИМН соединяет пары условий через логическое И. Поэтому формула с критериями A2:A4;"Север" и A2:A4;"Юг" для одного и того же столбца вернёт 0: одна ячейка не может одновременно быть «Север» и «Юг». Если нужны две альтернативные категории, сложите два независимых расчёта.

=СУММЕСЛИ(A2:A4;"Север";C2:C4)+СУММЕСЛИ(A2:A4;"Юг";C2:C4)

Для таблицы в начале урока результат равен 600: 100+300 для Севера и 200 для Юга. Этот приём безопасен, когда категории взаимоисключающие, как разные названия региона в одном столбце. Если критерии могут пересечься, простое сложение дважды посчитает строки, удовлетворяющие обоим. Тогда нужно отдельно определить пересечение или перестроить условие. Для проверки сначала посчитайте каждую часть формулы по отдельности и сравните сумму с ручным итогом по таблице. В этом же уроке правило для двух обязательных признаков, например «Север» и «Чай», остаётся за СУММЕСЛИМН.

Закрепите тему на задачах

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

Условная агрегация: СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИМН — задача по Excel

Откройте условие и решите задачу перед чтением следующей темы. В задачах на код проверяется программа, в тестовых заданиях — выбранный ответ. Для последовательного обучения вернитесь в программу бесплатного курса.

---

Как собрать отчёт из нескольких выгрузок

Когда продажи приходят в нескольких файлах, сначала нужно объединить данные и проверить их, а затем считать показатели. В платном «Продвинутом Excel» для этого используются Power Query, Power Pivot и меры DAX. В программе — отчёты по продажам, марже, остаткам и план-факту.

• Посмотреть задачи курса

Выбрать другое направление в каталоге Latorn →