Функция COUNTIFS (СЧЁТЕСЛИМН) в Excel

Функция COUNTIFS в Excel возвращает количество ячеек, которые соответствуют нескольким условиям, называемым критериями. Для определения критериев СЧЁТЕСЛИМН поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?,~) и может использоваться для подсчета ячеек, содержащих даты, числа и текст.

Что делает функция COUNTIFS в Excel?

Функция COUNTIFS (СЧЁТЕСЛИМН) в Excel подсчитывает ячейки, соответствующие нескольким критериям.

Возвращаемое значение

Количество раз, когда критерии удовлетворяются.

Аргументы

  • range1 — первый диапазон для оценки.
  • criteria1 — критерии для диапазона1.
  • range2 — [необязательный] Второй диапазон для оценки.
  • criteria2 — [необязательный] Критерий для использования в диапазоне2.

Синтаксис

=COUNTIFS(range1, criteria1, [range2], [criteria2], …)

Примечания по использованию

Функция COUNTIFS подсчитывает ячейки в диапазоне, которые соответствуют нескольким условиям, называемым критериями . Каждое условие имеет отдельный диапазон и критерии . Для определения критериев COUNTIFS поддерживает различные логические операторы (>,<,<>,=) и подстановочные знаки (*,?,~). Функция СЧЁТЕСЛИМН — широко используемая функция в Excel, но синтаксис, используемый для применения условий, немного сложен, поскольку он необычен для Excel.

Синтаксис

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

=COUNTIFS(range1,criteria1) // 1 condition
=COUNTIFS(range1,criteria1,range2,criteria2) // 2 conditions

Первые два аргумента, диапазон1 и критерии1 являются обязательными . Диапазон1  — это диапазон, к которому следует применить критерий1 . Диапазон2 — это диапазон, к которому следует применить критерий2 . Дополнительные условия применяются путем предоставления дополнительных аргументов диапазона и критериев : третье условие определяется диапазоном3 и критериями3 , четвертое условие определяется диапазоном4 и критериями4 и так далее. При использовании СЧЁТЕСЛИМН помните следующее:

  • Для включения в окончательный подсчет должны быть соблюдены все условия.
  • Все диапазоны должны быть одного размера, иначе COUNTIFS вернут ошибку #ЗНАЧ! ошибка.
  • Критерии должны включать логические операторы (>,<,<>,<=,>=) по мере необходимости.
  • Каждое новое условие требует отдельного диапазона и критериев .

Критерии

Функция COUNTIFS поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного совпадения. Поскольку COUNTIFS находится в группе из восьми функций , которые разбивают логические критерии на две части, синтаксис немного сложен. Каждое условие требует отдельного диапазона и критериев , а операторы необходимо заключать в двойные кавычки («»). В таблице ниже приведены некоторые распространенные примеры:

Цель Критерии
Ячейки больше 75 «>75»
Ячейки равные 100 100 или «100»
Ячейки меньше или равные 100 «<=100»
Ячейки, равные «красному» «красный»
Ячейки не равны «красному» «<>красный»
Пустые ячейки «» «»
Непустые ячейки _ «<>»
Ячейки, начинающиеся с «X» «Икс*»
Ячейки меньше A1 «<«&A1
Ячейки меньше, чем сегодня «<«&СЕГОДНЯ()

Обратите внимание, что в последних двух примерах используется конкатенация с символом амперсанда (&). Когда аргумент критерия включает значение из другой ячейки или результат формулы, логические операторы, такие как «<«, должны быть объединены конкатенацией. Это связано с тем, что Excel необходимо сначала оценить ссылки на ячейки и формулы, чтобы получить значение, прежде чем это значение можно будет присоединить к оператору.

Базовый пример

В показанном примере COUNTIFS можно использовать для подсчета записей с использованием двух критериев следующим образом:

=COUNTIFS(C5:C14,"red",D5:D14,"tx") // red and TX
=COUNTIFS(C5:C14,"red",F5:F14,">20") // red and >20

Обратите внимание, что функция не чувствительна к регистру.

Двойные кавычки («») в критериях

Как правило, текстовые значения необходимо заключать в двойные кавычки, а числа — нет. Однако, когда логический оператор включен в число, число и оператор должны быть заключены в кавычки, как показано ниже:

=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"

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

Значение из другой ячейки

При использовании значения из другой ячейки в условии ссылка на ячейку должна быть  объединена  с оператором при использовании. В приведенном ниже примере COUNTIFS подсчитает значения в ячейке A1:A10, которые меньше значения в ячейке B1. Обратите внимание, что оператор «меньше » (текст) заключен в кавычки, а ссылка на ячейку — нет:

