счет если с двумя условиями

Функция СЧЁТЕСЛИМН в Excel с несколькими условиями — объясняем на примерах.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

) перед звездочкой или знаком вопроса в записи параметра поиска.

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

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

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

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

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

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

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

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

=СЧЁТЕСЛИМН(D2:D21;»>=01.02.2020″;D2:D21;» =»&H3;D2:D21;»

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

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

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

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

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

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

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

Источник

СЧЁТЕСЛИ (функция СЧЁТЕСЛИ)

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

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

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

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

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

Например, критерий может быть выражен как 32, «>32», В4, «яблоки» или «32».

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

Примеры

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

Количество ячеек, содержащих текст «яблоки» в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст «персики» (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек, содержащих текст «яблоки» (значение ячейки A2) и «апельсины» (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 3.

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак «*» обозначает любое количество любых символов. Результат — 4.

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами «ки», в диапазоне A2–A5. Подставочный знак «?» обозначает отдельный символ. Результат — 2.

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

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

Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;»длинная строка»&»еще одна длинная строка»).

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

Формула СЧЁТЕ ЕСЛИ получает #VALUE! при ссылке на другой таблицу.

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

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

Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках.

Критерий не чувствителен к регистру. Например, строкам «яблоки» и «ЯБЛОКИ» будут соответствовать одни и те же ячейки.

Использование подстановочных знаков

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

) перед искомым символом.

Например, =СЧЁТЕСЛИ(A2:A5;»яблок?») возвращает все вхождения слова «яблок» с любой буквой в конце.

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

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

Для удобства используйте именованные диапазоны.

Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.

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

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

Источник

Excel-plus

Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel

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

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

В данной статье рассмотрим варианты использования функции СЧЁТЕСЛИ с двумя (несколькими) критериями поиска. Более подробная информация с описанием функции СЧЁТЕСЛИ, ее возможностями и примерами использования, находиться по ссылке: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры

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

СЧЁТЕСЛИ с двумя критериями. Числовое значение.

Найдем количество ячеек в столбце Числа, числовые значения в которых, больше числа 50 и меньше числа 50. Сначала, используя функцию СЧЁТЕСЛИ, мы находим, количество ячеек, значения в которых больше числа 50. Можно сначала найти количество ячеек, с значением меньше числа 50. Это не имеет значение. Как это сделать описано в статье: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры. Теперь в строке формулы добавляем плюс «+», и еще раз используем функцию СЧЁТЕСЛИ. Диапазон тот же самый, но в критерии поиска задаем поиск значений меньше числа 50. Получаем общие количество ячеек, значение в которых больше и меньше числа 50.

Можно использовать и другие варианты: больше или равно, меньше или ровно, равно, не равно. В зависимости от задачи.

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

СЧЁТЕСЛИ с двумя критериями. Ссылка на ячейку.

В качестве критерия поиска, можем использовать значения, содержащиеся в ячейке, указывая ссылку на эту ячейку в поле Критерии, диалогового окна Аргументы функции. Вернемся к нашей таблице и найдем количество ячеек в столбце Числа, числовые значения в которых, больше числа 50 и меньше числа 50. При этом используем в качестве критериев поиска ссылки на ячейки. Используем ячейку С7, как одну из ячеек, в которой находиться число 50.

Получили результат 12 ячеек.

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

СЧЁТЕСЛИ с двумя критериями. Текстовые критерии.

В качестве критерия поиска, в данном варианте, можно использовать текстовое значение (слово) или часть слова. Как работает функции при использование таких критериев поиска, можно узнать по ссылке: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры. Объединение нескольких критериев поиска в данном варианте происходит по такому же принципу, как и в вариантах, описанных выше. Возьмём для примера столбец, в котором содержаться название мебели. Найдем количество ячеек, в которых содержаться слово Стол и слово Шкаф. При этом, поиск будем осуществлять используя буквы находящиеся в этих словах. «Ст» и «ф» соответственно. К этим буквам необходимо добавить символ «*». В случае с «Ст», после, так как это первые буквы в слове. В случае с «ф», перед, так как это конец слова. Кавычки появятся автоматически.

Количество критериев не ограниченно двумя. Может быть больше, в зависимости от задачи.

Источник

Счетесли в excel примеры с двумя условиями

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

​Смотрите также​​ появляются на форуме​​ СУММЕСЛИМН.​ и просуммировала их​ условия).​ критерий передана ссылка​ вычислений нажмем комбинацию​

