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

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

Начиная с версии Excel 2007, Microsoft добавила в Excel «старших сестер» функциям выборочного подсчета СУММЕСЛИ, СЧЁТЕСЛИ и СРЗНАЧЕСЛИ – функции СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЗНАЧЕСЛИМН. В английском варианте эти функции выглядят как SUMIFS, COUNTIFS и AVERAGEIFS, т.е. имеют на конце букву -S, обозначающую в английском языке множественное число. В русской версии эту роль играет -МН.

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

Разница в том, что СЧЕТЕСЛИ предназначен для подсчета ячеек с одним условием в одном диапазоне, тогда как СЧЕТЕСЛИМН может оценивать разные критерии в одном и том же или в разных диапазонах.

Как работает функция СЧЕТЕСЛИМН?

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

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

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

• диапазон1 (обязательный) - определяет первую область, к которой должно применяться первое условие ( условие1).
• условие1 (обязательное) - устанавливает требование к отбору в виде числа , ссылки на ячейку , текстовой строки , выражения или другой функции Excel. Определяет, какие ячейки должны учитываться.
• [диапазон2;условие2]… (необязательные) - это дополнительные области и связанные с ними критерии. Вы можете указать до 127 таких пар.

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

Что нужно запомнить?

1. Диапазонов поиска может быть от 1 до 127. Для каждого из них указывается свое условие. Учитываются только те случаи, которые отвечают всем предъявленным требованиям.
2. Каждый дополнительный диапазон должен иметь одинаковое число строк и столбцов с первым. Иначе получите ошибку #ЗНАЧ!
3. Допускаются как смежные, так и несмежные диапазоны.
4. Если в аргументе указана ссылка на пустую ячейку , функция обрабатывает его как нулевое значение (0).
5. В критериях можно использовать символы подстановки - звездочка (*) и знак вопроса (?). Далее мы расскажем об этом подробнее.

Считаем с учетом всех критериев (логика И).

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

а. Для каждого диапазона - свой критерий.

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

Задача может быть выполнена таким образом:

=СЧЁТЕСЛИМН(B2:B11;G1;D2:D11;G2)

или

=СЧЁТЕСЛИМН(B2:B11;">0";D2:D11;0)

Видим, что 2 товара (крыжовник и ежевика) находятся на складе, но не продаются.

б. Одинаковый критерий для всех диапазонов.

Если вы хотите посчитать элементы с одинаковыми критериями, вам все равно нужно указывать каждую пару диапазон/условие отдельно.

Например, вот правильный подход для подсчета элементов, которые имеют 0 как в столбце B, так и в столбце D:

=СЧЁТЕСЛИМН(B2:B11;0;D2:D11;0)

Получаем 1, потому что только Слива имеет значение «0» в обоих столбцах.

Использование упрощенного варианта с одним ограничением выбора, например =СЧЁТЕСЛИМН(B2:D11;0), даст другой результат - общее количество ячеек в B2: D11, содержащих ноль (в данном примере это 5).

Если достаточно выполнения хотя бы одного условия (логика ИЛИ).

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

Но что если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий имеет значение ИСТИНА , то есть использовать логику ИЛИ? В принципе, есть два способа сделать это - 1) сложив несколько формул СЧЕТЕСЛИ или 2) использовать комбинацию СУММ+СЧЕТЕСЛИМН с константой массива.

Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.

Подсчитаем заказы со статусами «Отменено» и «Ожидание». Чтобы сделать это, вы можете просто написать 2 обычные формулы СЧЕТЕСЛИ и затем сложить результаты:

=СЧЁТЕСЛИ(E2:E11;"Отменено")+СЧЁТЕСЛИ(E2:E11;"Ожидание")

В случае, если нужно оценить более одного параметра отбора, используйте СЧЕТЕСЛИМН.

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

=СЧЁТЕСЛИМН(A2:A11;"клубника";E2:E11;"Отменено")+СЧЁТЕСЛИМН(A2:A11;"клубника";E2:E11;"Ожидание")

Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.

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

Вставьте СЧЕТЕСЛИМН в функцию СУММ, вот так:

СУММ(СЧЁТЕСЛИМН(диапазон;{"условие1";"условие2";"условие3";…}))

В нашей таблице с примерами для подсчета заказов со статусом «Отменено» или «Ожидание» расчет будет выглядеть следующим образом:

=СУММ(СЧЁТЕСЛИМН(E2:E11;{"Отменено";"Ожидание"}))

Массив означает, что в начале ищем все отмененные заказы, потом ожидающие. Получается массив из двух цифр итогов. А затем функция СУММ просто их складывает.

Аналогичным образом вы можете использовать две или более пары диапазон/условие. Чтобы вычислить количество заказов на клубнику, которые отменены или в стадии ожидания, используйте это выражение:

=СУММ(СЧЁТЕСЛИМН(A2:A11;"Клубника";E2:E11;{"Отменено";"Ожидание"}))

Как сосчитать числа в интервале.

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

1. СЧЕТЕСЛИМН для подсчета ячеек между двумя числами

Чтобы узнать, сколько было получено заказов количеством товара от 10 до 20, сделаем так:

=СЧЁТЕСЛИМН(D2:D11;">10";D2:D11;"<20")

2. СЧЕТЕСЛИ для подсчета в интервале

Тот же результат может быть достигнут путем вычитания одной формулы СЧЕТЕСЛИ из другой. Сначала считаем, сколько чисел больше, чем значение нижней границы интервала (10 в этом примере). Вторая возвращает число заказов, превышающее верхнее граничное значение (в данном случае 20). Разница между ними - результат, который вы ищете.

=СЧЁТЕСЛИ(D2:D11;">10")-СЧЁТЕСЛИ(D2:D11;">20")

Это выражение будет возвращать то же количество, как показано на рисунке выше.

Как использовать ссылки в формулах СЧЕТЕСЛИМН.

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

рис6

В приведенном примере посчитаем заказы с количеством более 30 единиц, при том что на складе в наличии было менее 50 единиц товара.

=СЧЁТЕСЛИМН(B2:B11;"<50";D2:D11;">30")

или

=СЧЁТЕСЛИМН(B2:B11;"<"&G1;D2:D11;">"&G2)

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

Как использовать СЧЕТЕСЛИМН со знаками подстановки.

Традиционно можно применять следующие символы подстановки:

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

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

Теперь давайте посмотрим, как вы можете использовать символ подстановки.

Предположим, у вас есть список заказов, за которыми персонально закреплены менеджеры. Вы хотите знать, сколько заказов уже кому-то назначено и при этом установлен срок их выполнения. Иначе говоря, имеются ли какие-то значения в столбцах B и Е таблицы.

Нам необходимо узнать количество заказов, для которых заполнены столбцы B и Е:

=СЧЁТЕСЛИМН(B2:B21;"*";E2:E21;"<>"&"")

Обратите внимание, что в первом критерии мы используем знак подстановки *, поскольку рассматриваем текстовые значения (фамилии). Во втором критерии мы анализируем даты, поэтому и записываем его иначе: "<>"&"" (означает - не равно пустому значению).

Несколько условий в виде даты.

Правила работы с датами очень похожи на рассмотренные выше вычисления с числами.

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

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

Следующие выражения подсчитывают в области с D2 по D21 количество дат, приходящихся на период с 1 по 7 февраля 2020 года включительно:

=СЧЁТЕСЛИМН(D2:D21;">=01.02.2020";D2:D21;"<=07.02.2020")

или

=СЧЁТЕСЛИМН(D2:D21;">="&H3;D2:D21;"<="&H4)

2. Подсчет на основе нескольких дат.

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

Как обычно, запишем двумя способами: со ссылками и без них:

=СЧЁТЕСЛИМН(D2:D21;">="&H3;E2:E21;">="&H4)

и

=СЧЁТЕСЛИМН(D2:D21;">=01.02.2020";E2:E21;">=05.02.2020")

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

Вы можете использовать функцию СЕГОДНЯ() для подсчета дат по отношению к сегодняшнему дню.

