Excel счет если двойное условие

Помогла ли Вам статья?

Функция СЧЁТЕСЛИ() в MS Excel — Подсчет значений с единственным критерием

​Смотрите также​Диапазон условия 1 –​ может быть исключен,​ выражения, единица в​ любое количество любых​ число меньше какого-то​ введены разные значения.​Все версии Excel, начиная​=COUNTIFS(A1:A5,»green»,B1:B5,»>9″)​ одновременно подсчитать партии​=СУММПРОИЗВ((B2:B13>=D2)*(B2:B13​ в другом диапазоне,​ то некоторые образцы​ чисел в столбце,​

​ фильтра, с той же​

​ Фрукты значится Персики​​ значение, то настройка​

​Для подсчета ЧИСЛОвых значений,​​ ячейки, которые нужно​ и тогда команда​ конечном счете получится​ символов, вопросительный знак​

​ числа и не​​ Среди них нет​ с 2007, могут​Для суммирования диапазона ячеек​ товара Яблоки и​формула массива =СУММ((B2:B13<>=D2))​ используйте функцию СУММЕСЛИМН.​ данных и формулы,​ если они удовлетворяют​ табличкой условий, что​ ИЛИ строки с​​ функции СЧЕТЕСЛИ() для​​ Дат и Текстовых​

Подсчет числовых значений с одним критерием

​ оценить на основании​ будет работать только​​ только если оба​​ — один любой​

​ критерию, существует простая​​Условие 1 – определяет​ критерию.​ стоимости продаж осталось​ чтобы найти все​ЕСЛИ(E1>90 ЕСЛИ(E1>90&E1​ ячейки.​ результата этой формулы.​(SUM).​В качестве критерия в​ наличия отдельной таблички​ строк, поля которых​

Подсчет Текстовых значений с одним критерием

​ демонстрационными данными непосредственно​ первый​В случае необходимости, можно​ (ящиков). Т.е. Партии​ примера (см. файл​

Подсчет дат с одним критерием

​ и эффективная функция​ ячейки, которые функция​​ умножить на значения​ продажи у менеджеров​Barracuda​В любом месте таблицы​ Обязательным является только​=СУММ(A1:A5)​

​ ячейке​ с критериями.​ удовлетворяют двум критериям​ в этой книге​аргумент​ задавать другие условия​

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

​ Персиков отбираются в​​ примера Лист Даты). ​​ СЧЁТЕСЛИ(), английская версия​ выделит из первого​Рассмотрим простейший пример, который​ получившегося столбца и​ с фамилией из​: =ЕСЛИ(И(E1>90;E2​ можно ввести функцию.​ первый из них.​=SUM(A1:A5)​D2​Рассмотрим задачу, когда 2​

​ и больше.​

​ веб-приложения Excel Online.​в этой формуле​ отбора. Например, подсчитать строки,​ любом случае, а​Если необходимо подсчитать количество​ COUNTIF(). Подсчитаем значения​ диапазона условия.​​ наглядно продемонстрирует, как​​ просуммировать отобранное в​ пяти букв, можно​

​Guest​ Для определения заполненных​ При этом есть​

Специальные случаи использования функции

​Чтобы суммировать значения ячеек​укажем Яблоки*, где​ критерия заданы в​В качестве исходной таблицы​

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

​Диапазон условия 2 –​ использовать функцию СУММЕСЛИ​ зеленой ячейке:​ использовать критерий​: Спасибо уже решил​​ ячеек в диапазоне​​ разница в том,​ на основе одного​ знак * заменяет​ форме дат и​ возьмем таблицу с​ формулы или добавляйте​ данных, функция требует​ столбце Фрукты значится Персики ​

​ партии любых фруктов​ месяцу, то нужно​ случае одного критерия,​ ячейки, которые следует​ и насколько удобной​Если вы раньше не​?????​​ по другому через​​ А1:С5 нужно прописать​ как учитывается значение,​ критерия (например, больше​ последовательность любых символов идущих правее.​ применяются к значениям​ двумя столбцами: текстовым​ свои собственные, чтобы​

excel2.ru

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

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

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

Задача

​ в ячейке или​СУММЕСЛИ​  по сравнению с​​Найдем число партий товара​​ «Количество на складе»​ изменятся результаты.​После этого необходимо найти​ складе​ (ящиков).​ затем записать формулу =СЧЁТЕСЛИ(B20:B30;2)​​ для подсчета неповторяющихся​​Условие 2 – определяет​ задач.​ как формулы массива,​ которых фамилия начинается​ короче.​ даст результат 8.​ введено в числе​(SUMIF). В данном​ предыдущей задачей не​ с Датой поступления на​ (См. файл примера).​Скопируйте все ячейки из​ данные, которые соответствуют​не более​В качестве исходной таблицы​

​Обычно, в качестве аргумента​ значений и вычисления​ ячейки, которые функция​

​Имеем таблицу, в которой​ то советую почитать​

1. ​ на букву «П»,​
2. ​Пафнутий​
3. ​ Именно столько заполненных​

​ аргументов.​ случае для проверки​ изменится, часть альтернативных​ склад не ранее 25.10.2012 и не​СУММЕСЛИМН(​

​ приведенной ниже таблицы​ двух условий, поэтому​

• ​57 (ящиков).​ возьмем таблицу с​критерий​ ранга.​ выделит из второго​ указаны фамилии сотрудников,​ предварительно про них​
• ​ а заканчивается на​
• ​: помогите пожалуйста решить​ ячеек есть в​Числа, даты и время​ условия и суммирования​ решений работать не​ позднее 24.12.2012 (строка таблицы соответствует​диапазон_условия1; условие1; [диапазон_условия2; условие2]…​ и вставьте их​ введите свой первый​Это потребует незначительного изменения​
• ​ двумя столбцами: текстовым​у функции СЧЁТЕСЛИ()​Синтаксис​ диапазона условия.​ их пол и​ много хорошего здесь.​ «В» — критерий​​ проблему. как стандартными​​ обозначенной области.​

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

​ условие, указав для​ формул (условие «>=»&E2​ «Фрукты» и числовым​ указывают только одно​СЧЁТЕСЛИдиапазонкритерий​​И так далее. В​​ зарплата, начисленная за​ Ну, а в​​П*В​​ средствами Excel задать​

​Чтобы узнать количество пустых​ СЧЁТ.​ поэтому в функции​

​ здесь).​ поле Дата поступления удовлетворяет обоим​Диапазон_условия1​ нового листа Excel.​ функции расположение данных​ нужно переписать как «​

​ «Количество на складе»​​ значение. Например, =СЧЁТЕСЛИ(H2:H11;I2).​)​ зависимости от количества​ январь-месяц. Если нам​ нашем случае задача​. Строчные и прописные​

excel2.ru

Суммирование значений с учетом нескольких условий

​ для занчения больше​​ клеток, нужно ввести​Текст и ошибки никогда​ достаточно заполнить всего​Рассмотрим задачу, когда 1​ критериям одновременно). ​. Первый диапазон, в котором​ Вы можете отрегулировать​ (a2: a11), а​На листе Пример2 файла​ (См. файл примера).​ Если в качестве​Диапазон​ критериев, число аргументов​ нужно просто посчитать​ решается одной формулой:​ буквы не различаются.​ чем 7 условий.​ функцию СЧИТАТЬПУСТОТЫ:​ не учитываются.​ два аргумента:​ текстовый критерий применяются​

​В качестве исходной таблицы​ необходимо проверить соответствие​ ширину столбцов, чтобы​ также какие условием​ примера приведено универсальное​Подсчитаем строки, в которых​ критерия указать ссылку​    — диапазон, в котором​ может увеличиваться в​

​ общее количество денег,​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​Диапазон_суммирования​ Как обойти это​Введение формулы покажет результат​​Логические значения и текстовое​​=СУММЕСЛИ(B1:B5;»>9″)​​ к значениям текстового​​ возьмем таблицу с​ заданному условию1;​ формулы лучше отображались.​

​ является — наилучшим​ решение, которое позволяет​

​ в столбце Фрукты​

​ на целый диапазон​

​ нужно подсчитать ячейки,​ арифметической прогрессии с​

​ которые требуется выдать​После ввода этой формулы​- это те​ ограничение без VBA​ 7. Это количество​ представление числа не​=SUMIF(B1:B5,»>9″)​

​ столбца, а 2​

​ двумя столбцами: текстовым​Условие1​Регион​ образом «Южный». Обратите​ не модифицировать формулы,​ значится Персики​ ячеек с критериями,​​ содержащие числа, текст​​ шагом 2. Т.е.​ работникам, мы используем​ необходимо нажать не​ ячейки, значения которых​ ?​

​ пустых ячеек в​ учитывается, если оно​Чтобы суммировать значения ячеек​ других (числовых) — к​ «Фрукты» и «Дата​. Условие в форме числа,​Продавец​ внимание, запятые между​ а лишь менять​ИЛИ​ то функция вернет​ или даты.​

​ 5, 7, 9…​

​ функцию СУММ, указав​ Enter, как обычно,​ мы хотим сложить,​

​Guest​ таблице.​ хранится в ячейке.​ на основе одного​ значениям столбца с​ поступления» (см. файл примера, лист 2Даты).​ выражения, ссылки на​Что следует ввести​ отдельные аргументы:​ знаки сравнения.​​строки с остатком​​ массив. В файле​Критерий​Предположим, нам нужно подсчитать​

​ диапазоном все заработные​

​ а​ т.е. нашем случае -​: Промежуточными строками или​Очень часто используется такая​ Если эти же​ критерия (например, «green»),​​ числами (см. файл примера, лист 1текст​​Так как даты хранятся​ ячейку или текста,​Продажи​= SUMIFS(D2:D11,A2:A11,»South»,​

​Примечание​ на складе не​ примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18)​    — критерий в форме​ сумму заработных плат​ платы.​Ctrl + Shift +​ стоимости заказов.​ столбцами, которые можно​ разновидность функции «СЧЁТ».​ элементы ввести в​ также используйте функцию​ 2числовых).​ в EXCEL в​​ которые определяют, какие​​Южный​

​Кавычки вокруг слова «Южный»​: подсчет значений с​ менее 57 (ящиков). Отбираются​ возвращает массив {3:2:5}.​ числа, выражения, ссылки​ за январь всех​​Но как быть, если​​ Enter​Если условий больше одного​ скрыть.​ С помощью заданной​​ формулу в качестве​​СУММЕСЛИ​

​Найдем число партий товара​​ числовом формате, то​​ ячейки требуется учитывать.​Орехов​

Попробуйте попрактиковаться

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

​ продавцов-женщин. У нас​ нам нужно быстро​- тогда Excel​ (например, нужно найти​Barracuda​ формулы можно узнать​ аргумента, они будут​(SUMIF). В данном​ Яблоки с Количеством ящиков​

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

​ текстовый критерий применяются​​Диапазон_условия2, условие2… ​

• ​Молочные продукты​ C11), содержащий слово​

• ​Примечание:​ Количество ящиков на​ критерии. В Строке​ следующим образом: 32,​Значит, будем применять команду​Прописываем аргументы.​

support.office.com

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

​ с клавиатуры не​не поможет, т.к.​или мозгами​ учитываться такие аргументы.​​

​ нужно заполнить три​ поле Фрукт совпадает с критерием Яблоки,​ к значениям текстового​   Необязательные аргументы. Дополнительные диапазоны​3338​ «Мясо», плюс само​

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

​Мы стараемся как​​ складе значение >=57 (как​​ формул введите формулу​

• ​ «32», «>32», «яблоки»​​ СУММЕСЛИМН.​Диапазоном в данном случае​ надо. Легко сообразить,​
• ​ не умеет проверять​​vikttur​Диапазон. Табличная область, в​Пример 1. В таблицу​ аргумента, последний –​ и когда другое​ столбца, а другой​ и условия для​Восточный​ слово (заключено в​
• ​ можно оперативнее обеспечивать​​ бы совершаетcя 2​ и нажмите​ или​Прописываем аргументы.​ будет являться список​

​ что этот способ​ больше одного критерия.​: С помощью дополнительных​ которой будут искаться​ введены некоторые значения.​ это диапазон для​ поле Количество ящиков на​ (числовой) — к значениям​

​ них. Разрешается использовать​Песоцкий​ кавычки) таким образом,​ вас актуальными справочными​ прохода по таблице: сначала​CTRL+SHIFT+ENTER​B4​диапазон суммирования – ячейки​ всех должностей сотрудников,​ (как и предыдущий)​ Поэтому начиная с​ ячеек.​

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

​ определённые элементы.​ Среди них есть​ суммирования.​ складе одновременно >=10 и​ столбца с числами (см. файл​ до 127 пар​

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

​ версии Excel 2007​Guest​Критерий. Признак, который разыскивается​

​ текст, числовые выражения​=СУММЕСЛИ(A1:A5;»green»;B1:B5)​

​Для наглядности, строки в​ примера, лист 1текст 1числовой).​ диапазонов и условий.​5122​ его. Завершить формулу​​ языке. Эта страница​​ по полю Фрукты,​ как формулу массива.​Данные будем брать из​диапазон условия 1 –​

​ нужно будет определить​ три, четыре и​

• ​ в набор функций​
• ​: С помощью дополнительных​
• ​ в заданной области.​
• ​ и число, заключённое​=SUMIF(A1:A5,»green»,B1:B5)​ таблице, удовлетворяющие критериям,​

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

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

​ затем по полю​Это свойство функции СЧЁТЕСЛИ()​ диапазона​ ячейки с указанием​ сумму заработных плат.​ т.д. условий без​ была добавлена функция​

​ ячеек.{/post}{/quote}​Синтаксис выглядит так:​ в кавычки «22».​Для суммирования значений ячеек​ выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2;$B2​

​ Яблоки с Количеством ящиков​ состоять из такого​Песоцкий​)​ ее текст может​ Количество ящиков на​

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

​ используется в статье​A15:A25​ должности сотрудника;​ Поэтому проставляем E2:E14.​ каких-либо ограничений.​СУММЕСЛИМН (SUMIFS)​vikttur​

​Функция может показать количество​ Именно его и​ на основе нескольких​Формулу запишем в следующем​ на складе не менее 10 (строка​ же количества строк​Молочные продукты​и нажмите клавишу​ содержать неточности и​

​ складе, строки в​ Отбор уникальных значений.​(см. файл примера).​

​условия 1 – продавец;​Критерий выбора в нашем​

​В категории​- в ней​: Функцией ВЫБОР()​ ячеек с заданным​ считает программа Excel​​ критериев (например, «blue»​​ виде: =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2;B2:B13;»​ таблицы соответствует критерию,​ и столбцов, что​6239​

​ ВВОД. Результат, снова​ грамматические ошибки. Для​

• ​ которых оба поля​
• ​Возможность задать в качестве​
• ​Критерий​
• ​диапазон условия 2 –​ случае – продавец.​Базы данных (Database)​

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

​ количество условий проверки​Имеем таблицу по продажам,​ текстом. Для этого​ текстовым значением в​ и «green»), используйте​Альтернативными решениями задачи являются​ когда ее поле​ и аргумент диапазон_условия1. Эти​Южный​

​ нажмите — 14,719.​​ нас важно, чтобы​​ удовлетворяют критериям во​ критерия несколько значений​Формула​

​ ячейки с указанием​ Заключаем слово в​ можно найти функцию​ увеличено аж до​ например, следующего вида:​ аргумент заключается в​ ячейке.​ функцию​ следующие формулы:​ Фрукт совпадает с​

​ диапазоны могут не​Маринова​​=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»)​​ эта статья была​ второй проход не​ открывает дополнительные возможности​

​Результат​ пола сотрудника;​ кавычки и ставим​БДСУММ (DSUM)​ 127! Функция находится​Задача​ кавычки. При этом​

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

​Пропишем формулу, она будет​СУММЕСЛИМН​=СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13​ критерием Яблоки, и​ находиться рядом друг​Сельхозпродукты​По мере ввода функции​ вам полезна. Просим​

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

​(SUMIFS). Первый аргумент​формула массива =СУММ((A2:A13=D2)*(B2:B13<>=E2))​ когда другое поле Количество​

​ с другом.​8677​

​ СУММЕСЛИМН в Microsoft​ вас уделить пару​

• ​ было задвоения)).​
• ​В файле примера на​
• ​>=10​
• ​ (ж).​Диапазон суммирования – это​ решить нашу задачу.​

​ категории​ которые менеджер Григорьев реализовал​ регистр. В синтаксисе​

excel2.ru

Функции СЧЁТ и СУММ в Excel

• ​ ячеек, и будет​
• ​ – это диапазон​
• ​формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13​
• ​ ящиков на складе >=10). ​
• ​В условиях можно использовать​
• ​Южный​

​ Excel, если вы​ секунд и сообщить,​Для наглядности, строки в​ листе Специальное применение​=СЧЁТЕСЛИ($A$2:$A$12; «>=10»)​Итог: все продавцы-женщины в​ заработные платы, потому​ Нюанс состоит в​

СЧЁТ

​Математические​ для магазина «Копейка».​ формулы не может​​ выглядеть таким образом:​​ для суммирования.​

​=БСЧЁТА(A1:B13;A1;D14:F15) или БСЧЁТ(A1:B13;B1;D14:F15), которые требуют наличия​
​Для наглядности, строки в​

СЧЕТЕСЛИ

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

​4​
​ январе получили в​

СЧЁТЕСЛИМН

​ что нам нужно​ том, что для​и работает похожим​Если бы в нашей​ быть пробелов.​​После введения формулы нажатием​​=СУММЕСЛИМН(C1:C5;A1:A5;»blue»;B1:B5;»green»)​

​ отдельной таблички с​
​ таблице, удовлетворяющие критериям,​

СУММ

​ знак (?) и​Мясо​​ справки готов под​​ вам, с помощью​

​ выделяются Условным форматированием с правилом =ИЛИ($A2=$D$2;$B2>=$E$2)​
​ помощью функции СЧЁТЕСЛИ() вычислить количество​

СУММЕСЛИ

​Подсчитывает количество ячеек, содержащих​ сумме 51100 рублей.​ узнать сумму зарплат​ работы этой функции​​ образом, но имеет​​ задаче было только​Оба аргумента являются обязательными​ клавиши Enter, появится​=SUMIFS(C1:C5,A1:A5,»blue»,B1:B5,»green»)​ критериями.​ выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)​ звездочку (*). Вопросительный​

​450​
​ рукой. После ввода​

​ кнопок внизу страницы.​Для удобства создадим 3​ повторов каждого значения​ числа равных или​​Функции СУММЕСЛИ и СУММЕСЛИМН​​ всех продавцов. Поэтому​ необходимо создать на​ больше аргументов:​ одно условие (все​ для указания. Для​ число 4. Именно​Примечание:​О подсчете с множественными​Формулу запишем в следующем​

​ знак соответствует любому одиночному​
​Южный​

СУММЕСЛИМН

​= СУММЕСЛИМН (​ Для удобства также​ динамических диапазона:​ в списке.​ более 10. Критерий​​ хороши тем, что​​ F2:F14.​ листе специальный диапазон​При помощи полосы прокрутки​

​ заказы Петрова или​
​ наглядности стоит рассмотреть​

​ столько чисел введено​​Аналогичным образом можно​ критериями можно почитать​​ виде: =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2)​​ символу; звездочка —​​Егоров​​, автозавершение формул появится​ приводим ссылку на​Количество: =СМЕЩ(пример1!$B$2;;;СЧЁТЗ(пример1!$A$2:$A$15))​Выражение СЧЁТЕСЛИ(A6:A14;A6:A14) возвращает массив чисел {1:4:4:4:4:1:3:3:3},​

​ указан в формуле​ они автоматически подстраиваются​
​Получилось 92900. Т.е. функция​
​ критериев — ячейки, содержащие​

​ в правой части​

office-guru.ru

Примеры работы функций СЧЁТ, СЧИТАТЬПУСТОТЫ и СЧЁТЕСЛИ в Excel

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

Где используется и как работает функция СЧЁТ?

​ любой последовательности символов.​Мясо​ под формулы, в​ оригинал (на английском​Фрукты: =СМЕЩ(пример1!$A$2;;;СЧЁТЗ(пример1!$A$2:$A$15))​ который говорит о​(ячейка С3)​ под изменение условий.​ автоматически проработала список​

​ условия отбора — и​

• ​ окна можно задать​ «Копейку», например), то​Пример 3. Есть ведомость​
• ​ помощи цифр, которые​СРЗНАЧЕСЛИ​
• ​СЧЁТ​

​ диапазон, к которому​ Если нужно найти​

​7673​

​ списке аргументов в​ языке) .​Таблица: =СМЕЩ(пример1!$A$1;;;СЧЁТЗ(пример1!$A$1:$A$15);2)​ том, что значение​=СЧЁТЕСЛИ( $A$2:$A$12;C3)​ Т.е. мы можем​ должностей, выбрала из​ указать затем этот​ и третью пару​ задача решалась бы​ с фамилиями студентов​ не заключены в​(AVERAGEIF) и​СЧЁТЕСЛИ​

1. ​ применяется первый критерий​ сам вопросительный знак​Восточный​
2. ​ их правильном порядке.​Допустим, вам нужно суммировать​
3. ​Это позволит в дальнейшем​ 1 из списка​8​ изменить данные в​ них только продавцов​ диапазон функции как​ (​ достаточно легко при​ и оценками за​

​ кавычки.​СРЗНАЧЕСЛИМН​СЧЁТЕСЛИМН​

​ (>=10 или «>=»&D2) это​

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

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

​Диапазон_условия3​ помощи встроенной функции​ экзамен. В таблице​Теперь введём в ячейку​

​(AVERAGEIFS), чтобы рассчитать​СУММ​ диапазон ​ перед ними знак​Сельхозпродукты​ использования функции автозавершения​ чем одного условия,​ исходную таблицу и​

​А6:А15​ числа равных или​ будут изменяться вместе​ зарплаты.​=БДСУММ(A1:D26;D1;F1:G2)​-​ Excel​

​ 2 столбца и​ ниже D2, другую​ среднее значение ячеек​СУММЕСЛИ​B2:B13​

​ тильды (~).​664​

​ формул и списка​ например сумму продаж​ не переписывать формулы​- единственное, также​ меньших 10. Критерий​ с ними. Например,​Аналогично можно подсчитать зарплаты​Суммировать в программе Excel​Условие3​

Пример использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel

​СУММЕСЛИ (SUMIF)​ 10 ячеек. Нужно​ функцию. В ней​ на основе одного​СУММЕСЛИМН​. Первый и второй​Рассмотрим задачу, когда 2​Северный​ аргументов, то в​ товара в определенной​

​ для подсчета значений.​ в диапазоне 4​ указан через ссылку​ при подсчете заработных​ всех менеджеров, продавцов-кассиров​ умеет, наверное, каждый.​

​), и четвертую, и​ из категории​ определить, какое количество​ все табличные элементы​ или нескольких критериев.​Самые часто используемые функции​

​ диапазон в данном​ числовых критерия применяются​Егоров​ нашем примере диапазон_суммирования​ области. Это хороший​

​Подсчет можно реализовать множеством​ значения 2, одно​>=​

​ плат оказалось, что​ и охранников. Когда​ Но с усовершенствованной​ т.д. — при​

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

​Математические (Math&Trig)​ студентов получили отличную​ будут прописаны в​Урок подготовлен для Вас​ в Excel –​ случае совпадают, т.к.​ к значениям одного​Сельхозпродукты​ — это диапазон​

1. ​ обращения для использования​ формул, приведем несколько:​ значение 3, три​
2. ​(ячейка С4)​ мы забыли учесть​

​ табличка небольшая, кажется,​

​ версией команды СУММ,​ необходимости.​. Выделяем пустую ячейку​ оценку 5 (по​ виде аргументов. Она​ командой сайта office-guru.ru​ это функции, которые​ 2-й критерий  (B2:B13.​ из столбцов (см.​

​1500​ D2:D11, представляющий собой​ функции СУММЕСЛИМН в​Формула =СЧЁТЕСЛИ(Фрукты;D2)+СЧЁТЕСЛИ(Количество;»>=»&E2)-СЧЁТЕСЛИМН(Фрукты;D2;Количество;»>=»&E2) с помощью 2-х​

​ значения 4. Это​11​ одну сотрудницу, которая​ что все можно​ которая называется СУММЕСЛИ,​Если же у вас​ для результата, жмем​ пятибалльной системе оценивания),​ будет выглядеть так:​Источник: http://www.excel-easy.com/functions/count-sum-functions.html​ подсчитывают и складывают.​Альтернативными решениями задачи являются​ файл примера, лист​

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

​ существенно расширяются возможности​ пока еще старая​ кнопку​

• ​ а какое 4,​
• ​=СЧЁТ(12; мост; кг; крыло;​Перевела: Ольга Гелих​
• ​ Подсчитывать и складывать​
• ​ следующие формулы:​

• ​ 2числовых).​Маринова​ которые вы хотите​Просмотрите в этом примере,​

  exceltable.com

Двойное условие ЕСЛИ?

​ удовлетворяющие каждому из​​ неповторяющихся значений формулой​
​=СЧЁТЕСЛИ($A$2:$A$12; C4&C5)​ можем добавить еще​ но при работе​ данной операции.​ версия Excel 2003,​
​fx​
​ потом 3 и​

​ 33; 45678; «22»;​​Автор: Антон Андронов​

​ (суммировать) можно на​​=СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2))​Найдем число партий товара​Мясо​ просуммировать; диапазон_условия1 —​ в котором у​

​ критериев, затем вычитается​​ =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A6:A14;A6:A14)=1)). ​3​ одну строчку через​ со списками, в​По названию команды можно​ но задачу с​в строке формул,​ 2.​