​ от проданной продукции​ 2-м критериям (Условие​) =5, которое удовлетворяет​

Синтаксис

​В качестве исходной таблицы​

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

​формула массива =СУММ((B2:B13<>=D2))​​ ячейку или текста,​ и Климов превысили​В условии можно использовать​Функция​

​ еженедельно и даже​​Прописываем аргументы.​ зарплаты.​Критерий – определяет, какие​ на пустую ячейку​ клавиш Ctrl+Shift+Enter:​ составляет техника фирмы​ И) можно без​ критерию >=5, поэтому​ возьмем таблицу с​ отдельной таблички с​

​Рассмотрим задачу, когда 1​​формула массива =СЧЁТ(ЕСЛИ((B2:B13>=D2)*(B2:B13​ которые определяют, какие​ квоту для периодов​ подстановочные знаки: вопросительный​СЧЁТЕСЛИМН​

​ чаще. Пользуйтесь поиском​​диапазон суммирования – ячейки​Аналогично можно подсчитать зарплаты​ ячейки из диапазона​ или пустая строка​Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив​​ Samsung.​​ применения формул с​ второе значение в​ двумя столбцами: текстовым​

Замечания

​ критериями.​ текстовый критерий с​=БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют​ ячейки требуется учитывать.​ Q1, Q2 и​ знак (?) и​применяет критерии к​ по форуму.​ с зарплатой;​ всех менеджеров, продавцов-кассиров​ будут выбраны (записывается​ «», результат вычисления​ дробных чисел 1/количество_вхождений,​Вид исходной таблицы данных:​ помощью стандартного Автофильтра.​

​ массиве =1 и​ «Фрукты» и числовым​О подсчете с множественными​ подстановочным знаком применяются​​ наличия отдельной таблички​​ Например, условие может​

​ Q3 (только в​ звездочку (*). Вопросительный​ ячейкам в нескольких​r_kot​диапазон условия 1 –​ и охранников. Когда​ в кавычках).​ для любого диапазона​ например, для пользователя​Для расчета используем формулу:​Установите автофильтр к столбцу​ т.д.​​ «Количество на складе»​​ критериями можно почитать​

Пример 1

​ к значениям текстового​ с критериями.​ быть выражено следующим​ Q2).​ знак соответствует любому одиночному​ диапазонах и вычисляет​: Поправил формулу. Вместо​ ячейки с указанием​ табличка небольшая, кажется,​Диапазон суммирования – фактические​ ячеек будет числовое​ с ником sam​=СЧЁТЕСЛИ(C3:C17;»Samsung»)/A17​

​ Количество ящиков на​

​Далее, функция попарно перемножает​

​Рассмотрим задачу, когда 2​

​ количество соответствий всем​

​ enter жми ctrl+shift+enter​

​ ячейки, которые необходимо​

​ это значение равно​

​ складе, выделив заголовок​

​ элементы массивов и​

​Рассмотрим задачу, когда критерии​

​В этом разделе перечислены​

​Данные​ любой последовательности символов.​ критериям.​ и наступит результат!​условия 1 – продавец;​

​ сосчитать и вручную,​

​ просуммировать, если они​

​Функция может быть использована​ 0,25 (4 вхождения).​C3:C17 – диапазон ячеек,​ столбца и нажав​ суммирует их. Получаем​

​ применяются к значениям​

​ распространенные причины ошибки​

​ столбца с числами (см. файл​ форме дат и​Диапазон_условия2, условие2. ​1​ Если нужно найти​Это видео — часть учебного​

Пример 2

​диапазон условия 2 –​

​ в качестве формулы​

​ содержащих названия фирм​

​ из разных столбцов.​

​ примера, лист 1текст (с​

​ применяются к значениям​

​ Необязательные аргументы. Дополнительные диапазоны​

​ сам вопросительный знак​

​ курса Усложненные функции​

​: Поправил формулу. Вместо​

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

​Выберите числовой фильтр Между.​

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

​ или звездочку, поставьте​

​ ЕСЛИ.​ enter жми ctrl+shift+enter​ пола сотрудника;​ которых по несколько​ всего 3 аргумента.​ выполнить расчет числа​

​ СУММ, соответствует количеству​

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

​»Samsung» – критерий поиска​Введите критерии​ функции СУММПРОИЗВ() является​ с определенным Фруктом​Если функция СЧЁТЕСЛИ или​

​Найдем число партий товара​Найдем число партий товара​ них. Разрешается использовать​02.05.2011​ перед ними знак​СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)​

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

​ и наступит результат!​​условие 2 – женский​​ сотен позиций, целесообразно​

​A17 – ячейка, хранящая​ же как в​ знак Умножения (*)​с Количеством на​ ячейку или диапазон​со слова Яблоки и​ склад не ранее 25.10.2012 и не​ диапазонов и условий.​

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

​ складе не менее​ в закрытой книге,​ с Количеством ящиков на​ позднее 24.12.2012 (строка таблицы соответствует​Каждый дополнительный диапазон должен​4​

​).​Диапазон_условия1.​: Или популярная на​ январе получили в​ команды СУММЕСЛИ в​ будет работать только​ будет рассмотрена в​ значение:​ соответствующий общему числу​

​ будет отобрано 7 строк​4. Формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))​ минимального (Условие И​

​ это приводит к​ складе не менее 10. ​

​ критерию, когда ее​ состоять из такого​04.05.2011​Скопируйте образец данных из​ Обязательный аргумент. Первый диапазон,​​ форуме без массива:​​ сумме 51100 рублей.​ конце добавляются еще​ по диапазону и​ одном из примеров.​

​Для определения количества просмотренных​ продаж.​

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

​ же количества строк​5​ следующих таблиц и​ в котором необходимо​=СУММПРОИЗВ(($C$4:$C$16=»да»)*($B$4:$B$16=$G4))​

​Функции СУММЕСЛИ и СУММЕСЛИМН​ две буквы –​ критерию.​Рассматриваемая функция может быть​ страниц пользователями default​Результат расчета:​ в нижней части​

​ =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)) Единственное, после​ котором строка считается​Примечание.​ 3, в исходной​ критериям одновременно). ​