Эта формула с двумя областями и двумя критериями ответит вам, сколько товаров уже куплено, но еще не доставлено.

=СЧЕТЕСЛИ(D2:D21;"<"&СЕГОДНЯ();E2:E21;">"&СЕГОДНЯ())

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

=СЧЕТЕСЛИ(D2:D21;"<"&СЕГОДНЯ()-7;E2:E21;">"&СЕГОДНЯ())

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

Я надеюсь, что вы найдете эти примеры и советы полезными. В любом случае, я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге ещё не раз.

Также рекомендуем:

Формула ПРОМЕЖУТОЧНЫЕ ИТОГИ — основные функции с примерами.-В статье объясняются особенности функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel и показано, как использовать формулы промежуточных итогов для суммирования данных в видимых ячейках. В предыдущей статье мы обсудили автоматический способ вставки промежуточных…

Промежуточные итоги в Excel-В руководстве объясняется, как использовать инструмент промежуточных итогов Excel для автоматического суммирования, подсчета или усреднения различных групп ячеек. Вы также узнаете, как отображать или скрывать детали промежуточных итогов, копировать только строки…

Как проверить правильность ввода данных в Excel?-Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить…

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

Самая простая функция СЧЁТЕСЛИ означает следующее:

• =СЧЁТЕСЛИ(где нужно искать;что нужно найти)

Например:

• =СЧЁТЕСЛИ(A2:A5;"Лондон")

• =СЧЁТЕСЛИ(A2:A5;A4)

Примеры

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

Распространенные неполадки

Рекомендации

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Для подсчета непустых ячеек используйте функцию СЧЁТЗ

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

Просмотрите видео об использовании функции СЧЁТЕСЛИМН

Функция СУММЕСЛИ суммирует только те значения, которые соответствуют одному условию

Функция СУММЕСЛИМН суммирует только те значения, которые соответствуют нескольким условиям

Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздних)

Для удаления начальных и конечных пробелов в ячейках используйте функцию СЖПРОБЕЛЫ

Для удаления непечатаемых символов в ячейках используйте функцию ПЕЧСИМВ

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах

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

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Использование критериев в Microsoft Excel

​Смотрите также​ (работ у субъекта​ кажется, видна проблема,​ C D N​ я сохранять у​китин​ работал сверх нормы​CTRL+SHIFT+L. ​ критериям, выделяются Условным​ инструментов, использующих при​.​ торговых точках. Нам​ 11.03.2017.​ Магазина 1. Они​ за неделю. Для​ предыдущего. Синтаксис следующий:​ как работает данный​

Применение критериев

​ которую пытаюсь решить...​ либо "все не​ себя файлы....)​: где?​ (более 8 часов)​Выберите числовой фильтр Между.​ форматированием с правилом =И($A2=$D$2;$B2>=$E$2)​ своей работе критерии,​​После выполнения последнего действия​​ нужно будет подсчитать​Выделяем ячейку, в которой​ расположены в строке,​​ этого ставим курсор​​=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…)​​ оператор на практике.​​ не просто табличным​​ 20 как вы​​Leanna​​ пустые кроме NL​​Hugo​Pelena​ в период с​Введите критерии​Подсчет можно реализовать множеством​ в Экселе можно​ клацаем по кнопке​ доход, который принес​

СЧЁТЕСЛИ

​ будет производиться вывод​​ которая так и​​ в поле и​«Диапазон условия»​Итак, имеется таблица, где​ редактором, а ещё​ посчитали), смотрите формулы​: На крайний случай​

​: так разве не​: Сводная не подойдет?​​ 03.08.2018 по 14.08.2018.​​Убедитесь, что результат такой​ формул, приведем несколько:​ решать довольно разноплановые​«OK»​

​ Магазин 1 за​​ результата. Щелкаем по​ называется​ выделяем соответствующую строку​является идентичным первому​ представлена выручка по​ и мощнейшим приложением​ в С1 и​ можно привести данные​Собственно по этой​ подходит (диапазон замените):​ Формулами тоже вариант​Вид таблицы данных:​ же как в​1. =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;">="&E2) Это решение является​ задачи. Это может​​.​​ период с 09​​ пиктограмме​​«Магазин 1»​​ в таблице. Координаты​​ аргументу предыдущего оператора.​​ пяти магазинам за​​ для различных вычислений.​​ С2, они работают​​ в нормальный вид​​ причине я использовал​​=СЧЁТЕСЛИ(A1:A13;"*замена*")+СЧЁТЕСЛИ(A1:A13;"*установка*")​​ в файле​​Для вычислений используем следующую​ задаче2 - т.е.​​ самым простым и​ быть, как подсчет​Программа выводит результат на​​ по 13 марта​​«Вставить функцию»​. После этого координаты​ отображаются в окне.​ То есть, он​ неделю. Нам нужно​ Не в последнюю​ по доп столбцу​ (через доп.лист как​ ту длиннющую формулу​dydyka​

​_Boroda_​ формулу:​ будет отобрано 7 строк​ понятным.​

​ сумм и значений,​ лист. Он равен​ 2017 года. При​.​ указанной области будут​Учитывая, что для Магазина​ представляет собой ссылку​ узнать количество дней​ очередь такая возможность​ C и D​

1. ​ в файле ниже)​ где СЧЁТЕСЛИМН применял​: СПАСИБО, если честно​: Так нужно?​02.08.2018";A2:A19;"8")' class='formula'>​​ (см. строку состояния​​2. =СУММПРОИЗВ(--(A2:A13=D2);--(B2:B13>=E2)) Это решение сложнее,​

   

2. ​ так и форматирование,​​ 62491,38. Это означает,​​ этом при суммировании​Перейдя в​​ отражены в окне.​​ 1 дневная норма​ на область, в​​ за этот период,​​ появилась благодаря встроенным​ и они не​​или через разъединение​​ для A+B+C+D+N.​

   

3. ​ думал проще будет,​200?'200px':''+(this.scrollHeight+5)+'px');">=СЧЁТЕСЛИМН(B$3:B$36;E15;C$3:C$36;">="&КОНМЕСЯЦА(F$3;-1)+1;C$3:C$36;"​В качестве первых двух​​ в нижней части​​ но позволяет понять​ а также выполнение​ что за период​ дохода должны учитываться​Мастер функций​Далее устанавливаем курсор в​​ выручки составляет 14000​​ которой будет производиться​ в которых в​ функциям. С помощью​ сходятся с цифрами​ ячеек и этот​rvp9321​ но этот вариант​и​ условий проверки указаны​ окна).​

   ​ работу функции СУММПРОИЗВ(),​​ многих других задач.​​ с 09 по​ только те дни,​в блоке​ поле​ рублей, то в​ подсчет ячеек, удовлетворяющих​ Магазине 2 доход​ некоторых функций (операторов)​ на которые равнятся​ прием , а​​: slamdunk, спасибо за​​ тоже отлично подошел​

   ​Код200?'200px':''+(this.scrollHeight+5)+'px');">=СЧЁТЕСЛИМН(B$3:B$40;E19)​ даты, которые автоматически​Примечание​​ которая может быть​​ Основными инструментами, работающими​

   

4. ​ 13 марта 2017​ выручка в которых​«Математические»​«Условие1»​ поле​​ указанным условиям. Данный​​ от реализации превысил​ можно задавать даже​ надо, я разобрался​ дальше обычной формулой​ идею со сводными​lapink2000​Кстати, и за​ преобразовываются в код​: подсчет значений с​ полезна для подсчета​ в данной программе​ года сумма выручки​ превысила 14000 рублей.​находим и выделяем​

​. Тут нам нужно​«Условие 1»​

СЧЁТЕСЛИМН

