Функция СЧЁТЕСЛИ в Excel: как посчитать ячейки с определённым значением

Сколько сделок у менеджера, сколько заказов из региона, сколько сумм выше порога — такие вопросы приходят каждый день, и пересчитывать строки глазами долго. Найти ответ помогает СЧЁТЕСЛИ в Excel — функция подсчёта по условию: по справке Microsoft СЧЁТЕСЛИ считает ячейки, которые отвечают одному критерию, и использует только одно условие^[1]. Для двух и больше условий у неё есть пара — СЧЁТЕСЛИМН.

Путь к рабочей формуле короткий: найти диапазон и значение, ввести СЧЁТЕСЛИ в Excel с условием, проверить результат на значении, которое легко пересчитать вручную, исправить пробелы и кавычки и протянуть формулу с закреплённым диапазоном на свои данные. Подвох в том, что ошибка в условии почти никогда не даёт сообщения: функция честно возвращает 0 или меньшее число.

Ключевые факты: СЧЁТЕСЛИ в Excel

• СЧЁТЕСЛИ считает ячейки диапазона, которые отвечают одному условию: числу, выражению, ссылке или тексту^[1].
• СЧЁТЕСЛИМН принимает до 127 пар диапазонов и условий, и все диапазоны должны быть одного размера^[4].
• При сравнении строк длиннее 255 символов функция возвращает неверный результат^[1].
• Справка Microsoft указывает, что критерии не учитывают регистр букв^[1].
• СЧЁТЕСЛИМН отличается от суммы нескольких СЧЁТЕСЛИ тем, что соединяет условия через «И», а сумма даёт «ИЛИ».

Как работает СЧЁТЕСЛИ в Excel: диапазон и одно условие

СЧЁТЕСЛИ в Excel считает ячейки диапазона, которые подходят под одно условие. Синтаксис: =СЧЁТЕСЛИ(диапазон;критерий), оба аргумента обязательны^[1]. В английском интерфейсе функция называется COUNTIF.

Функция СЧЁТЕСЛИ в Excel не различает регистр: по справке Microsoft «apples» и «APPLES» находят одни и те же ячейки^[1]. На листе-примере условие «иванов» строчными дало тот же результат, что «Иванов»^[2].

Пример — список сделок. В A7 фамилия набрана с пробелом в конце, как часто бывает после выгрузки из другой системы.

Справка относит страницу о функции к Excel 2021, Excel 2024 и Microsoft 365, включая Mac^[1]. В Google Таблицах есть COUNTIF с теми же двумя аргументами и COUNTIFS для нескольких критериев^[3]. СЧЁТЕСЛИ в Excel возвращает число, поэтому результат можно сразу делить и сравнивать: доля сделок от 100 считается как =СЧЁТЕСЛИ(C2:C7;">=100")/СЧЁТ(C2:C7) и на листе-примере даёт 0,33^[2].

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

Пошаговый подсчёт ячеек с определённым значением

СЧЁТЕСЛИ считает ячейки с определённым значением одной формулой по диапазону и критерию. Задача: узнать, сколько сделок у Иванова.

1. Выберите пустую ячейку, например E1.
2. Введите =СЧЁТЕСЛИ(A2:A7;"Иванов").
3. Нажмите Enter и сравните ответ с таблицей.
4. Если ответ меньше ожидаемого, проверьте ячейки на лишние пробелы.
5. Для подсчёта по каждой строке введите =СЧЁТЕСЛИ($A$2:$A$7;A2) и протяните вниз.

На этом листе формула вернула 2, хотя Ивановых три: в A7 лишний пробел, и «Иванов » для функции — другое значение^[2]. Справка Microsoft называет пробелы в начале и конце, разные кавычки и непечатные символы причиной неожиданного результата и советует ПЕЧСИМВ и СЖПРОБЕЛЫ^[1]. Условие с звёздочкой "Иванов*" нашло все три строки^[2].

Знак доллара закрепляет диапазон: без него при протягивании A2:A7 сползёт вниз, и СЧЁТЕСЛИ начнёт считать только нижнюю часть списка. Подробнее о приёме — на странице про абсолютные ссылки и клавишу F4. Формула по каждой строке показывает, сколько раз встречается значение; где ответ больше 1, там повтор. Проверочный приём для СЧЁТЕСЛИ в Excel простой: отфильтруйте столбец по тому же значению и сравните число строк с ответом формулы. Если числа расходятся, ищите пробелы и буквы из другой раскладки.

