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

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

В качестве исходной таблицы возьмем таблицу с двумя столбцами: текстовым «Фрукты» и числовым «Количество на складе» (См. файл примера).

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

СУММЕСЛИМН(диапазон_условия1; условие1; [диапазон_условия2; условие2]…)

  • Диапазон_условия1. Первый диапазон, в котором необходимо проверить соответствие заданному условию1;
  • Условие1. Условие в форме числа, выражения, ссылки на ячейку или текста, которые определяют, какие ячейки требуется учитывать. Например, условие может быть выражено следующим образом: 32, ">32", B4 или "яблоки";
  • Диапазон_условия2, условие2...    Необязательные аргументы. Дополнительные диапазоны и условия для них. Разрешается использовать до 127 пар диапазонов и условий.

Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и аргумент диапазон_условия1. Эти диапазоны могут не находиться рядом друг с другом.

В условиях можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (~).

Задача1 (2 числовых критерия)

Рассмотрим задачу, когда 2 числовых критерия применяются к значениям одного из столбцов (см. файл примера, лист 2числовых).

Найдем число партий товара с Количеством ящиков на складе не менее 10 и не более 50 (строка таблицы соответствует критерию, когда ее поле Количество ящиков на складе удовлетворяет обоим критериям одновременно). 

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =И($B2>=$D$2;$B2<=$E$2)

Формулу запишем в следующем виде: =СЧЁТЕСЛИМН(B2:B13;">="&D2;B2:B13;"<="&E2)

В формуле предполагается, что диапазон, к которому применяется первый критерий (>=10 или ">="&D2) это диапазон B2:B13. Первый и второй диапазон в данном случае совпадают, т.к. 2-й критерий  (<=50 или "<="&E2) применяется к тому же диапазону B2:B13.

Альтернативными решениями задачи являются следующие формулы:

  • =СУММПРОИЗВ((B2:B13>=D2)*(B2:B13<=E2))
  • формула массива =СУММ((B2:B13<=E2)*(B2:B13>=D2))
  • формула массива =СЧЁТ(ЕСЛИ((B2:B13>=D2)*(B2:B13<=E2);B2:B13))
  • =БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют наличия отдельной таблички с критериями.

Задача2 (2 критерия в формате Дат)

Рассмотрим задачу, когда 2 критерия заданы в форме дат и применяются к значениям одного из столбцов.

Найдем число партий товара с Датой поступления на склад не ранее 25.10.2012 и не позднее 24.12.2012 (строка таблицы соответствует критерию, когда ее поле Дата поступления удовлетворяет обоим критериям одновременно). 

В качестве исходной таблицы возьмем таблицу с двумя столбцами: текстовым «Фрукты» и "Дата поступления" (см. файл примера, лист 2Даты).

Так как даты хранятся в EXCEL в числовом формате, то формулы для подсчета не изменятся (см. задачу 1).

Задача3 (1 текстовый критерий, другой числовой)

Рассмотрим задачу, когда 1 текстовый критерий применяются к значениям текстового столбца, а другой (числовой) - к значениям столбца с числами (см. файл примера, лист 1текст 1числовой).

Найдем число партий товара Яблоки с Количеством ящиков на складе не менее 10 (строка таблицы соответствует критерию, когда ее поле Фрукт совпадает с критерием Яблоки, и когда другое поле Количество ящиков на складе >=10). 

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Формулу запишем в следующем виде: =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;">="&E2)

В формуле предполагается, что диапазон, к которому применяется первый критерий (>=10 или ">="&D2) это диапазон B2:B13. Первый и второй диапазон в данном случае совпадают, т.к. 2-й критерий  (<=50 или "<="&E2) применяется к тому же диапазону B2:B13.

Альтернативными решениями задачи являются следующие формулы:

  • =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2))
  • формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))
  • формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13))
  • =БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют наличия отдельной таблички с критериями.

Задача4 (1 текстовый критерий с подстановочным знаком, другой числовой)

Рассмотрим задачу, когда 1 текстовый критерий с подстановочным знаком применяются к значениям текстового столбца, а другой (числовой) - к значениям столбца с числами (см. файл примера, лист 1текст (с подстанов) 1числовой).

Найдем число партий товара начинающихся со слова Яблоки и с Количеством ящиков на складе не менее 10

В отличие от задачи 3, в исходной таблице присутствуют фрукты с более сложными названиями: яблоки свежие, персики сорт2. Чтобы одновременно подсчитать партии товара Яблоки и Яблоки свежие нужно использовать подстановочные знаки.

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

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

Задача5 (1 текстовый критерий, 2 числовых)

Рассмотрим задачу, когда 1 текстовый критерий применяются к значениям текстового столбца, а 2 других (числовых) - к значениям столбца с числами (см. файл примера, лист 1текст 2числовых).

Найдем число партий товара Яблоки с Количеством ящиков на складе не менее 10 и не более 90 (строка таблицы соответствует критерию, когда ее поле Фрукт совпадает с критерием Яблоки, и когда другое поле Количество ящиков на складе одновременно >=10 и <=90). 

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2;$B2<=$F$2)

Формулу запишем в следующем виде: =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;">="&E2;B2:B13;"<="&F2)

Альтернативными решениями задачи являются следующие формулы:

  • =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13<=F2))
  • формула массива =СУММ((A2:A13=D2)*(B2:B13<=F2)*(B2:B13>=E2))
  • формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13<=F2);B2:B13))
  • =БСЧЁТА(A1:B13;A1;D14:F15) или БСЧЁТ(A1:B13;B1;D14:F15), которые требуют наличия отдельной таблички с критериями.

О подсчете с множественными критериями можно почитать в этом разделе.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 3.8 (6 оценок)