Все формулы РФ

Excel и Google Workspace / IF, IFS

COUNTIF и COUNTIFS: подсчет строк по условиям

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

Опубликовано: Обновлено:

IF, IFSExcelGoogle ТаблицыОфисные расчетыЛогические функцииКачество данныхЕсть историческая справка

Формула

$$=COUNTIFS(A:A,"Москва",B:B,"Оплачен")$$

Обозначения

$A:A$
первый диапазон проверки условия, например город
$Москва$
первый критерий отбора
$B:B$
второй диапазон проверки условия, например статус
$Оплачен$
второй критерий отбора

Условия применения

  • COUNTIF используют для одного критерия, COUNTIFS - для двух и более критериев.
  • В COUNTIFS все диапазоны критериев должны иметь одинаковую форму и соответствовать друг другу по строкам.
  • Критерии могут быть текстом, числом, датой, ссылкой на ячейку или выражением сравнения вроде ">=100".

Ограничения

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

Подробное объяснение

COUNTIF и COUNTIFS отвечают на вопрос сколько строк или ячеек подходит под правило. Это отличается от SUMIF, где важно сложить числовые значения. Если руководитель спрашивает сколько счетов оплачено, сколько заявок просрочено или сколько клиентов попало в сегмент, нужна именно условная проверка количества.

COUNTIF устроена проще: один диапазон и один критерий. COUNTIFS расширяет идею до нескольких пар диапазон - критерий. Внутри COUNTIFS условия соединены логикой И, поэтому строка учитывается только тогда, когда она одновременно удовлетворяет всем указанным критериям. Это делает функцию естественной для отчетов по разрезам: город плюс статус, менеджер плюс месяц, категория плюс признак ошибки.

Критерий можно вынести в отдельную ячейку, чтобы формула стала частью небольшого отчета. Например, город находится в D2, статус в E2, а формула считает результат для выбранной пары. Такой подход лучше, чем редактировать текст в формуле вручную, потому что снижает риск опечаток и упрощает копирование по таблице KPI.

Главный практический риск - качество категорий. COUNTIFS не понимает, что Оплачен, оплачено и Оплачен с пробелом в конце должны означать одно и то же. Поэтому перед подсчетом полезно нормализовать статусы, использовать выпадающие списки и проверять неожиданные значения. Тогда условный подсчет становится надежной метрикой, а не случайной цифрой.

Как пользоваться формулой

  1. Определите, нужно посчитать одно условие или несколько условий одновременно.
  2. Для одного условия используйте COUNTIF, для нескольких - COUNTIFS.
  3. Проверьте, что диапазоны критериев в COUNTIFS имеют одинаковые строки.
  4. Запишите критерии текстом в кавычках или ссылками на ячейки отчета.
  5. Сверьте результат с фильтром по тем же условиям на небольшой выборке.

Историческая справка

Условный подсчет стал естественным развитием офисных таблиц после простых функций счета и суммы. Когда электронные таблицы начали использовать не только для расчетов, но и для списков заказов, клиентов и заявок, пользователям понадобилось быстро отвечать на вопрос сколько строк относится к категории. COUNTIF дал простой способ считать по одному признаку, а COUNTIFS расширил эту идею до нескольких разрезов. Эти функции оказались особенно важны для операционных отчетов, где количество событий не менее важно, чем сумма денег. В Excel и Google Таблицах условный подсчет стал базовым инструментом между ручным фильтром и более сложными сводными таблицами, потому что результат пересчитывается автоматически при изменении данных.

Историческая линия формулы

COUNTIF и COUNTIFS не связаны с одним автором как математическое открытие. Это функции электронных таблиц, возникшие из практики автоматического подсчета записей по условиям. Их исторический контекст - развитие офисных баз данных, отчетов и категорийной аналитики.

Пример

В A:A указан город, в B:B статус счета. Нужно посчитать оплаченные счета по Москве. Формула =COUNTIFS(A:A,"Москва",B:B,"Оплачен") проверяет каждую строку: город должен быть Москва и статус должен быть Оплачен. Если в таблице пять строк по Москве, но только три из них оплачены, результат будет 3. Для одного условия, например всех оплаченных счетов, достаточно =COUNTIF(B:B,"Оплачен"). Проверка результата делается фильтром: если отфильтровать город Москва и статус Оплачен, число видимых строк без заголовка должно совпасть с результатом формулы.

Частая ошибка

Частая ошибка - ждать от COUNTIFS логики ИЛИ. Формула с городом Москва и Санкт-Петербург в одной COUNTIFS не посчитает оба города одновременно, потому что одна строка не может быть сразу двумя разными текстами. Нужно сложить два COUNTIFS или использовать другой инструмент. Вторая ошибка - выбирать диапазоны разной длины, например A2:A100 и B2:B99. Третья ошибка - считать пустые ячейки без понимания, что пустая строка, формула с пустой строкой и пробел могут вести себя по-разному. Еще одна проблема - критерии дат: дата как текст может не совпасть с настоящей датой.

Практика

Задачи с решением

Посчитать оплаченные счета

Условие. В B2:B100 статусы счетов. Нужно посчитать строки со статусом Оплачен.

Решение. Есть один критерий по одному диапазону, поэтому используем COUNTIF: =COUNTIF(B2:B100,"Оплачен").

Ответ. =COUNTIF(B2:B100,"Оплачен")

Посчитать заявки по менеджеру и статусу

Условие. В A2:A100 менеджеры, в B2:B100 статусы. В D2 выбран менеджер, в E2 статус. Нужна формула подсчета.

Решение. Нужно одновременное выполнение двух условий: менеджер из D2 и статус из E2. Формула: =COUNTIFS(A2:A100,D2,B2:B100,E2).

Ответ. =COUNTIFS(A2:A100,D2,B2:B100,E2)

Дополнительные источники

  • Microsoft Support: Excel functions by category - https://support.microsoft.com/en-au/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb
  • Google Docs Editors Help: Google Sheets function list - https://support.google.com/docs/table/25273?hl=en
  • Microsoft Support: COUNTIF and COUNTIFS function documentation in Excel Support

Связанные формулы

Excel и Google Workspace

SUMIF и SUMIFS: сумма по одному или нескольким условиям

$=SUMIFS(C:C,A:A,"Москва",B:B,"Оплачен")$

SUMIF складывает значения по одному условию, а SUMIFS складывает значения по нескольким условиям. Эти функции строят обновляемые итоги по категориям без ручной фильтрации.

Excel и Google Workspace

AVERAGEIF и AVERAGEIFS: среднее по условиям

$=AVERAGEIFS(C:C,A:A,"Москва",B:B,"Оплачен")$

AVERAGEIF считает среднее значение по одному условию, а AVERAGEIFS - по нескольким условиям. Формулы помогают сравнивать средний чек, срок, оценку или показатель только внутри нужной группы.

Excel и Google Workspace

AND и OR внутри IF для сложных условий

$=IF(AND(B2>=100000,C2="Да"),"VIP","Обычный")$

AND и OR объединяют несколько проверок внутри IF. AND требует выполнения всех условий, а OR возвращает истину, если выполнено хотя бы одно из перечисленных условий.

Excel и Google Workspace

Сумма по условию SUMIF / СУММЕСЛИ

$=SUMIF(A:A,"Москва",B:B)$

SUMIF складывает значения только для тех строк, которые соответствуют одному условию. В русской локализации Excel функция обычно называется СУММЕСЛИ.