​ и столбцов, что​05.05.2011​ вставьте их в​ проверить соответствие заданному​=СУММПРОИЗВ(($C$4:$C$16=»нет»)*($B$4:$B$16=$G4))​ хороши тем, что​

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

​ МН (СУММЕСЛИМН), значит,​​ использована для определения​ и user_1 запишем​Доля проданной продукции техники​ окна).​ ее ввода нужно​

​ удовлетворяющей критерию, когда​Это известная проблема​ таблице присутствуют фрукты​В качестве исходной таблицы​ и аргумент диапазон_условия1. Эти​6​ ячейку A1 нового​ условию.​Марина​

​ они автоматически подстраиваются​ подразумевается функция с​Рассмотрим простейший пример, который​

​ количества совпадений как​ формулу:​

​ фирмы Samsung в​Примечание​ вместо​ оба ее поля​ с рядом других​​ с более сложными​​ возьмем таблицу с​ диапазоны могут не​06.05.2011​ листа Excel. Чтобы​

​Условие1.​: Всем большое спасибо​

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

​: подсчет значений с​ENTER​ одновременно соответствуют критериям).​ функций Excel, в​ названиями: яблоки свежие,​ двумя столбцами: текстовым​ находиться рядом друг​Формула​ отобразить результаты формул,​

​ Обязательный аргумент. Условие в​​ :-)​​ Т.е. мы можем​ применяется в случае,​ использовать функцию СУММЕСЛИ​

​ и сразу по​Функция имеет следующую синтаксическую​ 40%.​ множественными критерями также​нажать​ Например, число партий​ том числе СУММЕСЛИ,​ персики сорт2. Чтобы​ «Фрукты» и «Дата​ с другом.​

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