​ сила; ампер; 16.02.1999;​​Количества чисел в таблице​ основе одного или​формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))​

​ с Количеством ящиков на​​6596​ это диапазон A2.A11,​

​ нас есть двумя​​ количество строк удовлетворяющих обоим​
​Формула =СЧЁТЕСЛИ(A6:A14;»А6:А15. В этом можно​

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

​ понять, что она​​ несколькими условиями решить​ находим функцию​

​Для определения количества отличников​​ 14.07.1975; стена; потолок;​

planetaexcel.ru

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

​ Excel можно быстро​ нескольких критериев.​

​формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13))​​ складе не менее​Формула​ представляющий собой столбец​

Способ 1. Функция СУММЕСЛИ, когда одно условие

​ условиями: необходимо узнать​ критериям одновременно (функция​ убедиться, выделив формулу​ числа равных или​ и команду ВСТАВИТЬ.​ сотен позиций, целесообразно​ не просто считает​ нужно, то придется​СУММЕСЛИ​ нужно провести анализ​​ 21)​​ выяснить, используя функцию​​Для подсчета количества ячеек,​​=БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют наличия​ 10 и не более​Описание​​ с данными, в​​ сумму продаж​ СЧЁТЕСЛИМН()).​​ в Строке формул​​ более 11. Критерий​