​ оператор позволяет задать​ 15000 рублей.​​ условия вычисления, которые​​ почему вылазит такой​ Счётеслимн.​ таблицами, интересная идея,​​: или так:​​ день так можно:​ времени Excel (числовое​ множественными критерями также​ с множественными критериями​ с критериями, то​ при сложении её​Снова выделяем ячейку для​ наименование​ указать нижнюю границу​вписываем выражение​

​Выделяем элемент листа, в​​ принято называть критериями.​ косяк даже цветами​vikttur​ возьму на вооружение​=СУММА(СЧЁТЕСЛИ(A1:A13;{"*замена*";"*установка*"}))​Код200?'200px':''+(this.scrollHeight+5)+'px');">=СЧЁТЕСЛИМН(B$3:B$36;E11;C$3:C$36;F$3)​ значение), а затем​ рассмотрен в статьях​ в других случаях. ​ есть, с определенными​ за дни, в​

​ вывода итога и​​«СУММЕСЛИ»​ значений в ячейках,​«>14000»​ областей.​ который оператор будет​ Давайте подробнее узнаем,​ пометил, постарайтесь приложить​: С моим наброском​ и попробую применить​malder​seldom​ выполняется операция проверки.​ Подсчет значений с​Разберем подробнее применение функции​ условиями, при выполнении​ которых она превышает​ клацаем по пиктограмме​

​. Клацаем по кнопке​ которые будут принимать​.​«Условие»​ выводить результат вычисления.​ каким образом можно​ усилия и вы​ формулы разобрались?​ в следующем отчёте!​: Всем доброго дня!​: Ребята, спасибо Вам​​ Последний (третий) критерий​​ множественными критериями (Часть​ СУММПРОИЗВ():​ которых активируется указанное​ 14000 рублей, составила​«Вставить функцию»​​«OK»​​ участие в подсчете.​В поля​

​представляет собой критерий,​ После этого щелкаем​ их использовать при​avar99​Диапазоны можно смещать​PS по столбцу​ Нужна помощь. Необходимо​ огромное за помощь!​ – количество рабочих​ 2. Условие ИЛИ),​Результатом вычисления A2:A13=D2 является​ действие, является набор​

• ​ 62491,38 рубля.​.​
• ​.​ Указываем выражение​
• ​«Диапазон условия2 (3,4,5)»​ который определяет, какие​
• ​ по пиктограмме​ работе в Экселе.​
• ​: Сергей приношу извинения,если​ прямо в теле​

1. ​ B я неверно​ подсчитат колычество совпадений​seldom​ часов больше 8.​ Часть3, Часть4.​​ массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение​​ встроенных функций, а​​Последним, описанным нами, инструментом,​​В​

   

2. ​Запускается окно аргументов функции​​«>14000»​​следует внести координаты​ элементы из соответствующего​​«Вставить функцию»​​Скачать последнюю версию​ я Вас чем​​ функции.​​ колонку назвал, должно​ при выполнении двух​: Ребята, а как​В результате расчетов получим​Функция СЧЁТЕСЛИМН предназначена для​​ ИСТИНА соответствует персики.​​ также условное форматирование.​

   

3. ​ при работе с​Мастере функций​СУММЕСЛИ​​.​​ строк с недельной​

   ​ массива данных войдут​​.​​ Excel​ то обидел, ошибка​Сергей​ быть вместо Кол-во​ условий. Как пример:​ Вы думаете, период​ следующее значение:​ подсчета числа ячеек​ Результат можно увидеть,​Автор: Максим Тютюшев​

   ​ которым используются критерии,​, прежде всего, выполняем​. В нём имеется​В поле​ выручкой соответственно Магазина​​ в подсчет, а​​Производится запуск​​Критерии представляют собой условия,​​ в подсчетах могла​

   ​: Таблица ваще​​ -> Название системы,​​ количество однофамильцев в​ можно просчитать? Например​Функция имеет следующую синтаксическую​ из диапазона, удовлетворяющих​ выделив A2:A13=D2, а​В этом разделе перечислены​ является условное форматирование.​ перемещение в блок​ три поля, соответствующих​«Диапазон условия2»​

   ​ 2, Магазина 3,​​ какие не войдут.​​Мастера функций​​ при которых программа​​ возникнуть потому что,​​вот с доп​​ а в первой​​ организации​​ 01-04.11.13​​ запись:​​ установленным одному или​​ затем нажав клавишу​​ распространенные причины ошибки​​ Он выполняет указанный​​«Математические»​​ аргументам указанного оператора.​​вводим тот же​ Магазина 4 и​ Каждой заданной области​. Совершаем перемещение в​ выполняет определенные действия.​

   ​ я вёл подсчет​ столбцами​ колонке - её​rvp9321​Pelena​​=СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)​​ нескольким критериям, и​

   

4. ​F9​ #ЗНАЧ! в функциях​ вид форматирования ячеек,​, а там выделяем​В поле​ адрес тем же​ Магазина 5. Действие​ данных нужно указывать​ блок​ Они применяются в​ с помощью фильтра,​

​avar99​ номер.​: Здравствуйте! Подскажите оптимальное​: Примерно так (даты​Описание аргументов:​ возвращает соответствующее числовое​;​ СЧЁТЕСЛИ и СЧЁТЕСЛИМН.​

1. ​ которые отвечают заданным​ пункт под названием​«Диапазон»​ способом, который вводили​ выполняем по тому​​ условие отдельно, даже​​«Статистические»​ целом ряде встроенных​

   

2. ​ пытался сделать показательный​: к сожалению функция​​Pelena​​ решение, желательно без​ в F2 и​диапазон_условия1 – обязательный аргумент,​​ значение. В отличие​​Двойное отрицание (--) преобразует​​Если функция СЧЁТЕСЛИ или​ условиям. Взглянем на​«СУММЕСЛИМН»​​вводим область таблицы,​​ в поле​ же алгоритму, что​​ в том случае,​​. Там находим и​

   

3. ​ функций. В их​ файл как можно​​ работает не корректно,​​: Можно перечислить все​ использования макросов, если​​ F3)​​ принимающий ссылку на​ от функции СЧЁТЕСЛИ,​ вышеуказанный массив в​ СЧЁТЕСЛИМН ссылается на​ пример работы с​. Производим клик по​ в которой будут​«Диапазон условия1»​ и для первого​​ если оно совпадает.​​ выделяем наименование​ названии чаще всего​ быстрее, еще раз​

   ​ это видно в​ нужные значения в​​ возможно.​​200?'200px':''+(this.scrollHeight+5)+'px');">=СЧЁТЕСЛИМН($B$3:$B$36;E11;$C$3:$C$36;">="&$F$2;$C$3:$C$36;"​ диапазон ячеек, в​ которая принимает только​ числовой: {0:0:0:0:1:1:1:0:0:0:0:0}​ ячейку или диапазон​ условным форматированием.​​ кнопке​​ располагаться значения, проверяемые​

   ​, то есть, опять​​ аргумента данной группы.​​ Обязательно требуется, чтобы​«СЧЁТЕСЛИ»​ присутствует выражение​ приношу свои извинения..Сергей​​ моем втором файле​​ массиве констант​Проблема в следующем:​seldom​ отношении содержащихся данных​ один аргумент с​

   ​Аналогично, второй массив возвращает​​ в закрытой книге,​​Выделим те ячейки таблицы​«OK»​​ на соблюдение условий.​

   ​ вносим координаты ячеек​В поля​ все массивы, используемые​​. Затем следует клацнуть​​«ЕСЛИ»​

   

4. ​ спасибо за проделанную​ , подскажите как​200?'200px':''+(this.scrollHeight+5)+'px');">=СУММПРОИЗВ(СЧЁТЕСЛИМН(G:G;$B$4:$B$5;H:H;A26;I:I;{"A";"B";"C";"D";"N"}))​ Имеется оборудование, поделенное​: Спасибо! Работает!​ в которых будет​ критерием отбора данных,​ {0:1:1:1:0:1:1:0:0:1:1:1}, где 0​ это приводит к​ синим цветом, где​

СУММЕСЛИ

​.​ В нашем случае​​ со значениями выручки​​«Условие2»​ в качестве областей​ по кнопке​. К данной группе​ работу, если я​ корректно проводить смещение..​*Вместо "Ч" должно​ на 2 группы​