​ и насколько удобной​ нескольким критериям поиска.​ запись:​​ рассмотрен в статьях​CTRL+SHIFT+ENTER​ персики (ячейка​

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

​ СУММЕСЛИМН и СЧИТАТЬПУСТОТЫ.​ одновременно подсчитать партии​ поступления» (см. файл примера, лист 2Даты).​В условиях можно использовать​Результат​ нажмите клавишу F2,​ ссылки на ячейку​: Темы с одноименным​

​ ячейках, и суммы​ не один критерий.​ она может оказаться​ В последнем случае​=СЧЕТЕСЛИ(диапазон; критерий)​Пример 2. По итогам​ Подсчет значений с​5. Формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет​D2​ Дополнительные сведения см.​

​ товара Яблоки и​Так как даты хранятся​ подстановочные знаки: вопросительный​

​=СЧЕТЕСЛИМН(A2:A7;»1″)​ а затем — клавишу​

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

​ множественными критериями (Часть​ еще один вариант​) с количеством ящиков​

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

​ в статье Функции​ Яблоки свежие нужно​ в EXCEL в​ знак (?) и​

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

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

​ задач.​​ более функции СЧЁТЕСЛИ,​диапазон – обязательный аргумент,​ составить таблицу, в​ 2. Условие ИЛИ),​ многокритериального подсчета значений.​ на складе >=5​ СУММЕСЛИ, СЧЁТЕСЛИ и​ использовать подстановочные знаки.​ числовом формате, то​ звездочку (*). Вопросительный​

​ 1 и 6​​ измените ширину столбцов,​ требуется учитывать. Например,​ еженедельно и даже​ при подсчете заработных​ но минимум –​

​Имеем таблицу, в которой​ возвращаемые результаты которых​ принимающий ссылку на​ которой содержатся данные​ Часть3, Часть4.​6. Формула =БСЧЁТА(A1:B13;A1;D14:E15) требует​ (ячейка​ СЧИТАТЬПУСТОТЫ возвращают ошибку​В качестве критерия в​

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

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

​ складывают или вычитают.​ одну либо несколько​

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

​ о количестве студентов,​Функция СЧЁТЕСЛИ в Excel​

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

​ предварительного создания таблицы​Е2​ #ЗНАЧ!​ ячейке​ не изменятся (см.​ символу; звездочка —​ и 6), содержащихся​

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

​ данные.​ выражено следующим образом:​ по форуму.​ мы забыли учесть​

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

Задача1

​ с условиями. Заголовки​). Результат очевиден: 2.​Решение:​

​D2​ задачу 1).​​ любой последовательности символов.​​ в ячейках A2–A7.​Продавец​ 32, «>32», B4,​Спасибо, обязательно воспользуюсь​ одну сотрудницу, которая​ СУММЕСЛИ он был​ зарплата, начисленная за​ A1:A10 хранится последовательность​ требуется определить число​ 5, 4, 3​​ количества ячеек в​​ этой таблицы должны​ Для наглядности, строки​Откройте связанную книгу,​​укажем Яблоки*, где​​Рассмотрим задачу, когда 1​ Если нужно найти​4​Превышена квота Q1​ «яблоки» или «32».​

​ :-)​ работает продавцом. Мы​

​ в конце, то​ январь-месяц. Если нам​ значений от 1​

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

​ текстовый критерий применяются​ сам вопросительный знак​

​ форматированием с правилом =И($A2=$D$2;$B2>=$E$2)​ F9, чтобы обновить​Хотя формула с функцией СЧЁТЕСЛИМН()​ столбца, а другой​ перед ними знак​

​ числа меньше 5​Ильина​ и условия для​ неправильность моей формулы​ правую кнопку мыши​​ Он также означает​​ которые требуется выдать​​ с числами больше​

​ которому выполняется расчет​Вид исходной таблицы:​ в качестве второго​

).​ в ячейках A2–A7​Да​ них. Разрешается использовать​

​ :-)​ и команду ВСТАВИТЬ.​ ячейки, которые необходимо​ работникам, мы используем​ 3 и менее​ количества совпадений в​Предварительно выделим ячейки E2:E5,​ аргумента данной функции,​ соответствует Условию И.​ формул, приведем несколько:​Также можно обойти эту​ предыдущей задачей не​

Задача2

​ столбца с числами (см. файл​Рассмотрим задачу, когда 2​ и даты раньше​

​Нет​ до 127 пар​Марина​У нас появилась дополнительная​ просуммировать.​ функцию СУММ, указав​ 8 необходимо выполнить​ рассматриваемом диапазоне. Условием​ введем приведенную ниже​

​ и возвращает соответствующее​Здесь есть один трюк:​1. =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2) Это решение является​ проблему, используя функции​ изменится, часть альтернативных​ примера, лист 1текст 1числовой).​

​ числовых критерия применяются​

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

​ 03.05.2011 в ячейках​Нет​ диапазонов и условий.​: Перепробовала уже кучу​ строчка. Сразу обращаем​