=COUNTIFS(A1:A10,"<"&B1) // count cells less than B1
Примечание. СЧЁТЕСЛИМН — это одна из нескольких функций, которые разбивают условия на две части: диапазон + критерии. Это вызывает некоторые несоответствия  по отношению к другим формулам и функциям.

Не равно

Чтобы создать критерий «не равно», используйте оператор «<>» , заключенный в двойные кавычки («»). Например, приведенная ниже формула будет подсчитывать ячейки, не равные «красному» в диапазоне A1:A10:

=COUNTIFS(A1:A10,"<>red") // not "red"

Пустые ячейки

COUNTIFS может подсчитывать ячейки, которые пусты или не пусты. Приведенные ниже формулы подсчитывают пустые и не пустые ячейки в диапазоне A1: A10:

=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank

Даты

Самый простой способ использовать COUNTIFS с датами — это сослаться на действительную дату в другой ячейке с помощью ссылки на ячейку. Например, для подсчета ячеек в A1:A10, содержащих дату, превышающую дату в B1, можно использовать следующую формулу:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1
Обратите внимание, что мы  присоединяем  оператор «>» к дате в ячейке B1, но и не заключаем в кавычки ссылку на ячейку.

Самый безопасный способ  жестко закодировать дату в СЧЁТЕСЛИМН — использовать функцию DATE . Это гарантирует, что Excel поймет дату. Чтобы подсчитать ячейки в A1: A10, содержащие дату менее 1 сентября 2020 г., вы можете использовать:

=COUNTIFS(A1:A10,"<"&DATE(2020,9,1)) // dates less than 1-Sep-2020

Подстановочные знаки

В критериях можно использовать знак вопроса (?), звездочку (*) или тильду (~) . Знак вопроса (?) соответствует любому одному символу, а звездочка (*) соответствует нулю или более символов любого типа. Например, для подсчета ячеек в A1:A5, содержащих текст «яблоко», можно использовать следующую формулу:

=COUNTIFS(A1:A5,"*apple*") // count cells that contain "apple"
Тильда (~) — это escape-символ, позволяющий находить буквальные подстановочные знаки. Например, чтобы подсчитать буквальный вопросительный знак (?), звездочку (*) или тильду (~), добавьте тильду перед подстановочным знаком (например, ~?, ~*, ~~).

Логика OR

Функция COUNTIFS предназначена для применения нескольких критериев, но условия применяются с логикой AND. Это означает, что если вы попытаетесь подсчитать ячейки, содержащие «красный» или «синий» в одном диапазоне, результат будет равен нулю (0). Однако для подсчета ячеек с помощью логики OR вы можете использовать константу массива и функцию SUM следующим образом:

=SUM(COUNTIFS(range,{"red","blue"})) // red or blue
Приведенная выше формула будет подсчитывать ячейки в диапазоне , содержащем «красный» или «синий». Вкратце, COUNTIFS возвращает два числа в массиве (одно для «красного» и одно для «синего»), а функция SUM возвращает сумму в качестве конечного результата.

Ограничения

Функция COUNTIFS имеет некоторые ограничения, о которых вам следует знать:

  • Условия соединяются логическим И. Другими словами, все условия должны быть TRUE , чтобы ячейка была включена в подсчет. Приведенный выше обходной путь можно использовать в простых ситуациях.
  • Функция требует фактических диапазонов для всех аргументов диапазона; вы не можете использовать массив. Это означает, что вы не можете изменить значения, которые появляются в аргументе диапазона, до применения критерия.
  • COUNTIFS не чувствителен к регистру. Для подсчета значений на основе условия с учетом регистра можно использовать формулу, основанную на функции SUMPRODUCT с EXACT.

Самый распространенный способ обойти вышеуказанные ограничения — использовать функцию SUMPRODUCT. В текущей версии Excel другим вариантом является использование более новых функций BYROW и BYCOL .

Примечания

  • Несколько условий применяются с логикой AND, т. е. условие 1 AND условие 2 и т. д.
  • Все диапазоны должны быть одного размера. Если вы укажете диапазоны, которые не совпадают, вы получите ошибку #VALUE.
  • Нечисловые критерии должны быть заключены в двойные кавычки (например, «<100», «>32», «TX»).
  • Подстановочные знаки «?» и «*» можно использовать в критериях. Знак вопроса соответствует любому одному символу, а звездочка соответствует любой последовательности символов.
  • Чтобы соответствовать буквальному вопросительному знаку (?) или звездочке (*), используйте тильду (~), т. е. (~?, ~*).
Автор А. Днепров

Обучаю основам работы с офисными программами.

Функция COUNTIFS (СЧЁТЕСЛИМН) в Excel
Как округлить значение