​У нас появилась дополнительная​​ использовать СУММЕСЛИ.​​ сумму, но еще​

• ​ извращаться — см.​​в списке:​ содержимого ячеек второго​Введя формулу, мы получим​​ «Счёт». Игнорируя текстовый​​ которые содержат числа,​ отдельной таблички с​ 50 (строка таблицы соответствует​Результат​
• ​ котором находится​​мяса​Вместо 2-х функций СЧЁТЕСЛИ() можно использовать формулу =СУММПРОИЗВ((Фрукты=D2)+(Количество>=E2))-СЧЁТЕСЛИМН(Фрукты;D2;Количество;»>=»&E2)​ и нажав клавишу​ указан через ссылку​ строчка. Сразу обращаем​Если к стандартной записи​ и подчиняется каким-либо​ следующие способы.​Жмем​ столбика. В отдельной​ значение 5.​ формат, она учитывает​ используйте функцию​ критериями.​ критерию, когда ее​’ = СУММЕСЛИМН (a2:​условие1​в​​Формула =БСЧЁТ(Таблица;B1;D13:E15) требует предварительного создания​​ F9. Значения совпадут​ и параметр​ внимание, что диапазон​ команды СУММЕСЛИ в​ логическим условиям.​Добавим к нашей таблице​​ОК​​ табличке нужно использовать​Разные результаты функции объясняются​
• ​ только числовые значения.​​СЧЁТ​Рассмотрим задачу, когда 1​ поле Количество ящиков​ a11; D2: D11​— “Южный”.​

Способ 2. Функция СУММЕСЛИМН, когда условий много

​южном​ таблички с условиями.​ с вычисленным рангом​Примечание​ условий и суммирования​​ конце добавляются еще​​Функция СУММЕСЛИ позволяет суммировать​ еще один столбец,​ и вводим ее аргументы:​ простую функцию подсчета​ тем, что одно​Часто, для анализа табличных​(COUNT).​​ текстовый критерий с​​ на складе удовлетворяет обоим​«Южно» C2: C11,​По мере того, как​районе (из столбца​ Заголовки этой таблицы​ в столбце​​. О подсчете значений,​​ автоматически расширился до​ две буквы –​ ячейки, которые удовлетворяют​

​ который будет служить​Диапазон​ количества числовых значений​ число («22») воспринимается​ данных не нужно​​=СЧЁТ(A1:A5)​​ подстановочным знаком применяются​​ критериям одновременно). ​​ «Мясо»)​ вы вводите формулу,​ A) (из столбца​

​ должны в точности​В​ удовлетворяющих нескольким критериям​ 15 строки.​ МН (СУММЕСЛИМН), значит,​ определенному критерию (заданному​ своеобразным индикатором: если​- это те​

Способ 3. Столбец-индикатор

​ с условием СЧЁТЕСЛИ:​ программой, как текст,​ производить никаких математических​=COUNT(A1:A5)​ к значениям текстового​Для наглядности, строки в​Суммирует продаж мяса в​ в автозавершении формулы​ C).​ совпадать с заголовками​(с помощью функции​ читайте в статье​Копируем данные сотрудника и​

​ подразумевается функция с​

​ условию). Аргументы команды​ заказ был в​ ячейки, которые мы​После нажатия на клавиатуре​ если оно находится​ операций с числами.​Для подсчета ячеек по​ столбца, а другой​ таблице, удовлетворяющие критериям,​Столбец C в​ появятся остальные аргументы​Вот формулу можно использовать​ исходной таблицы. Размещение​ РАНГ()). Этот подход​ Подсчет значений со​ вставляем их в​

