Функция СЧЕТЕСЛИ в Excel и примеры ее использования
Функция СЧЕТЕСЛИ входит в группу статистических функций. Позволяет найти число ячеек по определенному критерию. Работает с числовыми и текстовыми значениями, датами.
Синтаксис и особенности функции
Сначала рассмотрим аргументы функции:
- Диапазон – группа значений для анализа и подсчета (обязательный).
- Критерий – условие, по которому нужно подсчитать ячейки (обязательный).
В диапазоне ячеек могут находиться текстовые, числовые значения, даты, массивы, ссылки на числа. Пустые ячейки функция игнорирует.
В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.
Рекомендации для правильной работы функции:
- Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
- Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
- Функция не учитывает регистр текстовых значений.
- При формулировании условия подсчета можно использовать подстановочные знаки. «?» - любой символ. «*» - любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
- Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.
Функция СЧЕТЕСЛИ в Excel: примеры
Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.
У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;">100"). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

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

Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.
Формула: =СЧЁТЕСЛИ(A1:A11;"табуреты"). Или:

Во втором случае в качестве критерия использовали ссылку на ячейку.
Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;"таб*").
Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;"*и"). Получаем:

Формула посчитала «кровати» и «банкетки».
Используем в функции СЧЕТЕСЛИ условие поиска «не равно».
Формула: =СЧЁТЕСЛИ(A1:A11;"<>"&"стулья"). Оператор «<>» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

При применении ссылки формула будет выглядеть так:

Часто требуется выполнять функцию СЧЕТЕСЛИ в Excel по двум критериям. Таким способом можно существенно расширить ее возможности. Рассмотрим специальные случаи применения СЧЕТЕСЛИ в Excel и примеры с двумя условиями.
- Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;"столы")+СЧЁТЕСЛИ(A1:A11;"стулья"). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
- Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» - на базе критерия в ячейке А2.
- Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;">=100")-СЧЁТЕСЛИ(B1:B11;">200").
- Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;">=100")-СЧЁТЕСЛИ(A1:B11;">200"). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
- Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.





СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ
Посчитаем количество реализованных товаров по группам.
- Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.
- Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» - «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).


Скачать примеры функции СЧЕТЕСЛИ в Excel
Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.