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

Всем добрый день, сегодня я открываю рубрику "Функции" и начну с функции СЧЁТЕСЛИ. Честно говоря, не очень-то и хотел, ведь про функции можно почитать просто в справке Excel. Но потом вспомнил свои начинания в Excel и понял, что надо. Почему? На это есть несколько причин:

  1. Функций много и пользователь часто просто не знает, что ищет, т.к. не знает названия функции.
  2. Функции - первый шаг к облегчению жизни в Экселе.

Сам я раньше, пока не знал функции СЧЁТЕСЛИ, добавлял новый столбец, ставил функцию ЕСЛИ и потом уже суммировал этот столбец.

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

=СЧЁТЕСЛИ("Диапазон";"Критерий")

Если с первым аргументом более-менее понятно, можно подставить диапазон типа A1:A5 или просто название диапазона, то со вторым уже не очень, потому что возможности задания критерия достаточно обширны и часто незнакомы тем, кто не сталкивается с логическими выражениями.счётесли в excel

Самые простые форматы "Критерия":

  • Ячейка строго с определенным значением, можно поставить значения ("яблоко"), (B4),(36). Регистр не учитывается, но даже лишний пробел уже включит в подсчет ячейку.
  • Больше или меньше определенного числа. Тут уже идет в ход знак равенства, точнее неравенств, а именно (">5");("<>10");("<=103").

Но ведь нам иногда нужны более специфичные условия:

  • Есть ли текст. Хотя кто-то может сказать, что функция и так считает только непустые ячейки, но если поставить условие ("*"), то будет искаться только текст, цифры и пробелы в расчет приниматься не будут.
  • Больше (меньше) среднего значения диапазона: (">"&СРЗНАЧ(A1:A100))
  • Содержит определенное количество символов, например 5 символов:("?????")
  • Определенный текст, который содержится в ячейке: ("*солнце*")
  • Текст, который начинается с определенного слова: ("Но*")
  • Ошибки: ("#ДЕЛ/0!")
  • Логические значения ("ИСТИНА")

Если же у вас несколько диапазонов, каждый со своим критерием, то вам надо обращаться к функции СЧЁТЕСЛИМН. Если диапазон один, но условий несколько, самый простой способ - суммировать: Функция СЧЁТЕСЛИ Excel Есть более сложный, хотя и более изящный вариант - использовать формулу массива: СЧЁТЕСЛИ, счетесли, Excel

Эксель Практик

«Глаза боятся, а руки делают»