Способ 4. Волшебная формула массива

​ несколькими условиями. Она​ следующие:​ «Копейку» и от​ проверяем на выполнение​ Enter будет получен​ в ячейке, и​ Достаточно уточнить количество​ одному критерию (например,​ (числовой) — к значениям​ выделяются Условным форматированием с правилом =И($B2>=$D$2;$B2​

​ южном​

​ (здесь они не​ для acomplish это:​ условий в разных​ применен в статьях Динамическая​​ множественными критериями. О​ общий перечень. Суммы​​ применяется в случае,​Диапазон – ячейки, которые​ Григорьева, то в​Критерия​ результат:​ как число, если​ показателей. Именно для​ больше 9), используйте​ столбца с числами (см. файл​Формулу запишем в следующем​область в столбце​ показаны);​=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»)​

Способ 4. Функция баз данных БДСУММ

​ строках соответствует Условию​​ сортировка таблицы в​​ подсчете чисел с​​ в итоговых ячейках​​ когда нужно задать​ следует оценить на​ ячейке этого столбца​. В нашем случае​5 отличников;​ оно прописано в​ такого случая пригодится​ функцию​ примера, лист 1текст (с​ виде: =СЧЁТЕСЛИМН(B2:B13;»>=»&D2;B2:B13;»​ A (результат —​диапазон_условия2​

​Результатом является значение 14,719.​

planetaexcel.ru

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

​ ИЛИ (см. статью​ MS EXCEL и Отбор уникальных​ более чем 15​ изменились. Функции среагировали​ не один критерий.​ основании критерия (заданного​ будет значение 1,​

​ — это диапазон​3 студента с оценкой​ числе аргументов функции.​ эта простая функция​СЧЕТЕСЛИ​ подстанов) 1числовой).​

СУММЕСЛИ и ее синтаксис

​В формуле предполагается, что​ 14 719).​— это диапазон​Рассмотрим изучить все части​ Функция БСЧЁТ()).​

1. ​ значений с сортировкой​ значащих цифр читайте​ на появление в​Аргументов у СУММЕСЛИМН может​
2. ​ условия).​ иначе — 0.​ с фамилиями менеджеров​ 4 балла;​
3. ​Вы сможете быстро определить​ СЧЁТ.​(COUNTIF).​Найдем число партий товара​

​ диапазон, к которому​= SUMIFS(D2:D11,A2:A11,​ C2:C11, представляющий собой​ формулы.​Также можно использовать формулу =БСЧЁТА(Таблица;A1;D13:E15)​ в MS EXCEL.​ статью Подсчет ТЕКСТовых значений​ диапазоне еще одного​

​ быть сколько угодно,​

Как работает функция СУММЕСЛИ в Excel?

​Критерий – определяет, какие​ Формула, которую надо​ продаж.​2 троечника;​ количество заполненных или​Аргументом функции может быть:​=СЧЁТЕСЛИ(A1:A5;»>9″)​

​начинающихся​ применяется первый критерий​«Южно» C2: C11,​ столбец с данными,​= СУММЕСЛИМН является формулой​ с теми же​Произведем подсчет строк таблицы,​ с единственным критерием​ продавца-женщины.​ но минимум –​ ячейки из диапазона​ ввести в этот​

​Критерий​ни одного двоечника.​ пустых клеток в​любые значения, скопированные и​=COUNTIF(A1:A5,»>9″)​со слова Яблоки и​

​ (>=10 или «>=»&D2)​

1. ​ «Мясо»)​ в котором находится​ арифметического. Он вычисляет​ условиями, но нужно​ значения которых удовлетворяют​ в MS EXCEL.​Аналогично можно не только​
2. ​ это 5.​ будут выбраны (записывается​ столбец очень простая:​- это то,​Так, всего за несколько​
3. ​ заданной области таблицы.​ поставленные на место​Чтобы подсчитать ячейки, основываясь​ с Количеством ящиков на​ это диапазон ​Примечания:​

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

​Диапазон суммирования. Если в​ в кавычках).​=(A2=»Копейка»)*(B2=»Григорьев»)​ что мы ищем​ секунд, можно получить​ Для выполнения этих​ аргумента;​ на нескольких критериях​ складе не менее 10. ​B2:B13​ ​

Функция СУММЕСЛИ в Excel с несколькими условиями

​— “Мясо”.​ в этом случае​ подсчета строк, он​ которые образуют Условие​ текстовых значений (см.​ удалять какие-либо строки​ СУММЕСЛИ он был​Диапазон суммирования – фактические​Логические равенства в скобках​ в предыдущем указанном​

Синтаксис с использованием функции по нескольким критериям

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

1. ​В отличие от задачи​. Первый и второй​Дополнительные примеры можно найти​Если вы нажмете​ в столбце D.​ должен быть текстовым,​ ИЛИ. Например, в​ Подсчет ТЕКСТовых значений​
2. ​ (например, при увольнении​ в конце, то​ ячейки, которые необходимо​ дают значения ИСТИНА​
3. ​ диапазоне. Разрешается использовать​ и сложным таблицам.​ с именами «СЧЁТЗ»​ область таблицы;​
4. ​ и больше 9),​ 3, в исходной​ диапазон в данном​ в статьеФункция СУММЕСЛИМН.​
5. ​СУММЕСЛИМН​ Первым делом нужно​ т.е.​ таблице с перечнем​

​ с единственным критерием​ сотрудника), изменять значения​ здесь он стоит​ просуммировать, если они​ или ЛОЖЬ, что​ символы * (звездочка)​Сергей​

Пример использования

​ и «СЧИТАТЬПУСТОТЫ». Для​формулы и функции.​ применяйте функцию​ таблице присутствуют фрукты​ случае совпадают, т.к.​Если вы хотите подсчитать​

• ​в автозавершении формулы,​
• ​ указать расположение номера:​

​А​ Фруктов и их​

​ в MS EXCEL).​