​Диапазон условия 1 –​ диапазоном все заработные​ следующие действия:​ могут являться логическое​​ формулу:​​ числовое значение.​

​ в качестве второго​

​ самым простым и​ СУММ и ЕСЛИ​ решений работать не​Найдем число партий товара​ к значениям одного​ B2–B7.​Егоров​

​Важно:​​ вариантов, но когда​ внимание, что диапазон​ ячейки, которые нужно​ платы.​записать первую функцию СЧЁТЕСЛИ​ выражение, числовое значение,​=СЧЁТЕСЛИ(B3:B19;D2:D5)​

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

​Функция СЧЁТЕСЛИ может быть​ аргумента функции БСЧЁТА()​ понятным.​ вместе в формуле​ будет (подробнее см.​ Яблоки с Количеством ящиков​ из столбцов (см.​2​Да​ Каждый дополнительный диапазон должен​

​ применяю к таблице​ условий и суммирования​ оценить на основании​Но как быть, если​ с критерием «>3»;​ текстовая строка, значение​Описание аргументов:​ использована для анализа​ (поле) нужно ввести​2. =СУММПРОИЗВ(—(A2:A13=D2);—(B2:B13>=E2)) Это решение сложнее,​ массива. Дополнительные сведения​ здесь).​ на складе не менее 10 (строка​ файл примера, лист​=СЧЕТЕСЛИМН(A2:A7; «​Да​ состоять из такого​ исправленную формулу или​ автоматически расширился до​ первого критерия.​ нам нужно быстро​записать вторую функцию с​ типа Дата, ссылка​B3:B19 – диапазон ячеек​

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

​ числовых значений, текстовых​ ссылку на заголовок​ но позволяет понять​ см. в статье​Рассмотрим задачу, когда 1​ таблицы соответствует критерию,​ 2числовых).​Такое же описание, что​Нет​

​ же количества строк​

​Условие 1 – определяет​

​Найдем число партий товара​ и для предыдущего​Шашков​ и столбцов, что​

​ ничего не получается,​

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

​Копируем данные сотрудника и​ ячейки, которые функция​ только продавцов? В​определить разницу между возвращаемыми​Примечания:​ экзамен;​ данных другого типа.​ значениями, т.к. БСЧЁТА()​ которая может быть​ и СЧИТАТЬПУСТОТЫ возвращают​

​ к значениям текстового​

​ Фрукт совпадает с​ с Количеством ящиков на​ примера, но вместо​

​D2:D5 – диапазон ячеек,​

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

​ С ее помощью​ подсчитывает текстовые значения.​ полезна для подсчета​ ошибку #ЗНАЧ!​ столбца, а 2​ критерием Яблоки, и​ складе не менее​ констант в условии​Да​диапазон_условия1​ :-(((​ общий перечень. Суммы​

​ 10 и не более​ используются ссылки на​Да​. Эти диапазоны могут​не могу понять​ в итоговых ячейках​Диапазон условия 2 –​Прописываем аргументы.​ множества (3;+∞) подмножество​ соответствии с двумя​ подсчета числа совпадений.​ неповторяющихся значений в​ записать другую формулу​

​ в других случаях. ​Сократите строку, если​ значениям столбца с​ ящиков на складе >=10). ​

​ 50 (строка таблицы соответствует​

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

​ не находиться рядом​

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

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

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

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

​ требуемому условию, счет​ Вам самой. Я​ удалять какие-либо строки​ диапазона условия.​ Поэтому проставляем E2:E14.​

​ увеличивается на 1.​ только все наличие​ (например, при увольнении​И так далее. В​Критерий выбора в нашем​ данной операции.​ столов и стульев.​ день, а также​

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

​ не менее минимального и​ затем нажав клавишу​=СЧЁТЕСЛИ(B2:B12;»длинная строка»&»еще одна длинная​ критерию, когда ее​ применяется первый критерий​Формулу запишем в следующем​ возьмем таблицу с​

​Определяет, насколько Ильина превысила​ Если все вторые​ тавара разложил по​ сотрудника), изменять значения​ зависимости от количества​ случае – продавец.​По названию команды можно​ Для вычислений используем​ сколько раз за​ определения количества клиентов-однофамильцев​ не более максимального​F9​

​ строка»)​ поле Фрукт совпадает с критерием Яблоки,​ (>=10 или «>=»&D2) это​ виде: =СЧЁТЕСЛИМН(B2:B13;»>=»&D2;B2:B13;»​ двумя столбцами: текстовым​ квоту продаж для​

​ ячейки соответствуют требуемому​

​ «февраль» и подставить​ может увеличиваться в​ кавычки и ставим​ не просто считает​Если в качестве критерия​ заходили пользователи с​

​ можно ввести функцию​ строка таблицы соответствует​Двойное отрицание (—) преобразует​ сообщества, посвященном Excel​ поле Количество ящиков на​B2:B13​ диапазон, к которому​ «Количество на складе»​ и 3 (только​ раз увеличивается на​ ваша проблема, а​

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

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

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

​ складе одновременно >=10 и​. Первый и второй​ применяется первый критерий​ (См. файл примера).​

​ регистр символов не​Вид исходной таблицы:​ количество любых символов​ критериям одновременно). ​Аналогично, второй массив возвращает​ версии Excel? Если​ таблице, удовлетворяющие критериям,​

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

​ случае совпадают, т.к.​ это диапазон ​диапазон_условия1; условие1; [диапазон_условия2; условие2]…​=СЧЕТЕСЛИМН(B2:B5,»=Да»,C2:C5,»=Да»)​ пор, пока не​С уважением, Александр.​

​ что нам нужно​Функция СУММЕСЛИ позволяет суммировать​

​ имеет значения. Например,​

​Марина​ ошибка, может вообще​ сумму заработных плат​

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

​ узнать сумму зарплат​ ячейки, которые удовлетворяют​ функция СЧЁТЕСЛИ(A1:A2;»Петров») вернет​ свой уникальный идентификатор​ подстроки «Иванов».​ задачи. Например, с​ соответствует значениям B2)​ темами на портале​Формулу запишем в следующем​Альтернативными решениями задачи являются​. Первый и второй​Диапазон_условия1​ свои квоты за​ ячейки.​: Александр, спасибо :-)​ неправильная формула:-): =СЧЁТ(ЕСЛИ($C$4:$C$16=»да»;ЕСЛИ($B$4:$B$16=$G4;0);0))​ за январь всех​ всех продавцов. Поэтому​

