Как использовать несколько условий в Excel COUNTIF и COUNTIFS

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

В этом руководстве рассматриваются различные способы использования одного или нескольких критериев в функциях Excel СЧЁТЕСЛИ и СЧЁТЕСЛИМН.

Хотя в этом руководстве я сосредоточусь в первую очередь на функциях СЧЁТЕСЛИ и СЧЁТЕСЛИМН, все эти примеры также можно использовать с другими функциями Excel, принимающими в качестве входных данных несколько критериев (таких как СУММЕСЛИ, СУММЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН).

Знакомство с функциями Excel СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Давайте сначала разберемся, как использовать функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН в Excel.

Функция Excel СЧЁТЕСЛИ (с одним критерием)

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

синтаксис

=СЧЁТЕСЛИ(диапазон, условие)

Входные параметры

• Объем –  Диапазон ячеек, которые вы хотите рассчитать.
• состояние –Критерии, которые должны оцениваться по диапазону ячеек, подлежащих подсчету.

Функция Excel СЧЁТЕСЛИМН (с несколькими критериями)

Функция Excel СЧЁТЕСЛИМН лучше всего работает, когда вы хотите подсчитать количество ячеек на основе нескольких критериев.

синтаксис

=COUNTIFS(диапазон_критериев1,диапазон_критериев1,[диапазон_критериев2,критерий2]…)

Входные параметры

• диапазон_критериев1 – Диапазон ячеек, которые вы хотите оценить по критерию 1.
• критерии1 – Критерием, который вы хотите оценить, является критерий_диапазон1, чтобы определить, какие ячейки следует оценивать.
• [критерии_диапазона2] – Диапазон ячеек, которые вы хотите оценить по критерию 2.
• [критерий2] — Критерий, который вы хотите оценить, — это критерий_диапазона2, чтобы определить, какие ячейки следует подсчитывать.

Теперь давайте рассмотрим несколько примеров использования нескольких критериев в функции Excel СЧЁТЕСЛИ.

Используйте условие ЧИСЛО в функции Excel СЧЁТЕСЛИ

#1 Считать ячейки, когда критерий равен значению

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

Ниже приведен пример, где мы считаем ячейки, содержащие число 9 (что означает, что параметр условия равен 9).Вот формула:

=СЧЁТЕСЛИ($B$2:$B$11;D3)

В примере выше (на картинке) критерий находится в ячейке D3.Вы также можете вводить условия непосредственно в формулы.Например, вы также можете использовать:

=СЧЁТЕСЛИ($B$2:$B$11,9)

#2 Считать ячейки, когда условие больше значения

Чтобы получить количество ячеек со значением, превышающим указанное значение, мы используем оператор «больше» («>»).Мы можем использовать его непосредственно в формулах или использовать ссылки на ячейки с условиями.

Всякий раз, когда мы используем оператор в условии в Excel, нам нужно поместить его в двойные кавычки.Например, если условие больше 10, то нам нужно ввести «> 10» в качестве условия (см. изображение ниже):

Вот формула:

=СЧЁТЕСЛИ($B$2:$B$11”>10″)

Вы также можете включать условия в ячейки и использовать ссылки на ячейки в качестве условий.В этом случае вам не нужно заключать условие в двойные кавычки:

=СЧЁТЕСЛИ($B$2:$B$11;D3)

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

В этом случае вам необходимо создать условный параметр, который представляет собой комбинацию оператора и ссылки на ячейку (см. изображение ниже):

=СЧЁТЕСЛИ($B$2:$B$11">"&D3)

Примечание. При объединении операторов и ссылок на ячейки операторы всегда заключаются в двойные кавычки.Операторы и ссылки на ячейки соединяются амперсандом (&).

#3 Считать ячейки, когда критерий меньше значения

Чтобы получить количество ячеек со значением меньше указанного значения, мы используем оператор «меньше» («<»).Мы можем использовать его непосредственно в формулах или использовать ссылки на ячейки с условиями.

Всякий раз, когда мы используем оператор в условии в Excel, нам нужно поместить его в двойные кавычки.Например, если критерий состоит в том, что количество должно быть меньше 5, то нам нужно ввести «<5» в качестве критерия (см. изображение ниже):

=СЧЁТЕСЛИ($B$2:$B$11"<5")

Вы также можете включать условия в ячейки и использовать ссылки на ячейки в качестве условий.В этом случае вам не нужно заключать стандарт в двойные кавычки (см. изображение ниже):

=СЧЁТЕСЛИ($B$2:$B$11;D3)

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

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

