Суммирование по нескольким условиям с помощью SUMIFS

Вы уже знаете, что SUMIF суммирует значения, соответствующие одному условию, например все продажи из восточного региона. Но реальные вопросы обычно состоят из нескольких частей: Каковы были продажи восточного региона в январе? Это сразу два условия.

Здесь особенно полезна SUMIFS. Оконечная S означает, что функция может объединять множество критериев, а строка добавляется в итог только в том случае, если проходит каждую заданную Вами проверку.

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

SUMIFS меняет порядок, которого можно ожидать от SUMIF. Сначала указываются числа, которые нужно сложить, затем каждое условие задаётся парой.

• sum_range — значения, которые нужно суммировать
• criteria_range1, criteria1 — первая проверка
• criteria_range2, criteria2 — вторая проверка

Можно добавлять пары диапазона и критерия для 127 условий. Прочитайте схему вслух: просуммируйте это, если это равно тому и если это равно тому.

Представьте таблицу, где в столбце A указан регион, в столбце B — месяц, а в столбце C — сумма. Вам нужна общая сумма продаж для региона Восток за январь.

Суммируемые значения находятся в C:C. Первое условие проверяет A:A на наличие значения "Восток", а второе проверяет B:B на наличие значения "январь". Строка учитывается только тогда, когда оба условия истинны.

Это самая распространённая ошибка в SUMIFS. Диапазон суммирования и каждый диапазон критериев должны иметь одинаковые размеры — одинаковое количество строк и столбцов.

Если диапазон суммирования — C2:C100, а один из диапазонов критериев — A2:A99, формула возвращает ошибку #VALUE!, потому что строки не выровнены.

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

Жёстко заданное значение "Восток" в кавычках работает, но в гибком отчёте пользователь должен иметь возможность выбирать значение. Введите регион в ячейку F1, а месяц — в F2, затем укажите ссылки на эти ячейки в качестве критериев.

Теперь при изменении F1 или F2 итог пересчитывается мгновенно. Обратите внимание: обычная ссылка на ячейку не требует никаких кавычек — кавычки нужны только для буквального текста, введённого внутри формулы.

Критерии не ограничиваются точным совпадением текста. Для чисел можно использовать операторы сравнения, заключая их в кавычки.

• ">100" — больше 100
• "<=50" — меньше или равно 50
• "<>0" — не равно нулю

Здесь мы суммируем значения для региона Восток, но только в тех строках, где сама сумма больше 100. Обратите внимание: диапазон суммирования и диапазон критериев могут быть одним и тем же столбцом.

Что делать, если пороговое значение находится в ячейке, а не введено непосредственно? Нельзя просто написать ">F1" — это будет поиск буквального текста F1. Вместо этого нужно соединить оператор с ячейкой с помощью символа &.

Таким образом, ">"&F1 формирует условие «больше, чем содержится в F1». Этот приём сцепления необходим для динамических отчётов, которыми управляет пользователь.

SUMIFS легко масштабируется. Для каждого нового правила добавляйте ещё одну пару диапазона и критерия. Предположим, в столбце D указан менеджер по продажам. Тогда можно суммировать продажи региона Восток за январь, выполненные "Марией".

Каждое условие дополнительно сужает результат. Поскольку SUMIFS использует логику AND, строка должна удовлетворять всем трём проверкам, чтобы попасть в сумму.

Текстовые критерии поддерживают подстановочные знаки. Звёздочка * соответствует любому количеству символов, а вопросительный знак ? — ровно одному.

• "North*" соответствует значениям Север, Северо-восток и Северо-запад
• "*east*" соответствует любому тексту, содержащему восток

Это суммирует значения для любого региона, название которого начинается с "Север", что удобно, когда названия регионов имеют общий префикс.

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

• SUMIF: range, criteria, [sum_range] — сначала указывается диапазон для проверки, а диапазон суммирования является необязательным и указывается последним.
• SUMIFS: sum_range, criteria_range1, criteria1, ... — диапазон суммирования всегда указывается первым.

Совет: если условий больше одного, сразу используйте SUMIFS. Многие используют SUMIFS даже с одним критерием, чтобы придерживаться единой схемы.

Когда SUMIFS возвращает 0, обычно это означает, что ни одна строка не соответствует всем условиям, а не то, что формула не работает. Проверьте наличие скрытых пробелов в тексте, несовпадений в написании или чисел, сохранённых как текст.

Быстрый способ диагностики — удалять по одному условию за раз. Если итог появляется после удаления какого-либо критерия, именно этот критерий исключал все строки. Такой подход «изолировать и проверить» ускоряет отладку формул с несколькими критериями.

Проверьте, насколько хорошо Вы понимаете порядок аргументов SUMIFS.

Теперь Вы умеете суммировать числа одновременно по нескольким условиям:

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — диапазон суммирования указывается первым.
• Все диапазоны должны быть одного размера, иначе появится ошибка #VALUE!.
• Используйте ">"&F1 для сравнения со значением ячейки, а * и ? — для подстановочных знаков.
• Условия используют логику AND — строка должна пройти их все.

Далее: подсчёт строк по нескольким условиям с помощью COUNTIFS.