P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс "Топ-10 инструментов Excel") и будьте в курсе новых событий.

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

  1. поМарка

    Подскажите, а как найти повторяющиеся значения, но что бы к регистру не было чувствительности?

  2. admin Автор записи

    по идее, СЧЁТЕСЛИ как раз и ищет совпадения, невзирая на регистр.

  3. поМарка

    В том и дело, что не находит — он не различает при поиске заглавные и строчные буквы, а объединяет их в общее количество совпадений…
    Подскажите, может надо какой-нибудь символ поставить при поиске? (апострофы и кавычки не помогают)

  4. Игорь

    Как задать условие в excel что бы он считал определенный диапазон ячеек в строках с определенным значением в первом столбце?

  5. admin Автор записи

    Игорь, вообще-то СЧЁТЕСЛИ это и делает. Наверное, вам лучше конкретизировать задачу.

  6. Анна

    понравилась статься, но увы не получается что-то, мне нужно суммировать из разных чисел повторяющиеся цифры, например 123 234 345 456 мне нужно посчитать сколько «1″, «2″, «3″ и т.д. в этих числах то есть чтобы формула распознала одинаковые цифры и считала их, если это возможно напишите как быть? Буду очень ждать, С уважением, Анна Ириковна

  7. admin Автор записи

    Гм. :) хорошо бы увидеть пример :)
    Но без неё могу дать наметку — создайте рядом столбец, где через текстовую формулу вы отберете числа по группам. Например, правсимв(А1;1). А потом уже по этому столбцу работайте СЧЁТЕСЛИ.

  8. Alex

    Как распространить формулу по столбцу, чтобы диапазон в формуле оставался тот же, а критерий менялся?

  9. admin Автор записи

    использовать абсолютные ссылки в диапазоне и относительный — в критерии:

    СЧЁТЕСЛИ($A$1:$A$100;B1)

  10. Анна

    Пример такой, 23 12 1972 то есть это дата рождения, мне нужно чтобы суммировалось количество двоек, то есть не 2+2+два, а что их всего три двойки, то есть в ячейке конечной должно стоять 3, единиц 2, 3 7 9 по единице, такое возможно? просто я голову сломала, я самоучка, но такие формулы мне сложноваты, если Вам не очень трудно дайте пожалуйста образец формулы полностью, хотя бы на одно число, с уважением, Анна Ириковна

  11. admin Автор записи

    Представим, что ваша дата в ячейке А1.

    Тогда считаем сколько двоек: = длстр(A1)-длстр(ПОДСТАВИТЬ(A1;»2″;»"))

    Если же на несколько чисел, то лучше скиньте пример, как вы это хотите в конце, а то вариантов много.

  12. Анна

    спасибо, я сейчас попробую формулу … попробую сама, если совсем не выйдет тогда с большим поклоном буду просить советов еще)) С уважением, Анна Ириковна

  13. Анна

    не выходит… можно я пришлю Вам то что мне нужно на электронный адрес? просто это таблица.. мне сложно ее описать… Анна И.

  14. Анна

    Подскажите как быть, вот есть у меня буквы Сс, СС, сс. и таких тысячи три они естественно повторяются. нужно посчитать сколько из этих 3х тысяч Сс, сколько СС и сс. Как это сделать? я уже не знаю как быть. Ибо эксель не различает их. Допустим есть у меня Сс, СС, сс, СС, хочу посчитать количество СС, пишет 4, т.е.считает всё.

  15. Макс

    Хорошая статья) Но все равно не смог разобраться со своим заданием. У меня есть 3 столбца, один это Студенты, второй Преподаватели, третий Оценки. Подскажите, как посчитать количество студентов, обучающихся у Ивановой, получивших положительные оценки? Получается вроде как 2 диапазона и 2 критерия, и я не могу понять)

  16. Maykot

    Добрый день.
    Помогите разобраться с диапазоном.
    У меня есть ячейка А2 в которой есть текстовое значение — например «солнце».
    В ячейке A3 значение «море». И т.д.
    Как правильно вписать в Формулу =СУММЕСЛИ(C:C;»*солнце*»;D:D) вместо конкретного диапазона («*солнце*») содержимое ячейки A2, т.е. не =СУММЕСЛИ(C:C;»*солнце*»;D:D), а вместо «*солнце*» была ссылка на ячейку?

  17. Дмитрий

    Здравствуйте! Подскажите как подсчитать кол-во числовых повторяющихся значений, если
    =СЧЁТЕСЛИ(F$8:$F$2951;A8) содержит число 257 в А8, а в массиве данных есть 0257 и он считает это значение как повтор, а этого не нужно!

  18. Александр

    Здравствуйте! Подскажите пожалуйста, как в Excel представить формулу:
    Скорректированная стоимость =
    = Стоимость * (К1 + К2 + … + КN – (N — 1);
    где:
    К1, К2, КN — коэффициенты, отличные от 1
    N – количество коэффициентов, отличных от 1.

  19. Александр

    Здравствуйте! Подскажите пожалуйста, как в формуле:
    =СУММЕСЛИ(C3:C14;»<1")-(СЧЁТЕСЛИ(C3:C14;"<1")-1)
    задать диапазон значений коэффициентов, отличных от 1 (менее 1, более 1, но менее 2).

  20. Михаил

    Добрый вечер!
    Подскажите, пожалуйста, как посчитать количество ячеек в которых указана какая-либо дата? То есть, в столбце есть ячейки с датами (разными) и есть ячейки с текстом (разным), мне нужно посчитать количество ячеек с датами.
    Спасибо!

  21. Юлия

    Добрый вечер! Подскажите, пожалуйста, формулу, считающую цифры только которые больше 8 (переработка в табеле учета рабочего времени). Вот неправильный вариант: =SUMIF(C42:V42;»>8″)+SUMIF(C42:V42)

  22. admin Автор записи

    =SUMIF(C42:V42,»>8″) =СУММЕСЛИ(C1:C2;»>8″)

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

  23. Д.Н.

    Добрый день! спасибо за формулу массива для диапазона с несколькими критериями!
    у меня, наверное, глупый вопрос, но как заменить текст {1;2;3} на ссылки на ячейки с текстовыми значениями.
    то есть если я ввожу { «X»; «Y»;»Z»} считает всё верно
    но при вводе {A1;A2;A3} — ошибка,
    дело в фигурных скобках?)

  24. Денис

    Добрый день. Столкнулся с такой проблемой в функции СЧЁТЕСЛИМН. При вводе 2х диапазонов все считает отлично, но при добавлении 3-го — выдает ошибку. Может ли скрываться подвох в количестве ячеек?
    У меня =СЧЁТЕСЛИМН(‘очная форма обучения’!R11C13:R250C13;»да»; ‘очная форма обучения’!R11C7:R250C7;»бюджет»; ‘очная форма обучения’!R16C9:R30C9;»да»)
    Без 3-го диапазона и условия все нормально.
    Заранее благодарен.

  25. admin Автор записи

    Денис, выровняйте диапазоны, они все должны быть одинаковые и можете ставить до 127 наборов условий.

  26. A.K.

    Здравствуйте, помогите, пожалуйста, разобраться.
    Есть два столбца: один — дата, второй — время (формат 00:00:00).
    Необходимо выбрать даты соответствующие определенному периоду времени.
    При этом, таких промежутков должно быть 4, т.е. каждые 6 часов.
    Возможно ли это это задать одной формулой и если — да, то какой?

  27. admin Автор записи

    Добрый день.
    Конечно, можно. Правда, я не понял, вам надо по датам или по часам? Две разные формулы. И как вы хотите это разбить? Чтобы промежутки помечались номерами? Типа первые 6 часов суток — это 1, вторые -2 и т.д.?

  28. андрей

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

  29. admin Автор записи

    К сожалению цвет формулой не определяется. Точнее определяется, но там надо формулу пользовательскую писать :)
    Я бы сделал так — отфильтровал по цвету зеленых и в соседнем столбце поставил «готовые», потом так же поставил «недоработанные» — белые ячейки.
    Потом через СЧЁТЕСЛИ нашел все, что надо.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>