Подсчёт по условию с помощью COUNTIF

SUMIF складывает совпадающие числа. Иногда итог не важен — нужно лишь узнать, сколько строк соответствует условию. Сколько продаж было в регионе Восток? Сколько оценок превышает 90?

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

COUNTIF проще, чем SUMIF, поскольку ему нужны только два элемента:

• диапазон ячейки, которые нужно просмотреть
• критерий условие, с которым сравнивается каждая ячейка

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

Предположим, регионы указаны в A2:A10. Чтобы узнать, сколько строк относятся к региону «Восток», проверьте этот диапазон на наличие слова «Восток», заключённого в кавычки.

Электронная таблица просматривает каждую ячейку и увеличивает счётчик на единицу каждый раз, когда встречает «Восток». Как и SUMIF, поиск совпадений в тексте не зависит от регистра.

Жёстко заданное слово вполне подходит, но ссылка на ячейку делает формулу гибкой. Если D1 содержит название региона, подсчитывайте совпадения с D1.

Измените D1 на «Запад» — и результат подсчёта сразу обновится. Это удобно сочетать с SUMIF в следующем столбце, чтобы показывать и итог, и количество для одного региона.

COUNTIF также работает с числами. Чтобы подсчитать, сколько оценок в B2:B10 равны ровно 100, используйте само число в качестве условия.

Для обычного числа кавычки не нужны, хотя они тоже сработают. Каждая ячейка, равная 100, увеличивает результат на единицу.

Настоящая сила проявляется при использовании операторов сравнения в кавычках. Чтобы подсчитать оценки больше 90:

• ">90" выше 90
• "<60" ниже 60
• ">=70" 70 или выше

Эта формула показывает, сколько значений превысили отметку 90, что отлично подходит для подсчёта результатов «сдано/не сдано».

Чтобы сравнить значение, хранящееся в ячейке, объедините оператор и ячейку с помощью амперсанда — точно так же, как в SUMIF. Если D1 содержит проходной балл, подсчитайте все значения, не меньшие этого балла.

& соединяет ">=" с содержимым D1, создавая динамическое условие, например ">=70".

Чтобы подсчитать ячейки, которые не равны определённому значению, используйте оператор неравенства <>. Эта формула подсчитывает все регионы, кроме «Востока».

Также можно подсчитать непустые ячейки с помощью одной только строки "<>": она соответствует любой ячейке, содержащей что-либо. Это быстрый способ узнать, сколько записей уже заполнено.

Два полезных шаблона:

• "" подсчитывает действительно пустые ячейки
• "<>" подсчитывает ячейки, содержащие что-либо

Это удобно для отслеживания прогресса: например, можно узнать, сколько задач ещё не имеют статуса. Обратите внимание: ячейка с пробелом выглядит пустой, но таковой не является, поэтому очистите данные, если результаты подсчёта кажутся неправильными.

COUNTIF особенно полезна при создании сводки частот. Введите каждый уникальный регион в ячейки D2, D3 и D4, затем напишите одну COUNTIF со ссылкой на ячейку с названием региона и протяните её вниз.

Теперь в каждой строке указано, сколько раз встречается этот регион. Добавьте SUM под результатами подсчёта: она должна равняться количеству строк с данными. Это быстрая проверка того, что ни одна строка не пропущена.

Есть ещё один полезный приём: поиск дубликатов. Если подсчитать, сколько раз значение из A2 встречается во всём столбце, и получить результат больше единицы, это дубликат.

Оберните формулу в IF, чтобы пометить такие значения: результат больше 1 означает, что запись повторяется. Это распространённый приём очистки списков перед анализом.

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

Теперь Вы умеете подсчитывать строки, соответствующие условию, с помощью COUNTIF. Запомните:

• Шаблон состоит только из диапазона и условия, без диапазона суммирования.
• Кавычки используются для текста и условий с операторами, например ">90".
• Объединяйте оператор с ячейкой с помощью &, как в ">="&D1.
• Используйте COUNTIF для таблиц частот и поиска дубликатов.

Далее Вы научитесь усреднять только совпадающие значения с помощью AVERAGEIF.