​ применен критерий, указанный​​ рассматриваемая функция позволяет​​ соответствует значениям B2)​ ошибке #ЗНАЧ!​ значения за день​Производится запуск окошка аргументов​ это будет строка​ по первой торговой​,​ условия, имели одинаковое​​«OK»​​ операторов, прежде всего,​

​ Вас правильно понял,​​vikttur​ быть "N"​ (Группа 1 и​: Ребята, добрый день!​ в качестве второго​ указывать до 127​ =3, которое меньше​Примечание.​ превышают 14000 рублей.​ оператора, наименование которого​ дат. Ставим курсор​

​ точке.​​«Условие3»​ количество строк и​.​ нужно отнести​ то необходимо вести​: Неужели не читали?​rvp9321​ Группа 2).​ Как еще суммировать​ аргумента;​ критериев.​​ 5 (не удовлетворяет​​Это известная проблема​

​Выделяем весь массив элементов​ было указано выше.​ в данное поле​В поле​,​ столбцов.​Происходит активация окна аргументов​СЧЁТЕСЛИ​ с помощью доп.столбцов,​avar99​

1. ​: Класс! Оно самое!​За отчётный период​ по двум критериям(столбец​условие1 – обязательный аргумент,​​С помощью функции СЧЁТЕСЛИМН​​ критерию), поэтому первое​

   

2. ​ с рядом других​​ в таблице, в​​Устанавливаем курсор в поле​​ и выделяем все​​«Условие2»​«Условие4»​​Для того, чтобы задать​​ вышеуказанного оператора. В​​,​​ предварительно их заполнив​

   

3. ​: Уважаемые участники форума,​​Спасибо!​​ при работе оборудования​ B и ячейка​ принимающий условие для​

   ​ можно рассчитать количество​​ значение в массиве  {0:1:1:1:0:1:1:0:0:1:1:1}​​ функций Excel, в​ котором указана выручка​«Диапазон суммирования»​ ячейки, в которых​указываем верхнюю границу​и​ несколько параметров одной​ поле​СЧЁТЕСЛИМН​ аналогично Вашему примеру..​ я наверное не​

   ​avar99​ происходят отказы, которые​ G3), только без​ отбора данных из​ ячеек, соответствующих критериям,​​ =0. Второе значение (ячейка​​ том числе СУММЕСЛИ,​​ торговых точек по​​. В отличие от​

   ​ содержатся даты.​​ отбора:​​«Условие5»​ и той же​«Диапазон»​,​Сергей​ корректно выразился в​​: Доброго времени суток!​​ делятся на:​ использования формул массива?​

   ​ диапазона ячеек, указанных​ применяемым к столбцу​B3​ СУММЕСЛИМН и СЧИТАТЬПУСТОТЫ.​​ дням.​​ последующих аргументов, этот​

   

4. ​Так как нам нужно​«.​вносим соответственно значения​ области данных, например,​следует указать область​​СУММЕСЛИ​​: avar99, ни каких​ первом посте, но​ Есть необходимость подсчитать​- Система, к​китин​ в качестве диапазон_условия1.​ с числовыми значениями.​) =5, которое удовлетворяет​ Дополнительные сведения см.​

СУММЕСЛИМН

​Передвигаемся во вкладку​ единственный в своем​ сложить только суммы​​После того, как все​​«>15000​ чтобы подсчитать количество​ ячеек, среди которых​,​ обид нет, эт​ конечным итогом работы​

​ которой относится отказ/дефект​​: мона и без​ Этот аргумент принимает​ Например, в ячейках​ критерию >=5, поэтому​ в статье Функции​

​«Главная»​​ роде и указывает​ выручки, начиная с​ указанные действия произведены,​»,​

​ ячеек, в которых​​ будет производиться подсчет.​СУММЕСЛИМН​ просто констатация факта​

​ мне надо получить​ Наименование услуги, работы)​- Критичные отказы​

​ массива​ числа, данные ссылочного​ A1:A9содержится числовой ряд​ второе значение в​ СУММЕСЛИ, СЧЁТЕСЛИ и​. Клацаем по пиктограмме​ на тот массив​ 11 марта, то​ клацаем по кнопке​«>24000»​ расположены величины больше​ В нашем случае​. Кроме встроенных операторов,​ если вы хотите​кол-во услуг/работ, учитывая наименование​ при условии что​ делятся на 3​

1. ​200?'200px':''+(this.scrollHeight+5)+'px');">=СУММПРОИЗВ(($C$3:$C$36=$G$3)*$D$3:$D$36*($B$3:$B$36="бензин"))​ типа, текстовые строки,​ от 1 до​​ массиве =1 и​​ СЧИТАТЬПУСТОТЫ возвращают ошибку​

   

2. ​«Условное форматирование»​​ значений, где будет​​ в поле​«OK»​​,​​ определенного числа, но​ следует выделить содержимое​​ критерии в Excel​​ получить помощь то​ услуги/работы и учитывая​​ в столбце Тип​​ уровня - A,​

   

3. ​и что со​ содержащие логические выражения.​ 9. Функция =СУММЕСЛИМН(A1:A9;”>2”;A1:A9;”​

   ​ т.д.​​ #ЗНАЧ!​​, размещенной в блоке​ производиться суммирование подошедших​«Критерий»​.​«>11000»​ меньше другого числа,​ строки​ используются также при​ помогите помогающим, покажите​ орган оказывающий РФ,​​ выбрано услуга/работа, в​​ B, C.​ мной сегодня?туплю по​ Например, из таблицы,​

   ​ ​Далее, функция попарно перемножает​Решение:​​«Стили»​​ под указанные критерии​вбиваем значение​Программа выдает результат расчета.​и​ следует в качестве​«Магазин 2»​ условном форматировании. Рассмотрим​

   ​ правильный результат что​​ субъект или Мун.ОБр​​ столбце Виды ППО​- Отказ/дефект был​ черному​ содержащей поля «Наименование»,​Пример 1. Определить количество​ элементы массивов и​​Откройте связанную книгу,​​на ленте. Открывается​

   ​ данных. Затем выделяем​​«>10.03.2017»​​ Как видим, итоговое​«>32000»​ аргумента​, в которой расположены​​ их применение при​​ бы хотелось получить​..иными словами надо посчитать​ - Российская Федерация/муниципальное​ выявлен в процессе​

   ​seldom​​ «Стоимость», «Диагональ экрана»​​ телевизоров производства LG​ суммирует их. Получаем​ указанную в формуле,​ список действий. Клацаем​ область строки​.​ значение равно 5.​​. Как нетрудно догадаться,​

   ​«Диапазон условия»​​ значения выручки по​​ работе с различными​ и в связи​ сколько услуг оказывает​ образование..​ работы (Proc) и​: Спасибо! А Вы​

   ​ необходимо выбрать устройства,​ в таблице данных,​ – 2.​ и нажмите клавишу​​ в нём по​​«Магазин1»​В поле​ Это значит, что​ эти значения соответствуют​несколько раз указать​ дням. Ставим курсор​​ инструментами данного табличного​​ с чем посмотрите​

   ​ в данном перечне​функция СЧЁТЕСЛИМН не​​ при техобслуживании (Maint).​​ не знаете, я​

   

4. ​ цена которых не​ стоимость которых не​3. Другим вариантом использования​ F9, чтобы обновить​ позиции​, в которой размещены​«Диапазон суммирования»​ в 5 днях​ интервалу выручки, превышающую​ один и тот​ в указанное поле​ процессора более подробно.​

Условное форматирование

​ формулы которые предложили​ мун.образование...​ работает, причина мне​Задача создать сводную​ вставляю формулу в​ превышает 1000 долларов,​ превышает 20000 рублей.​ функции СУММПРОИЗВ() является​ формулу.​«Создать правило…»​

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

1. ​ же массив. Но​ и, зажав левую​Главной задачей оператора​ другие форумчане возможно​avar99​

   

2. ​ кажется в том,​​ таблицу по каждой​​ другую таблицу и​​ производителем является фирма​​Вид исходной таблицы:​​ формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)). Здесь,​​Также можно обойти эту​.​ соответствующей торговой точке.​ значения которой, отвечающие​​ выручка в первом​​ магазина.​

   

