COUNTIF и COUNTIFS в Google Таблицах: считать по одному или нескольким критериям

Кратко: что делают эти функции

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

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

Определение (одна строка): COUNTIF считает по одному критерию; COUNTIFS считает по набору критериев, применённых к соответствующим диапазонам.

Важно: COUNTIFS не суммирует независимые результаты для каждого условия. Он проверяет пересечение условий по строкам (или по индексам в диапазонах).

Синтаксис

COUNTIF:

=COUNTIF(range, condition)

COUNTIFS:

=COUNTIFS(range1, condition1, range2, condition2, ...)

range1 проверяется по condition1, range2 — по condition2 и т.д. Только те элементы, которые удовлетворяют ВСЕМ парам (rangeN, conditionN), будут посчитаны.

Подстановочные знаки и операторы

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

• ? — заменяет ровно один любой символ. Пример: R?d найдет Rod и Red.
• • — заменяет любое количество любых символов. Пример: R*d найдет Rod, Road, Roasted.
• ~ — экранирует следующий символ, чтобы он не воспринимался как подстановочный знак. Пример: R~?d найдет строку literally “R?d”.

Операторы сравнения в условиях:

• (больше)

• < (меньше)
• = (равно)

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

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

Пример с оператором в текстовой форме: “>=20” — строка-условие, поэтому берите её в кавычки.

Примечание: в локализации некоторых версий Google Таблиц аргументы функций разделяются точкой с запятой (;) вместо запятой (,). Проверяйте локальные настройки.

Пример: COUNTIF — считать волонтёров старше 20 лет

В таблице есть столбцы с возрастом (B2:B7) и ростом (C2:C7). Хотим посчитать, сколько волонтёров в возрасте 20 лет и старше.

Шаги:

1. Выберите ячейку для результата, например F4.
2. Введите формулу:

=COUNTIF(B2:B7, ">=20")

3. Нажмите Enter. Функция вернёт количество ячеек в диапазоне B2:B7, значение которых >= 20.

Ещё пример: посчитать, сколько ячеек в A1:A10 содержат слово “Red”:

=COUNTIF(A1:A10, "Red")

Важно: COUNTIF проверяет одно поле для каждого элемента. Если нужно одновременно проверить возраст и рост, используйте COUNTIFS.

Пример: COUNTIFS — возраст и рост одновременно

Задача: посчитать волонтёров, которые старше или равны 20 годам и имеют рост больше 175 см.

Формула:

=COUNTIFS(B2:B7, ">=20", C2:C7, ">175")

COUNTIFS проверит возраст по B2:B7 и рост по C2:C7. В результате будут посчитаны только те строки, где оба условия одновременно верны для соответствующих ячеек.

Частые ошибки и как их избежать

• Несопоставимые диапазоны: у COUNTIFS все диапазоны должны быть одинаковой длины и выровнены по индексам. Иначе функция вернёт ошибку.
• Неправильные кавычки: условия с операторами должны быть строками в кавычках, например “>=20”.
• Текст против чисел: “20” (текст) и 20 (число) обрабатываются по-разному. Старайтесь, чтобы сравниваемые значения были одного типа.
• Локаль разделителя аргументов: в русской локали часто используется “;” вместо “,”.
• Подстановочные знаки: если вы ищете символ ? или *, экранируйте его с помощью ~.

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

Когда COUNTIF/COUNTIFS не подходят — альтернативы

1. FILTER + ROWS/COUNTA — когда нужно получить подмножество строк и дальше с ним работать.

=ROWS(FILTER(A2:A100, (B2:B100>=20)*(C2:C100>175)))

2. SUMPRODUCT — гибкий инструмент для подсчёта с логикой поэлементно. Работает независимо от одинаковых размеров диапазонов (но лучше держать одинаковые длины).

=SUMPRODUCT((B2:B7>=20)*(C2:C7>175))

Здесь выражения в скобках дают массивы 0/1, а SUMPRODUCT суммирует их произведение.

3. QUERY — если вам удобнее писать SQL-подобные запросы.

=QUERY(A1:C100, "select count(B) where B >= 20 and C > 175", 1)

Выбор: COUNTIFS — проще и читабельнее для типичных задач подсчёта по критериям. SUMPRODUCT и QUERY дают больше гибкости для вычислений и агрегаций.

Подстановочные знаки: примеры и кейсы

• Найти все строки, где имя начинается на “Ann”:

=COUNTIF(A2:A100, "Ann*")

• Найти коды вида “A?3” (где ? — любой одиночный символ):

=COUNTIF(C2:C100, "A?3")

• Найти строку, содержащую символ “*” явно (экранование):

=COUNTIF(D2:D100, "*~**")

(в последнем примере первая — подстановочный знак для “содержит”, следом ~ означает буквальный символ звездочки).

