Подсчёт по нескольким условиям с помощью COUNTIFS

COUNTIFS подсчитывает количество строк, соответствующих нескольким условиям одновременно. Если COUNTIF отвечает на вопрос «сколько продаж было в регионе Восток?», то COUNTIFS отвечает на вопрос «сколько продаж в регионе Восток было в январе?»

Эта функция ничего не складывает и не усредняет — она просто подсчитывает строки, которые проходят каждую проверку. Поэтому она отлично подходит для вопросов вроде «сколько заказов на сумму свыше $100 поступило от новых клиентов?»

Разберём её структуру и применим её на практике.

В COUNTIFS нет диапазона суммирования, потому что функция не складывает значения — ей нужны только диапазоны для проверки и критерии, с которыми они сравниваются.

• criteria_range1, criteria1 — первая проверка
• criteria_range2, criteria2 — вторая проверка
• ...и так далее, всего до 127 пар

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

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

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

Как и SUMIFS, COUNTIFS принимает операторы сравнения в кавычках для числовых проверок.

• ">100" — значения больше 100
• ">=18" — 18 или больше
• "<>" — любая непустая ячейка

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

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

Эта формула подсчитывает суммы не менее 50 и не более 200. Оба условия ссылаются на столбец C, и поскольку COUNTIFS использует логику AND, подсчитываются только строки внутри заданного диапазона.

Чтобы сделать границы динамическими, сохраните их в ячейках и соедините оператор с помощью &. Введите нижнее значение в F1, а верхнее — в F2.

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

Для проверки качества данных полезны два специальных критерия:

• "<>" подсчитывает ячейки, которые не пусты
• "=" или "" подсчитывает ячейки, которые пусты

Эта формула подсчитывает, сколько строк региона Восток содержат имя менеджера по продажам (столбец D не пуст), — это быстрый способ обнаружить пропущенные данные.

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

Эта формула подсчитывает все строки, в которых имя менеджера по продажам начинается с буквы M: подходят Maria, Mike и Mohammed. Подстановочные знаки влияют только на текст; при проверке чисел они игнорируются.

Здесь действует то же правило размеров, что и в SUMIFS: каждый диапазон критериев должен иметь одинаковую форму. Если один диапазон — A2:A100, а другой — B2:B50, COUNTIFS возвращает ошибку #VALUE!.

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

COUNTIFS лежит в основе многих информационных панелей. Разместите названия регионов в столбце F, а названия месяцев — в строке 1, затем подсчитайте каждое сочетание с помощью смешанных ссылок, чтобы заполнить всю таблицу.

Фиксация столбцов с помощью $A:$A и ячеек поиска с помощью $F2 и G$1 позволяет одной формуле заполнить целую перекрёстную таблицу подсчётов при копировании.

Не путайте эти функции. COUNTIF принимает одну пару range, criteria, а COUNTIFS — одну или несколько пар. В отличие от SUMIFS, ни у одной из них нет отдельного диапазона результата — обе функции просто подсчитывают значения.

Если нужно больше одного условия, используйте COUNTIFS. Многие применяют COUNTIFS даже для одной проверки, чтобы запомнить только одну общую схему для SUM, COUNT и AVERAGE.

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

• COUNTIFS(range1, crit1, range2, crit2, ...) — диапазона суммирования нет, используются только пары для проверки.
• Чтобы подсчитать диапазон между двумя значениями, повторите один и тот же столбец с двумя границами.
• Используйте "<>" для непустых ячеек и соединяйте оператор с ячейкой с помощью &, чтобы задавать ограничения через ячейки.
• Все диапазоны должны быть одного размера.

Далее: усреднение значений по нескольким условиям с помощью AVERAGEIFS.