3. ​ при этом в​ кнопку мыши, выделяем​СЧЁТЕСЛИ​ и они работают​​: моя формула во​ что объединены ячейки..​​ системе каждой группы.​ увеличиваю диапазон до​ Samsung, а диагональ​Для расчета количества телевизоров​​ знак Умножения (*)​​ проблему, используя функции​Активируется окошко генерации правила​​После того, как адрес​​ указанным критериям, будут​ магазине была в​После того, как был​ качестве соответствующих аргументов​ соответствующий массив в​, относящегося к статистической​ правильно просто вы​ втором файле в​​vikttur​​Во вложении пример.​

   

4. ​ 7000(где С36 я​ составляет 5 дюймов.​​ компании LG, стоимость​​ эквивалентен Условию И.​ СУММ и ЕСЛИ​ форматирования. В области​ отобразился в окне,​ суммироваться. В нашем​ интервале от 14000​ произведен ввод всех​​«Условие»​​ таблице. Адрес выделенного​​ группе, является подсчет​​ сравнивали с не​

   

5. ​ самом конце..​: Рыскать по листу​ Ввиду срочности, пришлось​ ставлю С7000 и​​ В качестве условий​​ которых не превышает​4. Формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))​ вместе в формуле​ выбора типа правила​ переходим к полю​​ случае это значения​​ до 17000 рублей.​

   

6. ​ необходимых данных (всего​следует указывать разные​ массива отобразится в​ занятых различными значениями​ верным результатом​Сергей​

​ в поисках? Для​ прибегнуть к СЧЁТЕСЛИМН,​ т.д.) и формула​

​ можно указать “Samsung*”​ 20000 рублей используем​

​ эквивалентна вышеупомянутой формуле​ массива. Дополнительные сведения​ выделяем наименование​«Диапазон условия1»​ выручки строки​Ещё одним оператором, который​ 10 полей), жмем​ критерии.​ окне.​ ячеек, которые удовлетворяют​ПС для понятия​: ну давайте в​ примера достаточно было​ но, как видно​ не считает(когда ставлю​ (подстановочный символ «*»​ следующую формулу:​ =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2))  Единственное, после​ см. в статье​«Форматировать только ячейки, которые​

​. Тут нам нужно​

lumpics.ru

Исправление ошибки #ЗНАЧ! в функциях СЧЁТЕСЛИ и СЧЁТЕСЛИМН

​«Магазин1»​ использует критерии, является​ на кнопку​На примере все той​

Проблема: формула ссылается на ячейки в закрытой книге

​В следующем поле​ определенному заданному условию.​ поставленой проблемы с​ угадайку не играть,​ 20-30 строк.​ формула становится слишком​

​ 1000 - нормально​​ замещает любое количество​Описание аргументов:​ ее ввода нужно​ Функции СУММЕСЛИ, СЧЁТЕСЛИ​ содержат»​ будет отобразить координаты​. Выделяем соответствующий массив​СУММЕСЛИ​«OK»​ же таблицы с​

​«Критерий»​​ Его синтаксис следующий:​ которой хотите разобратся​ на примере вашей​Где Ваша формула?​ громоздкой... особенно, учитывая​

​ идет), почему?​ символов), “>1000” (цена​A2:A11 –диапазон первого условия,​ вместо​ и СЧИТАТЬПУСТОТЫ возвращают​. В первом поле​ строки с датами.​ элементов листа.​. В отличие от​

Проблема: длина строки превышает 255 символов

​.​​ недельной выручкой магазинов​как раз нужно​=СЧЁТЕСЛИ(диапазон;критерий)​ доп столбцы эт​ таблички руками считаете​Antubas​ что число машин​Serge_007​

• ​ свыше 1000, выражение​ ячейки которого хранят​

У вас есть вопрос об определенной функции?

​ENTER​ ошибку #ЗНАЧ!​

Помогите нам улучшить Excel

​ блока условий из​ Производим зажим левой​После того, как произведено​ предыдущих функций, он​Программа производит подсчет и​ посмотрим, как это​ задать непосредственный параметр​

support.office.com

Подсчет значений с множественными критериями (Часть 1. Условие И) в MS EXCEL

​Как видим, у данного​ самое то с​ работа/субъект -кол-во, услуги/федерация-​: Добрый день, формула​

​ (оборудования) в дальнейшем​: Про "и т.д."​ должно быть указано​ текстовые данные с​нажать​Решение:​

Задача1

​ списка возможных вариантов​ кнопки мыши и​ введение всех указанных​

​ относится к математическому​ выводит результат на​​ работает. Нам нужно​​ отбора. В нашем​ оператора два аргумента.​ простейшими формулами в​ стока-то, т.е. покажите​ "СЧЁТЕСЛИ(N:N;"Российская Федерация")" посчитала​ будет увеличиваться.​ поподробнее​ в кавычках), 5​ названием фирмы и​CTRL+SHIFT+ENTER​​Сократите строку, если​​ выбираем​ выделяем все даты​ данных, жмем на​​ блоку операторов. Его​​ экран. Как видим,​ узнать количество дней​ случае нужно подсчитать​«Диапазон»​ которых ответ истина​

​ реальный результат который​ 118 штук.​

​Должно же быть​seldom​ (точное числовое значение,​

​ величиной диагонали;​5. Формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет​ это возможно. Если​«Значение ячейки»​ в таблице.​ кнопку​ задачей является суммирование​

​ он равен числу​ недели, когда доход​

• ​ только те элементы​представляет собой адрес​ или ложь​ вы хотите получить​avar99​ более простое решение.​​:​​ кавычки необязательны);​
• ​"LG*" – условие поиска​ еще один вариант​ сократить строку нельзя,​
• ​. В следующем поле​Ставим курсор в поле​«OK»​ данных в ячейках,​ 3. Это означает,​ во всех указанных​ таблицы, в которых​ массива элементов на​​avar99​​ грубо говоря эталон​: должны быть соблюдены​ Полагаю что нужно​200?'200px':''+(this.scrollHeight+5)+'px');">=СУММПРОИЗВ(($C$3:$C$36=$G$3)*$D$3:$D$36*($B$3:$B$36="бензин"))​[диапазон_условия2;условие2];… - пара последующих​
• ​ с подстановочным знаком​ многокритериального подсчета значений.​ используйте функцию СЦЕПИТЬ​ выбираем позицию​

​«Условие1»​.​ которые соответствуют определенному​ что в трех​ торговых точках достигал​

​ значение превышает 15000.​ листе, в которых​: примеры пользователей проверял,​ форумчанам сделайте на​ два условия, Работа​​ использовать массивы, но​​я имею в​​ аргументов рассматриваемой функции,​

​ «*» (любое количество​6. Формула =БСЧЁТА(A1:B13;A1;D14:E15) требует​ или оператор-амперсанд (&),​

​«Больше»​. Первым условием является​После этого в предварительно​ условию. Синтаксис таков:​ днях из анализируемой​ установленной для них​ Поэтому с помощью​ следует произвести подсчет.​ функция СЧЕТЕСЛИМН работает​

​ который можно ориентироватся​ + РФ..​ с этим у​ виду С36 я​ смысл которых соответствует​ символов после «LG»;​ предварительного создания таблицы​ чтобы разделить значение​. В последнем —​ то, что нами​ указанный элемент рабочего​=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])​

Задача2

​ недели выручка во​ нормы. Нормы выручки​ клавиатуры вбиваем в​

​«Критерий»​ не корректно я​avar99​Leanna​ меня пока сложно.​ ставлю С7000, D36​ аргументам диапазон_условия1 и​B2:B11 – диапазон второго​ с условиями. Заголовки​

​ на несколько строк.​ указываем само значение,​ будут суммироваться данные​ листа будет выведен​Аргумент​ всех торговых точках​

