Как использовать функцию SUMIFS в Excel — подробное руководство
Excel предлагает множество функций для анализа данных, а SUMIFS — одна из самых удобных для суммирования по множеству критериев. В этом руководстве вы найдёте синтаксис, подробные примеры (текстовые, числовые, по датам, с подстановочными знаками), шаблоны, сценарии использования, распространённые ошибки и практические приёмы для повышения производительности.
Определение
SUMIFS — агрегирующая функция. Она суммирует значения в указанном диапазоне, но только для тех строк, которые соответствуют всем заданным критериям. Критерии могут быть текстовыми, числовыми, датными и с использованием масок.
Содержание
- Синтаксис функции
- Быстрые примеры
- Работа с текстовыми условиями
- Работа с числовыми условиями
- Работа с датами
- Подстановочные знаки и частичное совпадение
- Ссылки на ячейки и динамические критерии
- Структурированные таблицы и именованные диапазоны
- Альтернативы и когда не стоит использовать SUMIFS
- Полевые советы по производительности
- Шаблоны, чек-листы и тесты
- Краткое резюме
Синтаксис функции
=SUMIFS(sum_range, range1, criteria1, [range2], [criteria2], ...)Где:
- sum_range: диапазон, значения в котором будут суммироваться.
- range1: диапазон для проверки первого условия.
- criteria1: первое условие (строка, число, выражение, ссылка на ячейку или маска).
- range2, criteria2 и т.д.: дополнительные пары диапазон/критерий.
Важно: все диапазоны range1, range2 и sum_range должны иметь одинаковую размерность и ориентацию (колонки/строки), иначе Excel вернёт ошибку.
Быстрые примеры (горячий старт)
Пример структуры данных (оригинальная таблица сохранена ниже): колонка даты заказа, категория, цвет, цена продажи, количество, итоговая сумма.
Примеры формул:
- Сумма продаж по категории “Binders” и цвету “Green”:
=SUMIFS(F2:F18, B2:B18, "Binders", C2:C18, "Green")- Сумма продаж, где цена между 500 и 1000:
=SUMIFS(E2:E18, D2:D18, ">500", D2:D18, "<1000")- Сумма продаж в диапазоне дат:
=SUMIFS(F2:F18, A2:A18, ">1/1/2015", A2:A18, "<1/1/2016")- Сумма по частичным совпадениям: категория содержит “ind”, цвет содержит “la”:
=SUMIFS(F2:F18, B2:B18, "*ind*", C2:C18, "*la*")Работа с текстовыми условиями
Текстовые критерии можно задавать прямо в формуле в кавычках или ссылаться на ячейку.
Пример с ссылкой на ячейку:
=SUMIFS(F2:F18, B2:B18, $G$1, C2:C18, $H$1)Если в G1 стоит Binders, а в H1 — Green, формула становится переносимой и её легко менять без редактирования самой формулы.
Подсказка: не забывайте про лишние пробелы в ячейках — “Binders” и “Binders “ — разные значения.
Работа с числовыми условиями
Числовые критерии чаще всего записываются с операторами в виде строк:
- “>500” — больше 500
- “<=100” — меньше или равно 100
Если критерий формируется динамически, используйте конкатенацию:
=SUMIFS(E2:E18, D2:D18, ">" & G1, D2:D18, "<" & H1)Где G1 и H1 — ячейки с числами границ.
Работа с датами
Даты в критериях можно задавать несколькими способами:
- Явно в строке: “>1/1/2015” — работает, но зависит от региональных настроек.
- Использовать функцию DATE для надёжности:
=SUMIFS(F2:F18, A2:A18, ">" & DATE(2015,1,1), A2:A18, "<" & DATE(2016,1,1))- Или ссылаться на ячейки с датами:
=SUMIFS(F2:F18, A2:A18, ">" & $G$1, A2:A18, "<" & $H$1)Важно: если даты в таблице представлены как текст, сначала преобразуйте их в настоящие даты функцией DATEVALUE или через разделитель данных.
Примечание о локализации: в русскоязычной версии Excel формат даты в явной строке может отличаться; для надёжности используйте DATE или ссылки на ячейки.
Подстановочные знаки и частичное совпадение
SUMIFS поддерживает два подстановочных знака:
- (звёздочка) соответствует любому количеству символов.
- ? (вопрос) соответствует ровно одному символу.
Примеры:
=SUMIFS(F2:F18, B2:B18, "*ind*")
=SUMIFS(F2:F18, B2:B18, "???tors")Чтобы искать символы ““ или “?” как обычные символы, экранируйте их с помощью тильды: “~“.
Ссылки на ячейки и динамические критерии
Критерии можно строить из ссылок и выражений. Частая ошибка — попытка написать “>A1” вместо “>” & A1.
Правильно:
=SUMIFS(F2:F18, D2:D18, ">" & G1)Неправильно:
=SUMIFS(F2:F18, D2:D18, ">G1")Структурированные таблицы и именованные диапазоны
Преобразуйте диапазон в таблицу (Ctrl+T). Тогда формулы читаются понятнее и автоматически расширяются при добавлении строк:
=SUMIFS(Table1[Total Sales], Table1[Category], "Binders", Table1[Color], "Green")Именованные диапазоны также облегчают поддержку формул:
- Назовите столбец итоговых продаж SalesTotal и используйте:
=SUMIFS(SalesTotal, Category, "Binders", Color, "Green")Альтернативы SUMIFS и когда их использовать
- SUMIF — только одно условие. Меньше гибкости.
- SUMPRODUCT — мощная гибкость, умеет работать с логикой И/ИЛИ и массивами, но формулы могут быть более ресурсоёмкими:
=SUMPRODUCT((B2:B18="Binders")*(C2:C18="Green")*F2:F18)- Сводная таблица — хороший выбор для интерактивной агрегации, без написания формул.
- Функции динамических массивов (в новых версиях Excel) вместе с FILTER и SUM дают понятные решения:
=SUM(FILTER(F2:F18, (B2:B18="Binders")*(C2:C18="Green")))Когда SUMIFS не подойдёт:
- Нужна логика ИЛИ для нескольких значений в одном столбце (например, Category = A или B). В этом случае проще использовать SUMPRODUCT, складывать несколько SUMIFS или использовать SUM of FILTER.
- Работа с критериями, которые зависят от нескольких столбцов в сложной логике — SUMPRODUCT или Power Query предпочтительнее.
Производительность и лучшие практики
- Используйте точные диапазоны вместо целых столбцов (например, A:A), особенно в старых версиях Excel.
- При больших объёмах данных предпочтительнее структурированные таблицы и именованные диапазоны.
- Если нужно часто менять критерии — выносите их в отдельные ячейки, а не правьте формулы.
- Для сложной предобработки данных считайте Power Query более стабильным вариантом.
Частые ошибки и как их исправить
- #VALUE! или некорректный результат — проверьте, имеют ли диапазоны одинаковую длину.
- Ноль там, где должны быть суммы — проверьте формат ячеек (текст vs число/дата) и лишние пробелы.
- Ошибки при сравнении дат — используйте DATE или ссылки на реальные даты.
- Неправильная работа wildcard — убедитесь, что вы используете кавычки и звёздочки внутри строк.
Пошаговая мини-методология для внедрения SUMIFS в отчёты
- Преобразуйте исходный диапазон в таблицу (Ctrl+T).
- Добавьте именованные столбцы для ключевых полей (Category, Color, SalePrice, TotalSales).
- Введите критерии в отдельную панель (ячейки G1:H3 и т.д.).
- Составьте формулу SUMIFS со ссылками на панель критериев.
- Проверьте формулу на тестовых случаях (см. раздел тестов ниже).
- Если значения не совпадают с ожиданиями, проверьте формат и пропуски.
Роль‑ориентированные чек‑листы
Аналитик:
- Убедиться в консистентности типов данных.
- Использовать именованные диапазоны.
- Добавить проверочные кейсы.
Финансовый менеджер:
- Вынести периоды и лимиты в управляющие ячейки.
- Документировать логику расчётов.
Малый предприниматель:
- Использовать сводную таблицу для быстрых отчётов.
- Применять простые SUMIFS с 1–2 условиями для ежедневных сводок.
Шаблоны и приёмы (cheat sheet)
Ссылки на ячейки:
=SUMIFS(F2:F1000, B2:B1000, $G$1, C2:C1000, $H$1)Сравнение с несколькими значениями (ИЛИ) — два SUMIFS и плюс:
=SUMIFS(F2:F18, B2:B18, "A") + SUMIFS(F2:F18, B2:B18, "B")Или с SUMPRODUCT:
=SUMPRODUCT(((B2:B18="A")+(B2:B18="B"))*(F2:F18))Тестовые случаи и критерии приёмки
- Тест: одна строка соответствует всем критериям → сумма равна значению этой строки.
- Тест: ни одна строка не соответствует → результат 0.
- Тест: несколько строк соответствуют → сумма равна сумме их итогов.
- Тест: даты как текст → формула не должна считать строки, пока даты не преобразованы.
Критерии приёмки: формула возвращает ожидаемый результат на каждом из тестов, и диапазоны корректно расширяются при добавлении строк.
Когда SUMIFS выдаёт неверный результат — примеры и решения
Проблема: формула возвращает 0, хотя записи есть.
- Проверка: есть ли пробелы в критериях? Преобразуйте данные через TRIM.
- Проверка: совпадает ли формат дат/чисел? Преобразуйте типы.
Проблема: диапазоны разной длины.
- Решение: выровнять диапазоны или использовать структурированную таблицу.
Проблема: нужны логические ОР вместо И.
- Решение: сложение нескольких SUMIFS или использовать SUMPRODUCT.
Примеры реальных сценариев использования
- Ежемесячный отчёт по продажам: суммировать TotalSales по категории и региону.
- Финконтроль: суммировать расходы по категории и дате, чтобы отследить лимит расходов.
- Маркетинг: суммировать продажи там, где рекламная кампания = “Скидка” и канал = “Email”.
Оригинальная таблица данных (сохранена для воспроизведения примеров)
| | Order Date | | Category | | Color | | Sale Price | | Quantity | | Total Sales | |
| | 08-11-2016 | | Phones | | Black | | 907.152 | | 6 | | 5442.912 | |
| | 12-06-2016 | | Binders | | Green | | 18.504 | | 3 | | 55.512 | |
| | 11-10-2015 | | Appliances | | Yellow | | 114.9 | | 5 | | 574.5 | |
| | 11-10-2015 | | Tables | | Brown | | 1706.184 | | 9 | | 15355.656 | |
| | 09-06-2014 | | Phones | | Red | | 911.424 | | 4 | | 3645.696 | |
| | 09-06-2014 | | Paper | | White | | 15.552 | | 3 | | 46.656 | |
| | 09-06-2014 | | Binders | | Black | | 407.976 | | 3 | | 1223.928 | |
| | 09-06-2014 | | Appliances | | Yellow | | 68.81 | | 5 | | 344.05 | |
| | 09-06-2014 | | Binders | | Green | | 2.544 | | 3 | | 7.632 | |
| | 09-06-2014 | | Storage | | Orange | | 665.88 | | 6 | | 3995.28 | |
| | 09-06-2014 | | Storage | | Orange | | 55.5 | | 2 | | 111 | |
| | 15-04-2017 | | Phones | | Black | | 213.48 | | 3 | | 640.44 | |
| | 05-12-2016 | | Binders | | Green | | 22.72 | | 4 | | 90.88 | |
| | 22-11-2015 | | Appliances | | Green | | 60.34 | | 7 | | 422.38 | |
| | 22-11-2015 | | Chairs | | Dark Brown | | 71.372 | | 2 | | 142.744 | |
| | 11-11-2014 | | Technology | | Not Applicable | | 1097.544 | | 7 | | 7682.808 | |
| | 13-05-2014 | | Furniture | | Orange | | 190.92 | | 5 | | 954.6 | |
Краткое резюме
- SUMIFS — лучший выбор для суммирования по нескольким условиям.
- Для динамичности используйте именованные диапазоны и структурированные таблицы.
- Если нужна логика ИЛИ или более гибкая фильтрация, рассмотрите SUMPRODUCT, FILTER+SUM или сводные таблицы.
- Тестируйте формулы на граничных случаях: пустые значения, текст в числовых столбцах, разные форматы дат.
Важно: выбор инструмента зависит от объёма данных, требований к производительности и удобства поддержки отчёта.
Конец руководства.
