Функция СЧЁТЕСЛИМН() в 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) , которые требуют наличия отдельной таблички с критериями.

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