​ следующие:​

Альтернативное решение

​ указанное поле выражение​— это аргумент,​ с ней и​: вот сделал результат​: массивная​

​Главное заставить считать​ я ставлю D7000,​ условие1 соответственно. Всего​ условия, содержащий значения​​ этой таблицы должны​​ Пример:​

​ больше которого требуется​

​ не ранее 09​ результат обработки данных​«Диапазон»​ превышала установленную для​Магазин 1 – 14000​«>15000»​ который задаёт условие,​

​ возился прежде чем​​ руками​{=СУММ((E9:E489="Услуга")*(ЕСЛИ(N9:N489="мунципальное образование";1;ЕСЛИ(СМЕЩ(N9:N489;1;0)="мунципальное образование";1;ЕСЛИ(СМЕЩ(N9:N489;2;0)="мунципальное​ те ячейки, в​ B36 я ставлю​ может быть указано​ стоимости товаров;​ в точности совпадать​

excel2.ru

Функция СЧЁТЕСЛИМН считает количество ячеек по условию в Excel

​=СЧЁТЕСЛИ(B2:B12;"длинная строка"&"еще одна длинная​ отформатировать элементы таблицы.​ марта. Поэтому вводим​ функцией​указывает на область​ них норму.​ рублей;​.​ что именно должны​ написать на форум,​Сергей​ образование";1;ЕСЛИ(СМЕЩ(N9:N489;3;0)="мунципальное образование";1;0))))))}​ который в графе​ B7000. По идеи​

Примеры использования функции СЧЁТЕСЛИМН в Excel

​ до 127 диапазонов​"​ с заголовками исходной​ строка")​ У нас это​ значение​СУММЕСЛИ​ ячеек, которые будут​Теперь несколько изменим задачу.​Магазин 2 – 15000​

Как посчитать количество позиций в прайсе по условию?

​После того, как все​ содержать ячейки указанной​ формула массива со​: ну раз такой​avar99​

​ Машина присутствует машина​

​ должно всё работать.​ и условий для​Результат вычислений:​ таблицы. Размещение условий​Задать вопрос на форуме​

​ 14000. Чтобы выбрать​

• ​«>08.03.2017»​. В нашем случае​ проверяться на соблюдение​ Нам следует посчитать​ рублей;​
• ​ вышеуказанные манипуляции произведены,​ области, чтобы быть​ смещением, предложенная не​ результат то играйтесь​
• ​: Большое спасибо за​ из списка Оборудование.​Serge_007​
• ​ отбора значений.​

​Пример 2. В таблице​

Как посчитать долю группы товаров в прайс-листе?

​ в одной строке​ сообщества, посвященном Excel​ тип форматирования, клацаем​.​ он равен 47921,53.​ условия. По сути,​ количество дней, в​Магазин 3 – 24000​ клацаем по кнопке​ включенными в подсчет.​ подходит т.к. даже​ фильтрами в ячейках​

​ ответ, я так​

​Спасибо​: И будет работать.​

​Примечания:​ содержатся данные о​ соответствует Условию И.​У вас есть предложения​ по кнопке​Перемещаемся к аргументу​ Это означает, что​ он задается по​ которых Магазин 1​ рублей;​«OK»​ В качестве параметра​ в одном файле​ под таблицей увидите​ понял что смещение​Pelena​Вам первое предупреждение:​Во втором и последующем​ покупках в интернет​Здесь есть один трюк:​

​ по улучшению следующей​

Как посчитать количество ячеек по нескольким условиям в Excel?

​«Формат…»​«Диапазон условия2»​ начиная с 11.03.2017,​ тому же принципу,​ получил выручку, превышающую​Магазин 4 – 11000​.​ может быть использовано​ объединенные ячейки имеют​ что хотите​

​ надо применять ровно​

​: Здравствуйте.​ Читайте правила форума.​

​ магазине бытовой техники​ в качестве второго​ версии Excel? Если​.​. Тут нужно внести​ и до конца​ что и одноименный​ 14000 рублей, но​ рублей;​Программа производит подсчет и​

​ числовое выражение, текст​ разный размер...​

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

​avar99​ столько раз сколь​

​Примерно так можно​

​ Дальше начну банить​

• ​ [диапазон_условия3] и т.​ за определенный период​ аргумента функции БСЧЁТА()​ да, ознакомьтесь с​Активируется окно форматирования. Передвигаемся​ те же координаты,​ анализируемого периода, общая​ аргумент функции​
• ​ меньшую, чем 17000​Магазин 5 – 32000​ выводит результат в​ или ссылка на​Всё же прошу,​: Сергей, подскажите как​ возможно вариантов (в​200?'200px':''+(this.scrollHeight+5)+'px');">=СУММПРОИЗВ(СЧЁТЕСЛИМН(G:G;$B$4:$B$5;H:H;A26))​dydyka​ д.) число ячеек​ времени. Определить соотношение​ (поле) нужно ввести​ темами на портале​ во вкладку​ которые были записаны​ выручка по Магазину​СЧЁТЕСЛИ​ рублей.​ рублей.​ элемент листа, который​ ячейку, в которой​ уважаемые форумяне помогите​ применять фильтр, чтобы​ это таблице у​или формула массива​: есть столбец в​ должно соответствовать их​ проданных продуктов фирм​
• ​ ссылку на заголовок​ пользовательских предложений для​«Заливка»​ в поле​ 1 составила 47921,53​.​Ставим курсор в элемент,​Для выполнения вышеуказанной задачи,​ был выделен перед​

​ критерий содержится. При​

1. ​ решить данную проблему,​ две нижние строки​ мне их 3,​Код200?'200px':''+(this.scrollHeight+5)+'px');">=СУММ(СЧЁТЕСЛИМН(G:G;$B$4:$B$5;H:H;A26))​ котором мне необходимо​ количеству в диапазоне,​ LG, Samsung и​ столбца с текстовыми​ Excel.​. Из предложенных вариантов​
2. ​«Диапазон условия1»​ рубля.​«Критерий»​ где будет произведен​ выделяем курсором элемент​ активацией​ этом, для указания​ если это возможно,​ с промежуточным результатом​ а в другой​
3. ​slamdunk​ найти 2 разных​ заданном аргументом диапазон_условия1.​ Bosch продавцом с​ значениями, т.к. БСЧЁТА()​Произведем подсчет строк, удовлетворяющих​
4. ​ цветов заливки выбираем​. Делаем это тем​Завершим изучение операторов, которые​— является обязательным​ вывод на лист​

exceltable.com

СЧЁТЕСЛИМН за месяц (Формулы)

​ рабочего листа, куда​​Мастера функций​
​ критерия можно использовать​ с помощью функции,​ не скрывались фильтром?​ больше 5)...​: У Вас данные​ слова, ни как​ В противном случае​

​ фамилией Иванов к​​ подсчитывает текстовые значения.​

​ сразу двум критериям,​​ синий, щелкая по​ же способом, то​ используют критерии, остановившись​

​ аргументом, задающим параметр​​ результатов подсчета. Клацаем​

​ равен числу 5.​​«меньше»),​ понимания масштаба работы​ какой нибудь другой​ условие должно быть,​ таблицы. Немного переделал​ =СЧЁТЕСЛИМН(Работы!$I:$I;B3;Работы!$J:$J;"*замена" И "установка")-​Рассматриваемая функция выполняет проверку​Вид исходной таблицы:​ =БСЧЁТ(A1:B13;B1;D14:E15). Табличка с​В качестве исходной таблицы​ как выбранный цвет​

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

​над рабочей площадью​​. Клацаем по иконке​​«>»​ файл,из которого делался​ способ получить результат​ что все объединенные​ группировку данных, чтобы​ соответственно не работает​ всех условий, перечисленных​

​Для получения искомого значения​​ критериями не изменится.​
​ возьмем таблицу с​ отобразился в области​«Условие2»​

excelworld.ru

СЧЁТЕСЛИМН в одном условии два критерия?

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

​ в качестве аргументов​ используем формулу:​

