COUNTIF и COUNTIFS в Excel — подсчёт по условиям

Коротко о сути

COUNTIF и COUNTIFS — это встроенные функции Excel для автоматического подсчёта ячеек, соответствующих заданным критериям. COUNTIF применяют, когда нужен один критерий. COUNTIFS используют, когда нужно одновременно проверить несколько критериев на связанных диапазонах.

Общее определение и синтаксис

COUNTIF — считает ячейки, которые соответствуют одному условию.

COUNTIF(range, criteria)

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

COUNTIFS(range1, criteria1, range2, criteria2, ...)

Краткое определение терминов:

• range — диапазон ячеек, где выполняется поиск;
• criteria — условие; может быть строкой в кавычках, ссылкой на ячейку, выражением с операторами или подстановочными знаками.

Поддерживаемые логические операторы

• < меньше чем

• больше чем

• <= меньше или равно

• = больше или равно

• = равно
• <> не равно

Подстановочные знаки (wildcards)

• • любой набор символов
• ? любой один символ
• ~ экранирует следующий за ним символ как обычный

Важно: COUNTIF и COUNTIFS не чувствительны к регистру.

Пример использования COUNTIF

У нас есть столбец с цветами яблок. Нужно посчитать только красные.

Шаги:

1. Выделите ячейку для результата (в примере — E3).
2. Введите формулу:

=COUNTIF(A3:A11, "Red")

Здесь A3:A11 — диапазон, в котором ищем; “Red” — критерий (текстовые критерии берём в кавычки). Нажмите Enter — Excel вернёт число ячеек со значением Red.

Советы:

• Если критерий в ячейке, используйте ссылку: =COUNTIF(A3:A11, E1)
• Для частичных совпадений с подстановочными знаками: =COUNTIF(A3:A11, “Red“)
• Для числового сравнения: =COUNTIF(B2:B20, “>=100”) — кавычки обязательны вокруг операторов.

Пример использования COUNTIFS

Добавили столбец с размерами. Нужно посчитать только большие красные яблоки.

Шаги:

1. Выделите ячейку результата (в примере — E3).
2. Введите формулу:

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

COUNTIFS проверяет пары диапазон/критерий последовательно. Все условия работают как логическое И (AND): строка засчитывается, только если все критерии выполнены одновременно.

Обратите внимание:

• Диапазоны в COUNTIFS должны иметь одинаковую длину. Если длины отличаются, функция вернёт ошибку.
• Можно использовать только один диапазон и одно условие — тогда COUNTIFS эквивалентен COUNTIF.

Частые сценарии и готовые формулы

Шаблоны и примеры для вставки:

• Счёт значений в диапазоне, равных значению в ячейке G1:

=COUNTIF(A:A, G1)

• Числа больше заданного порога (ячейка H1):

=COUNTIF(B2:B100, ">" & H1)

• Частичное совпадение (начинается с «Pro»):

=COUNTIF(A2:A200, "Pro*")

• COUNTIFS с датами (C2:C100 — даты, считать после 2023-01-01):

=COUNTIFS(C2:C100, ">" & DATE(2023,1,1))

• Логическое ИЛИ для текстовых условий (когда нужно посчитать «Red» или «Big» по разным столбцам):

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

Этот пример суммирует результаты двух COUNTIF и даёт эффект ИЛИ. Учтите, что пересечения будут посчитаны дважды; для исключения дубликатов нужна дополнительная логика.

Альтернативы и когда их использовать

• SUMPRODUCT — гибкая альтернатива для сложных пересечений и учёта исключений. Работает с логикой массива.
• FILTER (Excel 365) + COUNTA — удобна для динамических фильтров и сложных условий.
• Таблица сводных данных (Pivot Table) — подходит для быстрых сводок без формул.
• SUBTOTAL в сочетании с фильтром — для подсчёта видимых строк.

Когда COUNTIF/COUNTIFS не подходят:

• Нужна чувствительность к регистру — используйте SUMPRODUCT с EXACT.
• Диапазоны разной длины — требуется объединение или приведение к общей длине.
• Логика ИЛИ по нескольким столбцам с пересечением — лучше SUMPRODUCT или UNIQUE+FILTER.