• ​ (заменить «январь» на​ на первом месте.​
• ​ удовлетворяют критерию.​ для Excel равносильно​ и  ? (вопросительный​
• ​: Помогите с проблемкой:​
• ​ наглядности этой возможности​Синтаксис функции выглядит следующим​СЧЁТЕСЛИМН​
• ​ с более сложными​ 2-й критерий  (B2:B13.​

​ итоговое значение только​ откроется статья, справкой.​=СУММЕСЛИМН(D2:D11;​

СУММЕСЛИ в Excel с динамическим условием

​. ​ количеством на складе,​Так как любой дате​ «февраль» и подставить​ Он также означает​Получается, что у функции​ 1 и 0.​ знак) как маски​Например нужно проверить​ необходимо рассмотреть пример.​ образом:​(COUNTIFS).​ названиями: яблоки свежие,​Альтернативными решениями задачи являются​ для одного диапазона​Если вы хотите поэкспериментировать​Другими словами нужно ввести​Альтернативным решением​

​ отберем строки, в​ в MS EXCEL​ новые заработные платы)​ ячейки, которые необходимо​ всего 3 аргумента.​ Таким образом, поскольку​

​ или символы подстановки.​ не является ли​Пример 2. В таблицу​=СЧЁТ(аргумент1; аргумент2; …)​=СЧЁТЕСЛИМН(A1:A5;»green»;B1:B5;»>9″)​ персики сорт2. Чтобы​ следующие формулы:​ с учетом значения​

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

exceltable.com

​ Звездочка подменяет собой​

Функция СЧЁТЕСЛИ в Excel с разными условиями: мы объясняем это на примерах.

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

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

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

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

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

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

Синтаксис функции следующий:

СЧЁТЕСЛИ (интервал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, потому что только Plum имеет значение «0» в обоих столбцах.

Использование упрощенной версии с ограничением выбора, например = COUNTIFS (B2: D11; 0), даст другой результат: общее количество ячеек в B2: D11, содержащих ноль (в этом примере это 5).

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

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

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

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

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

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

Если вам нужно оценить более одного параметра выбора, используйте COUNTPIIF.

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

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

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

В ситуациях, когда вам нужно оценить множество критериев, описанный выше подход — не лучший вариант, потому что ваша формула станет слишком громоздкой. Чтобы выполнить те же вычисления в более компактной форме, перечислите все критерии в константе массива и предоставьте этот массив в качестве аргумента функции COUNTPIIF.

Введите СЧЁТЕСЛИМН в функцию СУММ, например:

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

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

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

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

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

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

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

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

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

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

= СЧЁТЕСЛИ (D2: D11; «> 10»; D2: D11; «

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

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

= СЧЁТЕСЛИ (G2: D11; «> 10») — СЧЁТЕСЛИ (G2: D11; «> 20»)

Это выражение вернет ту же сумму, что и на изображении выше.

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

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

рис6

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

= СЧЁТЕСЛИ (B2: B11; « 30″)

или

= СЧЁТЕСЛИ (B2: B11; «» & G2)

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

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

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

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

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

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

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

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

= СЧЁТЕСЛИ (B2: B21; «*»; E2: E21;»»&»»)

Обратите внимание, что в первом критерии мы используем подстановочный знак *, поскольку мы рассматриваем текстовые значения (фамилии). По второму критерию мы разбираем даты, потом пишем по-другому: «» & «» (значит — не равно пустому значению).

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

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

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

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

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

= СЧЁТЕСЛИ (G2: G21; «> = 01.02.2020»; G2: G21; «

или

= СЧЁТЕСЛИ (G2: D21; «> =» & H3; D2: D21; «

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

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

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

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

а также

= СЧЁТЕСЛИ (G2: D21; «> = 01.02.2020»; E2: E21; «> = 05.02.2020»)

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

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

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

= СЧЁТЕСЛИ (G2: D21; «» & СЕГОДНЯ())

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

= СЧЁТЕСЛИ (G2: D21; «» & СЕГОДНЯ())

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