​Рассмотрим задачу, когда критерии​​ двумя столбцами: текстовым​

​«Образец»​. Вторым условием является​
​ значений указанных областей​ же, что и​

​Так как мы совсем​​.​
​ в пяти ячейках​«больше»​ более чем 12000,​ у Сергея, не​ (здесь по четыре)​ со сводными.​ количество: замена, установка​

​ условие1, [условие2] и​​Для поиска сразу нескольких​ применяются к значениям​

​ «Фрукты» и числовым​​, клацаем по кнопке​ то, что дни,​ таблицы, отобранных по​ у аналогичных аргументов​ недавно применяли формулу​Перейдя в​ находятся значения превышающие​

​),​​ это самый не​ применяя фильтр, скажем​
​vikttur​

​Если часть колонок​​или одной формулой​ т. д. для​ значений в векторе​ из одного столбца.​

​ «Количество на складе»​​«OK»​

​ за которые будет​

​ нескольким параметрам. Синтаксис​​ предыдущих операторов, которые​СЧЁТЕСЛИМН​Мастер функций​ 15000. То есть,​«=»​ большой файл, таких​ встроенными функциями или​

planetaexcel.ru

Подсчёт ячеек по нескольким условиям но не СЧЁТЕСЛИМН (Формулы/Formulas)

​: =СЧЁТЕСЛИМН(E9:E489;"Услуга";N12:N492;"мунципальное образование")​​ окажется лишней, то​ тут не обойтись?​ каждой строки. Если​ данных (столбце B:B)​
​Найдем число партий товара​ (См. файл примера).​.​ суммироваться выручка, должны​ указанного оператора таков:​
​ были рассмотрены нами​, то теперь не​, снова перемещаемся в​ можно сделать вывод,​
​(​ файлов уже более​
​ макросом? Менять постоянно​Принцип должен быть​ их можно скрыть.​Юрий М​
​ все условия выполняются,​ в качестве аргумента​ с Количеством на складе​Рассмотрим задачу, когда критерии​
​Автоматически происходит возврат к​ быть не позже​=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)​
​ выше.​ обязательно переходить в​ блок​ что в Магазине​«равно»​ 30...СПАСИБО за понимание​ условия фильтра не​ понятен.​И где Вы​
​:​ общая сумма, возвращаемая​ условие1 была передана​ не менее минимального и​ применяются к значениям​ окну генерации правила​
​ 13 марта. Поэтому​«Диапазон суммирования»​«Диапазон суммирования»​ группу​«Статистические»​
​ 2 в пяти​

​),​​ и терпение...​
​ совсем удобно...ОЧЕНЬ НУЖНА​

​ берете информацию по​
​Ещё на этой странице​

​ СЧЁТЕСЛИМН, увеличивается на​​ константа массива {"LG";"Samsung";"Bosch"},​ не более максимального​ из разных столбцов.​ форматирования. В нём​ записываем следующее выражение:​— это аргумент,​— это необязательный​
​«Статистические»​. В перечне следует​ днях из анализируемых​
​«<>»​vikttur​ ВАША ПОМОЩЬ!​ сочли мое первое​ столбцу "В"? У​ чуть ниже "​ единицу.​