Примеры «реальных» ситуаций и обходы проблем

1. Проблема: COUNTIFS возвращает 0, хотя очевидно есть совпадения.

   • Проверьте длину диапазонов — они должны совпадать.
   • Убедитесь, что данные не содержат невидимые пробелы. Используйте TRIM или VALUE.
   • Проверьте типы данных: текст “100” и число 100 считаются разными.

2. Нужен поиск с учётом регистра.

   • COUNTIF не различает регистр. Используйте формулу с SUMPRODUCT и EXACT:

=SUMPRODUCT(--(EXACT(A2:A100, "Red")))

3. Надо посчитать строки, где столбец A = Red или столбец B = Big, но не оба одновременно.
   • Подход через SUMPRODUCT:

=SUMPRODUCT(((A2:A100="Red")+(B2:B100="Big")=1)*1)

4. Ещё один пример: искать символы «?» и «*».
   • Экранируйте подстановочные знаки с помощью ~: =COUNTIF(A:A, “~?”)

Мини-методология выбора функции

1. Сколько условий? 0–1 → COUNTIF. 2+ → COUNTIFS или SUMPRODUCT.
2. Нужна ли логика ИЛИ? → используйте SUM(COUNTIF…), SUMPRODUCT или FILTER.
3. Нужен ли анализ в реальном времени/динамика? → FILTER/COUNTIFS в Excel 365.
4. Диапазоны одинаковой длины? → обязательно для COUNTIFS.

Роль-зависимые чеклисты

Для аналитика:

• Убедиться в однородности типов данных.
• Проверить отсутствие лишних пробелов и невидимых символов.
• Написать тестовые случаи (см. ниже).

Для бухгалтера:

• Проверить условия сравнения дат и валют.
• Убедиться в корректных форматах чисел.

Для менеджера отчётности:

• Выбрать KPI и критерии до построения формул.
• Документировать используемые диапазоны и подписи столбцов.

Критерии приёмки

• Формула возвращает ожидаемое число для контрольных примеров.
• Диапазоны имеют одинаковую длину (для COUNTIFS).
• Формула устойчива к пустым строкам и пробелам.
• Документация в листе поясняет смысл критериев.

Тестовые случаи

1. Простое совпадение: один столбец, одно совпадение — ожидаем 1.
2. Несколько совпадений: подсчитать все — ожидаем N.
3. Пустые ячейки в диапазоне — не должны ломать формулу.
4. Разный тип данных (текст/число) — проверка обработки.
5. Пересечение условий для COUNTIFS — только строки, где выполняются все условия.

Частые ошибки и способы устранения

• Ошибка #VALUE! при COUNTIFS — вероятно, диапазоны разной длины.
• Неправильный результат при числах — посмотреть, не в виде ли текста число.
• Дублирование при суммировании COUNTIF для ИЛИ — учесть пересечения или использовать UNIQUE.

Короткий словарь терминов

• Диапазон — набор соседних ячеек, например A1:A10.
• Критерий — условие, по которому фильтруют значения.
• Wildcard — подстановочный знак (* или ?) для поиска по шаблону.

Рекомендации по производительности

• COUNTIFS быстрее и легче читается для стандартных задач по нескольким критериям.
• SUMPRODUCT может быть медленнее на больших массивах; для больших таблиц лучше сводные таблицы или Power Query.
• В Excel 365 используйте динамические массивы и FILTER, если нужна гибкость.

Заключение

COUNTIF и COUNTIFS — базовые, но мощные инструменты для подсчёта значений по условиям. COUNTIF хорош для одного текста, числа или шаблона. COUNTIFS нужен, когда условия должны выполняться одновременно. Для логики «ИЛИ», чувствительности к регистру или сложной фильтрации используйте SUMPRODUCT, FILTER или сводные таблицы. Простые правила выбора и тесты помогут избежать типичных ошибок.

Важно: перед финальной публикацией отчёта проверяйте формулы на контрольных выборках.

Краткое резюме:

• COUNTIF — один критерий;
• COUNTIFS — несколько критериев (AND);
• Для OR, чувствительности к регистру или сложных условий — альтернативы.