Примеры функции «СЧЁТЕСЛИ» с двумя условиями в Excel

Данное руководство покажет вам, как применять функции COUNTIFS и COUNTIF для подсчета ячеек в зависимости от нескольких критериев, с учетом логики И и логики ИЛИ.

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

Функции COUNTIF и COUNTIFS часто путают между собой, поскольку они схожи по назначению, но имеют ключевые отличия. COUNTIF позволяет считать ячейки по одному критерию в одном диапазоне, в то время как COUNTIFS может работать с несколькими критериями как для одного диапазона, так и для разных.

Синтаксис функции COUNTIFS

Формула функции COUNTIFS выглядит следующим образом:

COUNTIFS(criteria_range1; criteria1; [criteria_range2; criteria2]…)

1. criteria_range1 — это диапазон, к которому применяется первое условие.
2. criteria1 — это условие, которое может быть выражено как число, текст, ссылка на ячейку или логическое выражение (например, ">10", "Яблоки").
3. [criteria_range2, criteria2] — дополнительные диапазоны и их условия (до 127 пар "диапазон-критерий").

Microsoft Excel отображает аргументы функции в процессе ввода, что помогает избежать ошибок.

Важные особенности функции COUNTIFS

1. Функция может подсчитывать ячейки на основе одного или нескольких условий. В случае нескольких условий будут подсчитаны только те ячейки, которые удовлетворяют всем указанным критериям.
2. Все диапазоны должны иметь одинаковые размеры по числу строк и столбцов.
3. Поддерживаются как смежные, так и несмежные диапазоны.
4. Если критерий ссылается на пустую ячейку, Excel считает это значение равным нулю.
5. Символы подстановки, такие как * и ?, могут использоваться для поиска данных по шаблону.

Примеры использования функций COUNTIF и COUNTIFS с несколькими условиями

Пример 1: COUNTIFS с несколькими критериями (логика И)

Предположим, что у вас есть таблица товаров, и вы хотите узнать количество товаров, которые есть на складе (в столбце B значения больше 0), но при этом они еще не проданы (в столбце C значения равны 0).

Формула для решения этой задачи будет следующей:

=COUNTIFS(B2:B7;">0"; C2:C7;"=0")

Результат этой формулы — 2, что означает, что на складе имеются "Вишня" и "Лимоны".

Пример 2: COUNTIFS с одинаковыми критериями

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

=COUNTIFS($B$2:$B$7,"=0"; $C$2:$C$7;"=0")

Результат — 1, так как только "Виноград" имеет значение 0 в обоих столбцах.

Попытка использовать более простую формулу вида COUNTIFS(B2:C7;"=0") вернет общее количество ячеек, содержащих ноль (в данном примере это будет 4).

Подсчет ячеек с логикой ИЛИ

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

Пример 1: Сложение нескольких формул COUNTIF

Предположим, что вы хотите подсчитать заказы со статусом "Отменен" и "В ожидании". Для этого можно сложить две отдельные формулы COUNTIF:

=COUNTIF($C$2:$C$11;"Отменен") + COUNTIF($C$2:$C$11;,"В ожидании")

Если требуется добавить дополнительные условия, используйте COUNTIFS. Например, для подсчета заказов "Яблок" со статусами "Отменен" и "В ожидании":