Повторите подсчёт в тренажёре: урок меняет список сделок и проверяет, что диапазон закреплён. Однако точное совпадение — лишь один вид условия: с числами, ссылками и частью текста критерий устроен иначе.

Чем отличаются условия: текст, числа, ссылка и звёздочка

СЧЁТЕСЛИ понимает текст, числа, знаки сравнения, ссылки и звёздочку в условии. Текст и знак сравнения берут в кавычки, число и ссылку пишут без них.

Все результаты получены на листе-примере^[2]. Оператор и ссылку соединяют знаком &: ">="&G1. Ссылка внутри кавычек для функции — просто текст «G1», поэтому ответ 0. Справка показывает тот же приём с «не равно»: "<>"&B4^[1].

Подстановочные знаки работают с текстом: вопросительный знак заменяет один символ, звёздочка — любую последовательность, а тильда перед знаком ищет сам знак^[1]. Условие "*" считает все ячейки с текстом. Логика «ИЛИ» собирается сложением нескольких функций, как в последней строке таблицы.

Таблица ниже связывает симптом, причину и действие, когда подсчёт даёт не то число.

Остаётся понять, как считать строки, где условий два и больше.

СЧЁТЕСЛИМН: до 127 пар условий и диапазоны одного размера

СЧЁТЕСЛИМН считает строки, где выполнены все условия по парам диапазон — условие. Справка Microsoft допускает до 127 пар диапазонов и условий^[4]. Синтаксис: =СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…).

Каждый дополнительный диапазон должен содержать столько же строк и столбцов, сколько первый, но стоять рядом диапазоны не обязаны^[4]. На листе-примере формула с диапазонами разной длины вернула #ЗНАЧ!^[2]. Строка «Иванов на Севере» дала 1 из-за того же пробела в A7: очистка данных меняет ответ и здесь. Формула с тремя условиями собирается так же: =СЧЁТЕСЛИМН(A2:A7;"Иванов*";B2:B7;"Север";C2:C7;">100") вернула на листе 1 — сделку на 120 на Севере^[2].

Для интервала «от и до» один диапазон указывают дважды с разными условиями. Справка разбирает пример =СЧЁТЕСЛИМН(A2:A7;"<6";A2:A7;">1"): он считает числа строго между 1 и 6 и даёт 4^[4]. Границы удобно держать в ячейках и подставлять через "<"&A6. Если условие ссылается на пустую ячейку, функция считает его равным 0^[4]. В условиях работают те же подстановочные знаки: вопросительный знак заменяет один символ, звёздочка — любую последовательность символов^[4].

СЧЁТЕСЛИМН соединяет условия только через «И»: строка попадает в подсчёт, лишь когда подходит под все пары. СЧЁТЕСЛИМН есть в списке версий справки с Excel 2016 до Microsoft 365, включая Excel в браузере^[4]. Именно поэтому для «ИЛИ» по-прежнему складывают несколько функций с одним условием.

Когда СЧЁТЕСЛИ не подойдёт: цвет, длинные строки, суммы

СЧЁТЕСЛИ не подойдёт, когда считать нужно по цвету, по длинному тексту или складывать суммы. В этих задачах нужен другой инструмент.

• Если ячейки различаются только цветом заливки или шрифта, формула их не посчитает — справка Microsoft прямо пишет, что функция этого не умеет^[1]; помогает фильтр по цвету.
• При сравнении строк длиннее 255 символов результат будет неверным^[1]: длинные описания сравнивают по ключевому фрагменту со звёздочкой.
• Если нужно не количество, а сумма по условию, подойдёт сложение по условию функцией СУММЕСЛИ.
• Когда условие зависит от двух столбцов сразу и нужен текстовый ответ в каждой строке, подсчёт не поможет — нужна проверка условия функцией ЕСЛИ.
• Если источник лежит в другой книге, она должна быть открыта, иначе формула вернёт #ЗНАЧ!^[1].

Смешанные столбцы — отдельная ловушка: числовое условие вроде ">55" пропускает текстовые ячейки, потому что функция игнорирует пустые и текстовые значения диапазона^[1]. Числа, сохранённые как текст, в такой подсчёт не попадут.

Без условия количество считают другие функции: СЧЁТ учитывает в диапазоне только числа и даты, пропуская текст и пустые ячейки^[5], а СЧЁТЗ — ячейки с любой информацией, включая пустой текст из формул^[6]. Сводная таблица с операцией «Количество» — ещё один способ из справки Microsoft: она сразу показывает количество записей по всем менеджерам или регионам^[7]. Остальные функции подсчёта и анализа собраны в обзоре формул Excel для подсчёта и анализа.