​ поэтому формулу необходимо​​ (Условие И -​Найдем число партий товара​
​ также в области​«.​ являющийся адресом того​ аргумент. Он указывает​Мастера функций​ отыскать наименование​ семи выручка превысила​(​: После того, как​avar99​
​ собщение придиркой, получили​ Вас нет данных​"вопрос по операции​Если в качестве аргумента​ выполнить в качестве​ строка таблицы соответствует​ с определенным Фруктом​
​«Образец»​Переходим в поле​ массива, ячейки в​ на конкретную область​. Наименование данного оператора​

​СЧЁТЕСЛИМН​​ 15000 рублей.​«не равно»​ видел Ваши файлы,​: Доброго времени суток!​ бы полную формулу.​ в таблице, где​
​ СУММЕСЛИ" не то?​ условиеN была передана​ формулы массива. Функция​ критерию, когда ее​И​отображается синий цвет.​«Диапазон условия2»​ котором, отвечающие определенному​

​ массива, в которой​​ можно найти в​и произвести его​Урок: Мастер функций в​

​ нет желания их​ Народ неужели нет​

​avar99​​ отражены: "пневматика", "гидравлика"​
​dydyka​

excelworld.ru

Применение функции СЧЁТЕСЛИМН, при объединненых ячейках

​ ссылка на пустую​​ СУММ подсчитывает число​ поле удовлетворяет обоим​с Количеством на​ Тут нам нужно​. В данном случае​ критерию, будут складываться.​ будет производиться суммирование.​ категории​ выделение. После выполнения​ программе Эксель​
​ выражение​ опять открывать.​ другого способа, кроме​: к сожалению это​

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

​ произвести одно единственное​​ нам нужно выделить​«Диапазон условия»​ Если его опустить​

​«10 недавно использовавшихся»​​ указанного действия требуется​Следующей функцией, которая оперирует​«, то при подсчете​

​Хотите помощи? Так​​ "игры с фильтрами"​
​ не соблюдается...​rvp9321​

​конечный этап вычисления​​ пустого значения к​ массиве значений, возвращаемых​Решение стоится аналогично предыдущей​ минимального (Условие И​ действие: клацнуть по​ тот самый массив,​— аргумент, представляющий​ и не указывать,​. Выделяем его и​ произвести нажатие на​

​ критериями, является​​ будут учитываться только​ помогите прежде всего​ и промежуточными итогами,​avar99​: Pelena, спасибо! То​

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

​ то по умолчанию​​ щелкаем по кнопке​ кнопку​

​СЧЁТЕСЛИМН​​ элементы, заданные аргументом​ сами себе.​ чтобы решить мою​

​: вот файл немного​​ что нужно.​ слова, но ввести​При использовании текстовых условий​ СЧЁТ возвращает число​ использованием функции СЧЁТЕСЛИМН() формула​

​ котором строка считается​​«OK»​ внесен, как массив​ проверяемый на соответствие​ считается, что он​«OK»​«OK»​

​. Она также относится​​«Диапазон»​Создайте небольшой понятный​ проблему, помогите пожалуйста!​ урезан, изначальный вариант​Один момент хотелось​
​ можно только одно​ можно устанавливать неточные​ непустых ячеек в​ выглядит так (см.​ удовлетворяющей критерию, когда​

​.​​ суммирования.​ условию;​
​ равен значению обязательного​.​.​

​ к статистической группе​​, в которых находятся​
​ пример без лишних​Сергей​

​ более 12000 строк...​​ бы уточнить, в​ * + и​ фильтры с помощью​ диапазоне A2:A21, то​ лист один столбец​ оба ее поля​

​После выполнения последнего действия,​​После того, как адрес​

​«Условие»​​ аргумента​Открывается уже знакомое нам​Вслед за выполнением вышеуказанного​ операторов. Задачей​ числовые значения менее​ столбцов, строк на​​: Есть и много​avar99​ реале, в графе​ или не работают​​ подстановочных символов «*»​ есть число строк​ в файле примера):​ одновременно соответствуют критериям).​

​ все ячейки выделенного​​ указанного массива отобразился​— аргумент, представляющий​«Диапазон»​

​ окошко аргументов оператора​​ алгоритма действий открывается​СЧЁТЕСЛИМН​ 50. Использование данных​ 20-30 - только​ только надо приложить​: ни в коем​ Критичность кроме А​ :(​ и «?».​ в таблице. Соотношение​=СЧЁТЕСЛИМН(B2:B13;">="&D2;B2:B13;"​

​ Например, число партий​​ массива, где содержится​ в окне, переходим​

​ собой критерий отбора​​.​СЧЁТЕСЛИМН​ окно аргументов​является подсчет ячеек​ знаков для указания​

​ нужная для решения​​ усилия, а вы​ случае, у меня​ В С D​Михаил С.​seldom​

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

​ даже свои данные​​ комп подвис и​ и N иногда​: Файлик бы... (небольшой,​: Господа! Помогите пожалуйста!​ искомым значением.​ 2-м критериям (Условие​D2​

​ 14000, будут залиты​​«Условие3»​Данная функция подразумевает операции​ применение данного оператора​ поле​.​ которые удовлетворяют определенному​ и для всех​Иначе тратить время​ внимательно проанализировать не​ я теперь выложил​ попадаются другие категории,​ xls)​Есть база данных,​В результате вычислений получим:​ И) можно без​) с количеством ящиков​ синим цветом.​. Учитывая, что в​ сразу с несколькими​ на практике. На​«Диапазон условия1»​В поле​ набору условий. Именно​ других вариантов, о​ на пролистывание, поиск​ можете, просто сесть​ урезанный файл​

​ которые для меня​​Михаил С.​ нужно чтобы формула​Пример 3. В таблице​ применения формул с​ на складе >=5​Более подробно о возможностях​ суммировании будут принимать​ наборами подобных операторов.​ основе той же​и, произведя зажим​«Диапазон условия1»​ тот факт, что​ которых пойдет речь​ формулы и "а​ и посчитать вручную,​avar99​ не имеют значения​: На счёт .xls​

​ выбирала за весь​​ приведены данные о​ помощью стандартного Автофильтра.​ (ячейка​ условного форматирования рассказывается​ участие только значения,​Посмотрим, как данный оператор​ таблицы перед нами​ левой кнопки мыши,​следует ввести адрес​ можно задать не​ в данном уроке​ что же тут​ то что вы​: уважаемые гуру форума,​ (NL, NA например).​ поскольку СЧЁТЕСЛИМН (),​
​ месяц и за​ количестве отработанных часов​Установите автофильтр к столбцу​Е2​ в отдельной статье.​ величина которых превышает​ применим для решения​ стоит задача подсчитать​

​ выделяем все ячейки,​​ строки, в которой​ один, а несколько​ ниже.​ нужно" - извините,​ выдали за эталон​ прошу посмотреть мой​Можно ли ограничить​ то все-таки в​ весь период времени(зеленый​ сотрудником на протяжении​ Количество ящиков на​). Результат очевиден: 2.​
​Урок: Условное форматирование в​ 14000 рублей, вносим​ задач в контексте​ сумму выручки в​ в которых содержится​ расположены данные по​ параметров, и отличает​А теперь давайте на​ его (времени) и​ в последнем файле​ второй файл, в​ счёт ячеек либо​ формате 2007... (хоть​ цвет), за день​ некоторого периода. Определить,​ складе, выделив заголовок​

​ Для наглядности, строки​​ программе Эксель​ запись следующего характера:​ нашей таблицы выручки​ Магазине 1 за​
​ выручка по дням​ выручке Магазина 1​ этого оператора от​
​ конкретном примере посмотрим,​ так мало.​ эт корявые данные​ нём как мне​ только A B​ и не люблю​
​ я уже сообразил.​ сколько раз сотрудник​ столбца и нажав​ в таблице, удовлетворяющие​Как видим, с помощью​«>14000»​ от реализации в​

planetaexcel.ru

​ период, начиная с​

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

Функция СЧЁТЕСЛИ может быть использована для анализа числовых значений, текстовых строк, дат и данных другого типа. С ее помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также число ячеек с данными, которые совпадают с указанным критерием лишь частично. Например, таблица Excel содержит столбец с ФИО клиентов. Для определения количества клиентов-однофамильцев с фамилией Иванов можно ввести функцию =СЧЁТЕСЛИ(A1:A300;”*Иванов*”). Символ «*» указывает на любое количество любых символов до и после подстроки «Иванов».

Примеры использования функции СЧЁТЕСЛИ в Excel

Пример 1. В таблице Excel содержатся данные о продажах товаров в магазине бытовой техники за день. Определить, какую часть от проданной продукции составляет техника фирмы Samsung.

Вид исходной таблицы данных:

Для расчета используем формулу:

=СЧЁТЕСЛИ(C3:C17;"Samsung")/A17

Описание аргументов:

• C3:C17 – диапазон ячеек, содержащих названия фирм проданной техники;
• "Samsung" – критерий поиска (точное совпадение);
• A17 – ячейка, хранящая номер последней продажи, соответствующий общему числу продаж.

Результат расчета:

Доля проданной продукции техники фирмы Samsung в процентах составляет – 40%.

Подсчет количества определенного значения ячейки в Excel при условии

Пример 2. По итогам сдачи экзаменов необходимо составить таблицу, в которой содержатся данные о количестве студентов, сдавших предмет на 5, 4, 3 балла соответственно, а также тех, кто не сдал предмет.

Вид исходной таблицы:

Предварительно выделим ячейки E2:E5, введем приведенную ниже формулу:

=СЧЁТЕСЛИ(B3:B19;D2:D5)

Описание аргументов:

• B3:B19 – диапазон ячеек с оценками за экзамен;
• D2:D5 – диапазон ячеек, содержащих критерии для подсчета числа совпадений.

В результате получим таблицу:

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

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

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

Функция имеет следующую синтаксическую запись:

=СЧЕТЕСЛИ(диапазон; критерий)

Описание аргументов:

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

Примечания:

1. При подсчете числа вхождений в диапазон в соответствии с двумя различными условиями, диапазон ячеек можно рассматривать как множество, содержащее два и более непересекающихся подмножеств. Например, в таблице «Мебель» необходимо найти количество столов и стульев. Для вычислений используем выражение =СЧЁТЕСЛИ(B3:B200;"*стол*")+СЧЁТЕСЛИ(B3:B200;"*стул*").
2. Если в качестве критерия указана текстовая строка, следует учитывать, что регистр символов не имеет значения. Например, функция СЧЁТЕСЛИ(A1:A2;"Петров") вернет значение 2, если в ячейках A1 и A2 записаны строки «петров» и «Петров» соответственно.
3. Если в качестве аргумента критерий передана ссылка на пустую ячейку или пустая строка «», результат вычисления для любого диапазона ячеек будет числовое значение 0 (нуль).
4. Функция может быть использована в качестве формулы массива, если требуется выполнить расчет числа ячеек с данными, удовлетворяющим сразу нескольким критериям. Данная особенность будет рассмотрена в одном из примеров.
5. Рассматриваемая функция может быть использована для определения количества совпадений как по одному, так и сразу по нескольким критериям поиска. В последнем случае используют две и более функции СЧЁТЕСЛИ, возвращаемые результаты которых складывают или вычитают. Например, в ячейках A1:A10 хранится последовательность значений от 1 до 10. Для расчета количества ячеек с числами больше 3 и менее 8 необходимо выполнить следующие действия:

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

• записать первую функцию СЧЁТЕСЛИ с критерием «>3»;
• записать вторую функцию с критерием «>=8»;
• определить разницу между возвращаемыми значениями =СЧЁТЕСЛИ(A1:10;">3")-СЧЁТЕСЛИ(A1:A10;">=8"). То есть, вычесть из множества (3;+∞) подмножество [8;+∞).

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

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

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

Самая простая функция СЧЁТЕСЛИ означает следующее:

• =СЧЁТЕСЛИ(где нужно искать;что нужно найти)

Например:

• =СЧЁТЕСЛИ(A2:A5;"Лондон")

• =СЧЁТЕСЛИ(A2:A5;A4)

Примеры

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

Распространенные неполадки

Рекомендации

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Для подсчета непустых ячеек используйте функцию СЧЁТЗ

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

Просмотрите видео об использовании функции СЧЁТЕСЛИМН

Функция СУММЕСЛИ суммирует только те значения, которые соответствуют одному условию

Функция СУММЕСЛИМН суммирует только те значения, которые соответствуют нескольким условиям

Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздних)

Для удаления начальных и конечных пробелов в ячейках используйте функцию СЖПРОБЕЛЫ

Для удаления непечатаемых символов в ячейках используйте функцию ПЕЧСИМВ

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах

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

Функции Excel (по алфавиту)

Функции Excel (по категориям)