=СЧЁТЕСЛИ($B$2:$B$11,"<"&D3)

Примечание. При объединении операторов и ссылок на ячейки операторы всегда заключаются в двойные кавычки.Операторы и ссылки на ячейки соединяются амперсандом (&).

#4 Подсчет ячеек с несколькими критериями — между двумя значениями

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

Вот два способа сделать это:

Способ XNUMX: используйте функцию СЧЁТЕСЛИМН

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

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

Примечание. Приведенная выше формула не учитывает ячейки, содержащие 5 или 10.Если вы хотите включить эти ячейки, используйте операторы больше или равно (>=) и меньше или равно (<=).Вот формула:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

Вы также можете включить эти условия в ячейки и использовать ссылки на ячейки в качестве условий.В этом случае вам не нужно заключать стандарт в двойные кавычки (см. изображение ниже):

Вы также можете использовать комбинацию ссылок на ячейки и операторов (ввод операторов непосредственно в формулы).При объединении операторов и ссылок на ячейки операторы всегда заключаются в двойные кавычки.Операторы и ссылки на ячейки соединяются амперсандом (&).

Способ 2: используйте две функции СЧЁТЕСЛИ

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

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

В приведенной выше формуле мы сначала находим количество ячеек со значением больше 5, а затем вычитаем количество ячеек со значением больше 10.Это даст нам результат 5 (т.е. со значениями больше 5 и меньше или равно 10).

Если вы хотите, чтобы формула содержала как 5, так и 10, используйте вместо этого следующую формулу:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

Если вы хотите, чтобы формула исключала «5» и «10» из подсчета, используйте следующую формулу:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

Вы можете включить эти условия в ячейки и использовать ссылки на ячейки или использовать комбинацию операторов и ссылок на ячейки.

Использование условий TEXT в функциях Excel

#1 Считать ячейки, когда условие равно указанному тексту

Чтобы подсчитать ячейки, которые содержат точное совпадение с указанным текстом, мы можем просто использовать этот текст в качестве условия.Например, в наборе данных (показанном ниже), если бы я хотел подсчитать все ячейки с именем Джо, я мог бы использовать следующую формулу:

=СЧЁТЕСЛИ($B$2:$B$11;"Джо")

Поскольку это текстовая строка, мне нужно поместить текстовое условие в двойные кавычки.

Вы также можете включить условие в ячейку, а затем использовать ссылку на эту ячейку (как показано ниже):

=СЧЁТЕСЛИ($B$2:$B$11,E3)

Примечание. Вы можете получить неправильные результаты, если в условии или диапазоне условий есть начальные/конечные пробелы.Обязательно очистите данные перед использованием этих формул.

#2 Считать ячейки, когда условие не равно заданному тексту

Подобно тому, что мы видели в примере выше, вы также можете подсчитывать ячейки, которые не содержат указанного текста.Для этого нам нужно использовать оператор не равно (<>).

Предположим, вы хотите подсчитать все ячейки, не содержащие имя JOE, вот формула, которая может это сделать:

=СЧЁТЕСЛИ($B$2:$B$11,"<>Джо")

Вы также можете включать условия в ячейки и использовать ссылки на ячейки в качестве условий.В этом случае вам не нужно заключать стандарт в двойные кавычки (см. изображение ниже):

=СЧЁТЕСЛИ($B$2:$B$11,E3)

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

В этом случае вам необходимо создать условный параметр, который представляет собой комбинацию оператора и ссылки на ячейку (см. изображение ниже):

=СЧЁТЕСЛИ($B$2:$B$11",<>"&E3)

При объединении операторов и ссылок на ячейки операторы всегда заключаются в двойные кавычки.Операторы и ссылки на ячейки соединяются амперсандом (&).

Использование условий DATE в функциях Excel COUNTIF и COUNTIFS

Excel хранит дату и время в виде чисел.Так что мы можем использовать его как число.

#1 Подсчет ячеек, когда условие равно указанной дате

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

Для работы с датами я рекомендую использовать функцию DATE, так как она устраняет любую вероятность неправильного значения даты.Так, например, если бы я хотел использовать дату 2015 сентября 9 года, я мог бы использовать функцию DATE следующим образом:

= ДАТА (2015,9,1)

Несмотря на региональные различия, эта формула будет возвращать одну и ту же дату.Например, 01-09-2015 будет 2015 сентября 9 года в синтаксисе даты в США и 1 января 2015 года в синтаксисе даты в Великобритании.Однако эта формула всегда будет возвращать 2105 сентября 9 года.