​ определенному критерию (заданному​ значение 2, если​ в базе данных​Пример 1. В таблице​ использованием функции СЧЁТЕСЛИМН() формула​ =3, которое меньше​

​ пользовательских предложений для​ виде: =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2;B2:B13;»​ следующие формулы:​ диапазон в данном​. Первый диапазон, в котором​ кварталы 1 и​Если аргумент условия является​Так и не​

​Задача: посчитать по​ продавцов-женщин. У нас​ F2:F14.​ условию). Аргументы команды​ в ячейках A1​ (Id), выполним расчет​ Excel содержатся данные​ выглядит так (см.​ 5 (не удовлетворяет​

СЧЕТЕСЛИ по двум критериям.

​ Excel.​​Альтернативными решениями задачи являются​=СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2))​ случае совпадают, т.к.​

​ необходимо проверить соответствие​ 2 (Егоров и​
​ ссылкой на пустую​

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

​ о продажах товаров​​ лист один столбец​ критерию), поэтому первое​Произведем подсчет строк, удовлетворяющих​

​ следующие формулы:​​формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))​ 2-й критерий (B2:B13.​ заданному условию1;​

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

​Диапазон – ячейки, которые​​ строки «петров» и​ за день по​

​ в магазине бытовой​​ в файле примера):​ значение в массиве <0:1:1:1:0:1:1:0:0:1:1:1>​ сразу двум критериям,​=СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13​формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13))​Альтернативными решениями задачи являются​

​Условие1​2​
​ интерпретируется функцией​ но когда заново​Guest​продавцом;​

​ должностей, выбрала из​​ следует оценить на​ «Петров» соответственно.​ следующей формуле массива​ техники за день.​=СЧЁТЕСЛИМН(B2:B13;»>=»&D2;B2:B13;»​ =0. Второе значение (ячейка​ которые образуют Условие​формула массива =СУММ((A2:A13=D2)*(B2:B13<>=E2))​

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

​. Условие в форме числа,​​=СЧЕТЕСЛИМН(B5:D5,»=Да»,B3:D3,»=Да»)​

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

Источник