Методика выбора: когда COUNTIF, когда COUNTIFS, когда другой инструмент

Правило-эвристика:

• Нужен один критерий к одному столбцу → COUNTIF.
• Нужен набор критериев по разным столбцам, применяемых одновременно → COUNTIFS.
• Нужно сложное булево условие (OR между столбцами, весовые коэффициенты) или агрегирование — рассмотрите SUMPRODUCT или QUERY.

Ментальная модель: представьте таблицу строк. COUNTIF отмечает каждую строку, если значение в указанном столбце проходит тест. COUNTIFS помечает строку только если все пометки в наборе столбцов истинны.

Дерево решений

flowchart TD
  A[Нужно посчитать строки по критериям?] --> B{Один критерий?}
  B -- Да --> C[Используйте COUNTIF'range, condition']
  B -- Нет --> D{Условия относятся к разным столбцам и должны выполняться одновременно?}
  D -- Да --> E[Используйте COUNTIFS]
  D -- Нет --> F{Нужно OR, взвешивание или сложная логика?}
  F -- Да --> G[Используйте SUMPRODUCT или QUERY]
  F -- Нет --> H[Используйте FILTER + ROWS/COUNTA для видимого результата]

Шаблоны формул (быстрый набор)

• Посчитать строки с пустым значением в столбце A:

=COUNTIF(A2:A100, "")

• Посчитать непустые значения:

=COUNTA(A2:A100)-COUNTIF(A2:A100, "")

• Посчитать строки с датой позже 1 января 2020 года (если в ячейках реальные даты):

=COUNTIF(A2:A100, ">" & DATE(2020,1,1))

• Посчитать строки, где A = “Yes” и B > 100:

=COUNTIFS(A2:A100, "Yes", B2:B100, ">100")

• Посчитать строки, соответствующие любому из нескольких значений (OR) — комбинируем COUNTIF:

=COUNTIF(A2:A100, "Red") + COUNTIF(A2:A100, "Blue")

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

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

1. Все значения проходят условия → результат равен количеству строк.
2. Ни одно значение не проходит → 0.
3. Пары диапазонов разной длины → проверка на ошибку или корректировку диапазонов.
4. Включение пустых и пробельных строк → ожидаемый/фактический результат совпадают.
5. Комбинация подстановочных знаков и операторов → вручную проверить пару хитровывернутых случаев.

Критерии приёмки: формулы должны возвращать ожидаемые результаты для перечисленных тестовых случаев.

Ролевые чек‑листы

Аналитик:

• Убедиться, что диапазоны выровнены по строкам.
• Проверить типы данных (текст/число/дата).
• Протестировать на граничных значениях.

Data steward:

• Очистить пробелы и нулевые строки.
• Стандартизировать форматы дат и чисел.

BI-разработчик:

• Выбрать оптимальный инструмент (COUNTIFS / SUMPRODUCT / QUERY).
• Документировать логику и предусмотреть комментарии в таблице.

Сравнение скорости и читабельности (какая функция когда лучше)

• COUNTIF/COUNTIFS — максимально читабельно и легко поддерживается; оптимальны для прямых запросов.
• SUMPRODUCT — мощный и гибкий, но формулы могут выглядеть сложнее.
• FILTER + ROWS — наглядно, если нужно получить сам набор строк, а не только число.
• QUERY — удобна для агрегаций и сложных группировок, особенно при больших таблицах.

Выбор зависит от требований поддержки и читаемости кода в вашей команде.

Безопасность данных и приватность

COUNTIF и COUNTIFS работают на видимых данных в таблице. При совместном доступе учитывайте, кто имеет доступ к самим ячейкам, так как формулы не маскируют содержимое. Для чувствительных данных используйте уровни доступа Google Таблиц и запросы на агрегирование до публикации.

Короткая шпаргалка — Cheat sheet

• Одиночное условие → COUNTIF.
• Множественные условия (AND) → COUNTIFS.
• Булево OR → суммировать несколько COUNTIF или использовать FILTER/SUMPRODUCT.
• Подстановочные знаки: ? и *; экранирование ~.
• Локаль может менять разделитель аргументов (запятая/точка с запятой).

Заключение

COUNTIF и COUNTIFS — базовые и очень полезные инструменты для подсчёта по условиям в Google Таблицах. COUNTIF удобен для простых задач, COUNTIFS — для набора условий сразу. Для более сложной логики используйте SUMPRODUCT, FILTER или QUERY. Перед публикацией проверяйте диапазоны, типы данных и локаль разделителей.

Важно: выбирайте инструмент по задаче и документируйте формулы — это сэкономит время команде при поддержке.

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

• COUNTIF — одно условие; простой и быстрый.
• COUNTIFS — несколько условий; применяются одновременно.
• Альтернативы нужны для OR‑логики, взвешивания или сложных агрегаций.