СУММЕСЛИМН и СЧЁТЕСЛИМН: суммы и подсчёт по условиям
Практика3 минРедакция Innovialabs
Коротко
СУММЕСЛИМН складывает значения из строк, где одновременно выполнены все условия, а СЧЁТЕСЛИМН считает такие строки; условия задаются парами «диапазон — критерий» и могут содержать даты, операторы сравнения и знаки * и ?.
Короткий ответ
СУММЕСЛИМН (SUMIFS) складывает числа из строк, где выполнены все условия сразу. СЧЁТЕСЛИМН (COUNTIFS) считает количество таких строк.
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; ...)
=СЧЁТЕСЛИМН(диапазон_условия1; условие1; [диапазон_условия2; условие2]; ...)
Обратите внимание: в СУММЕСЛИМН диапазон суммирования стоит первым, а в старой СУММЕСЛИ — последним. Проще всегда использовать СУММЕСЛИМН, даже для одного условия.
Примеры — для русской версии Excel с разделителем «;». В Google Таблицах функции называются SUMIFS и COUNTIFS и работают так же.
Пример: отчёт о продажах
Лист «Продажи»: A — дата, B — менеджер, C — город, D — товар, E — сумма, F — статус заказа.
Выручка менеджера Алишера в Ташкенте:
=СУММЕСЛИМН(E:E; B:B; "Алишер"; C:C; "Ташкент")
Лучше не писать значения в формуле, а ссылаться на ячейки: =СУММЕСЛИМН(E:E; B:B; H2; C:C; H3). Тогда формулу можно протянуть на всю таблицу отчёта. Регистр букв не учитывается: «ташкент» и «Ташкент» совпадут.
Операторы сравнения
| Условие | Что означает |
|---|---|
">100000" | больше 100 000 |
"<=50" | меньше или равно 50 |
"<>Отменён" | всё, кроме «Отменён» |
"<>" | непустые ячейки |
"=" | пустые ячейки |
">="&H2 | больше или равно значению из H2 |
Главная ошибка — написать ссылку внутри кавычек: ">=H2" сравнивает с текстом «H2». Оператор пишется в кавычках, а ссылка присоединяется через &.
Условия по датам
Выручка за март 2025 года:
=СУММЕСЛИМН(E:E; A:A; ">="&ДАТА(2025;3;1); A:A; "<"&ДАТА(2025;4;1))
- Один и тот же столбец можно указать дважды — так задаётся интервал.
- Верхнюю границу задавайте как «меньше первого дня следующего месяца», а не «меньше или равно 31-му числу»: если в датах есть время, строки за последний день иначе потеряются.
- Не пишите дату текстом вроде
">=01.03.2025": результат зависит от региональных настроек.
Для отчёта по месяцам поставьте в H2 первое число месяца и используйте ДАТАМЕС:
=СУММЕСЛИМН(E:E; A:A; ">="&H2; A:A; "<"&ДАТАМЕС(H2;1))
Начало текущего месяца даёт формула =КОНМЕСЯЦА(СЕГОДНЯ();-1)+1.
Подстановочные знаки
*— любое количество символов:"*iPhone*"найдёт все товары, где встречается iPhone.?— ровно один символ:"А-???"найдёт коды вида «А-101».~— экранирование, если нужно найти сам символ:"~*".
Подстановочные знаки работают только с текстом. Для чисел используйте операторы сравнения.
Подсчёт заказов
Количество оплаченных заказов за месяц:
=СЧЁТЕСЛИМН(F:F; "Оплачен"; A:A; ">="&H2; A:A; "<"&ДАТАМЕС(H2;1))
Средний чек — сумма, делённая на количество. Чтобы не получить #ДЕЛ/0! в месяце без продаж, используйте СРЗНАЧЕСЛИМН (AVERAGEIFS) с теми же условиями и оберните её в ЕСЛИОШИБКА с понятным текстом, например «нет продаж».
Условие «ИЛИ»
Все условия внутри одной СУММЕСЛИМН связаны логикой «И». Чтобы получить «Ташкент или Самарканд», сложите две формулы:
=СУММЕСЛИМН(E:E; C:C; "Ташкент") + СУММЕСЛИМН(E:E; C:C; "Самарканд")
Если вариантов много или логика сложная, удобнее СУММПРОИЗВ или вспомогательный столбец с признаком «подходит / не подходит».
Частые ошибки
- Диапазоны разного размера —
E2:E100иB2:B90дают #ЗНАЧ!. Все диапазоны должны совпадать по числу строк. - Суммы, сохранённые как текст, молча пропускаются, и итог оказывается меньше реального. Признак — числа прижаты к левому краю ячейки.
- Лишние пробелы: «Ташкент » с пробелом в конце не равен «Ташкент». Почистите данные функцией СЖПРОБЕЛЫ.
- Даты в виде текста не попадают в интервалы. Проверьте ячейку функцией ЕЧИСЛО: настоящая дата — это число.
- Строки итогов внутри данных приводят к двойному счёту. Держите исходную таблицу без промежуточных итогов.