Вот формула для подсчета количества ячеек, содержащих дату 02-09-2015:

=СЧЁТЕСЛИ($A$2:$A$11,ДАТА(2015,9,2))

# 2 Подсчет ячеек, когда условие до или после указанной даты

Для подсчета ячеек, содержащих даты до или после указанной даты, мы можем использовать операторы «меньше/больше».

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

=СЧЁТЕСЛИ($A$2:$A$11",>"&ДАТА(2015,9,2))

Точно так же вы также можете подсчитать количество ячеек до указанной даты.Если вы хотите включить в подсчет даты, используйте оператор «равно» вместе с операторами «больше/меньше».

Вы также можете использовать ссылки на ячейки, содержащие даты.В этом случае вам нужно использовать амперсанд (&), чтобы объединить оператор (в двойных кавычках) с датой.

См. пример ниже:

=СЧЁТЕСЛИ($A$2:$A$11">"&F3)

# 3 Подсчет ячеек с несколькими критериями — между двумя датами

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

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

Способ XNUMX: используйте функцию СЧЁТЕСЛИМН

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

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

Приведенная выше формула не учитывает ячейки, содержащие указанную дату.Если вы также хотите включить эти даты, используйте операторы больше или равно (>=) и меньше или равно (<=).Вот формула:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

Вы также можете включать даты в ячейки и использовать ссылки на ячейки в качестве критериев.В этом случае оператор не может включать дату в ячейку.Вам необходимо вручную добавить в формулу операторы (заключенные в двойные кавычки) и использовать амперсанд (&) для добавления ссылок на ячейки.Увидеть ниже:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

Способ 2: используйте функцию СЧЁТЕСЛИ

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

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

В приведенной выше формуле мы сначала находим количество ячеек с датой после 9 сентября, а затем вычитаем количество ячеек с датой после 2 сентября.Это даст результат 9 (то есть количество ячеек с датами после 7 сентября и до 7 сентября).

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

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

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

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

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

Использование подстановочных знаков в условиях функций СЧЁТЕСЛИ и СЧЁТЕСЛИМН

В Excel есть три подстановочных знака:

1. *(звездочка) - представляет любое количество символов.Например, ex* может означать excel, excels, пример, эксперт и т. д.
2. ? (вопросительный знак) - Он представляет собой один символ.Например, Тромп может означать Трампа или Бродягу.
3. ~ (тильда) - Используется для обозначения подстановочных знаков (~, *, ?) в тексте.

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

Теперь давайте возьмем несколько примеров:

#1 Подсчет ячеек, содержащих текст

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

=СЧЁТЕСЛИМН($C$2:$C$11,"*")

Примечание. В приведенной выше формуле игнорируются ячейки, содержащие числа, пустые ячейки и логические значения, но учитываются ячейки, содержащие апострофы (и поэтому кажущиеся пустыми) или те, которые могли быть включены в формулу.

Вот подробное руководство по обработке случаев с пустыми строками или апострофами.

#2 Подсчет непустых ячеек

Если вы планируете использовать функцию COUNTA, подумайте еще раз.

Попробуйте, может вас разочарует.COUNTA также подсчитывает ячейки, содержащие пустые строки (обычно возвращаемые формулами как =", или когда люди просто вводят апострофы в ячейки).Ячейки, содержащие пустые строки, кажутся пустыми, но это не так, поэтому они подсчитываются функцией СЧЕТЧ.

COUNTA также подсчитывает ячейки, содержащие пустые строки (обычно возвращаемые формулами как =", или когда люди просто вводят апострофы в ячейки).Ячейки, содержащие пустые строки, кажутся пустыми, но это не так, поэтому они подсчитываются функцией СЧЕТЧ.

Итак, если вы используете формулу = COUNTA (A1:A11), он возвращает 11, хотя должен возвращать 10.

Вот исправление:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

Давайте поймем эту формулу, разбив ее:

#3 Подсчет ячеек, содержащих определенный текст

Предположим, мы хотим подсчитать все ячейки, в которых имя торгового представителя начинается с J.Этого легко добиться, используя подстановочные знаки в функции СЧЁТЕСЛИ.Вот формула:

=СЧЁТЕСЛИМН($C$2:$C$11,"J*") 

Условие J* указывает, что текст в ячейке должен начинаться с J и может содержать любое количество символов.

Если вы хотите подсчитать ячейки, содержащие буквы в любом месте текста, окружите их звездочкой.Например, если вы хотите посчитать ячейки, содержащие букву «а», используйте *a* в качестве условия.

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