=COUNTIFS($A$2:$A$11; "Яблоки"; $C$2:$C$11;Отменен") + COUNTIFS($A$2:$A$11; "Яблоки"; $C$2:$C$11;"В ожидании")

Пример 2: SUM COUNTIFS с массивом критериев

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

=SUM(COUNTIFS($C$2:$C$11; {"Отменен"; "В ожидании";"В пути"}))

Чтобы подсчитать количество заказов "Яблок" со статусами "Отменен", "В ожидании" или "В пути", формула будет следующей:

=SUM(COUNTIFS($A$2:$A$11;"Яблоки";$C$2:$C$11,{"Отменен";"В ожидании";"В пути"}))

Как считать числа между двумя заданными значениями

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

Это можно реализовать двумя способами: используя функцию COUNTIFS либо путем вычитания одной формулы COUNTIF из другой.

Формула 1. COUNTIFS для подсчета значений между двумя числами

Чтобы узнать, сколько чисел находятся в диапазоне от 5 до 10 (исключая сами 5 и 10) в диапазоне ячеек от C2 до C10, можно воспользоваться следующей формулой:

=COUNTIFS(C2:C10;">5";C2:C10;"<10")

Для того чтобы включить в расчет числа 5 и 10, используйте операторы "больше или равно" и "меньше или равно":

=COUNTIFS(C2:C10;">=5";C2:C10;"<=10")

Формула 2. COUNTIF для подсчета значений между X и Y

Другим способом подсчета является вычитание одной формулы COUNTIF из другой. В первом случае функция подсчитает количество значений, которые превышают нижнюю границу (например, 5), а вторая — количество значений, которые превышают верхнюю границу (в данном случае 10).

Разница между результатами и даст требуемое количество значений:

=COUNTIF(C2:C10;">5")-COUNTIF(C2:C10;">=10")

Эта формула вернет результат, показывающий, сколько чисел больше 5 и меньше 10 содержится в диапазоне C2:C10, аналогично предыдущей формуле.

Для включения в расчет чисел 5 и 10 можно использовать следующую формулу:

=COUNTIF(C2:C10;">=5")-COUNTIF(C2:C10;">10")

Эта формула посчитает количество значений, которые лежат между 5 и 10 в диапазоне C2:C10, включая сами числа 5 и 10.

Использование ссылок на ячейки в формуле COUNTIFS

Когда вы используете логические операторы, такие как ">", "<", ">=" или "<=", в комбинации с ссылками на ячейки в формуле COUNTIFS, важно помнить, что логический оператор должен быть заключен в кавычки, а перед ссылкой на ячейку необходимо добавить амперсанд (&) для преобразования условия в строку.

Рассмотрим следующий пример: вы хотите подсчитать заказы на "Яблоки" с суммой заказа, превышающей 200 долларов. Используя данные из диапазонов A2:A11 и B2:B11, можно применить следующую формулу:

=COUNTIFS($A$2:$A$11;"Яблоки";$B$2:$B$11;">200")

Также можно ввести критерии в ячейки, например F1 и F2, и затем ссылаться на них в формуле:

=COUNTIFS($A$2:$A$11;$F$1;$B$2:$B$11;">"&$F$2)

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

Как использовать подстановочные знаки в COUNTIFS

Excel поддерживает использование подстановочных символов в формуле COUNTIFS:

• Вопросительный знак (?) — заменяет один любой символ. Используется для подсчета ячеек, начинающихся или заканчивающихся определенными символами.
• Звездочка (*) — соответствует любому количеству символов. С ее помощью можно подсчитать ячейки, содержащие определенное слово или набор символов.

Подсказка: если нужно учитывать реальные вопросительные знаки или звездочки, перед ними следует поставить символ тильда (~).

Например, если у вас есть список проектов в столбце A, и вы хотите подсчитать, сколько проектов уже распределены между сотрудниками (т.е. в столбце B есть какое-то имя), а в столбце D установлена дата завершения, используйте следующую формулу:

=COUNTIFS(B2:B10;"*",D2:D10;"<>"&"")

Обратите внимание: подстановочные символы нельзя использовать для числовых данных, таких как даты. Поэтому для поиска непустых ячеек в столбце D используется критерий "<>"&"".

COUNTIFS и COUNTIF с несколькими критериями для дат

Формулы COUNTIFS и COUNTIF для дат похожи на те, что используются для чисел.

Пример 1. Подсчет дат в определенном диапазоне

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

Например, следующие формулы подсчитают количество дат в диапазоне C2:C10, которые лежат между 1 июня 2014 года и 7 июня 2014 года:

=COUNTIFS(C2:C10;">=6/1/2014";C2:C10;"<=6/7/2014")

=COUNTIF(C2:C10;">=6/1/2014")-COUNTIF(C2:C10;">6/7/2014")

Пример 2. Подсчет дат с несколькими условиями

Подобным образом можно использовать COUNTIFS для подсчета дат из разных столбцов, удовлетворяющих нескольким условиям. Например, следующая формула подсчитает, сколько товаров было куплено после 1 мая и доставлено после 7 июня:

=COUNTIFS(C2:C10;">5/1/2014";D2:D10;">6/7/2014")

Пример 3. Подсчет дат с использованием текущей даты

С помощью функции TODAY() в сочетании с COUNTIF можно подсчитывать даты на основе текущей даты.

Вот пример формулы COUNTIFS, которая подсчитает количество товаров, купленных до сегодняшнего дня, но еще не доставленных:

=COUNTIFS(C2:C10;"<"&TODAY();D2:D10,">"&TODAY())

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

=COUNTIFS(C2:C10;"<="&TODAY()-7;D2:D10;">"&TODAY())

Таким образом, используя различные комбинации формул COUNTIF и COUNTIFS, вы сможете легко подсчитывать данные с несколькими условиями в Excel.

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

Заключение

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