счет если между двумя значениями
СЧЁТЕСЛИ (функция СЧЁТЕСЛИ)
С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).
Самая простая функция СЧЁТЕСЛИ означает следующее:
=СЧЁТЕСЛИ(где нужно искать;что нужно найти)
Группа ячеек, для которых нужно выполнить подсчет. Диапазон может содержать числа, массивы, именованный диапазон или ссылки на числа. Пустые и текстовые значения игнорируются.
Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.
Например, критерий может быть выражен как 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 с несколькими условиями — объясняем на примерах.
Начиная с версии 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. Подсчет дат с различными критериями на основе текущей даты
Вы можете использовать функцию СЕГОДНЯ() для подсчета дат по отношению к сегодняшнему дню.
Эта формула с двумя областями и двумя критериями ответит вам, сколько товаров уже куплено, но еще не доставлено.
Она допускает множество возможных вариаций. В частности, вы можете настроить ее, чтобы подсчитать, сколько заказов было оформлено более недели назад и пока еще не доставлено:
Вот такими способами можно сосчитать ячейки, удовлетворяющие различным условиям.
Я надеюсь, что вы найдете эти примеры и советы полезными. В любом случае, я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге ещё не раз.
Функция СЧЁТЕСЛИ() в MS Excel
Подсчет числовых значений с одним критерием
Данные будем брать из диапазона A15:A25 (см. файл примера ).
Критерий
Формула
Результат
Примечание
Подсчитывает количество ячеек, содержащих числа равных или более 10. Критерий указан в формуле
Подсчитывает количество ячеек, содержащих числа равных или меньших 10. Критерий указан через ссылку
>= (ячейка С4)11(ячейка С5)
Подсчитывает количество ячеек, содержащих числа равных или более 11. Критерий указан через ссылку и параметр
Подсчет дат с одним критерием
Если необходимо подсчитать количество дат, принадлежащих определенному месяцу, то нужно создать дополнительный столбец для вычисления месяца, затем записать формулу = СЧЁТЕСЛИ(B20:B30;2)
Подсчет с несколькими условиями
Синтаксис и особенности функции
Сначала рассмотрим аргументы функции:
В диапазоне ячеек могут находиться текстовые, числовые значения, даты, массивы, ссылки на числа. Пустые ячейки функция игнорирует.
В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.
Рекомендации для правильной работы функции:
Функция Счётесли
Счётесли (диапазон; критерий)
Диапазон – группа ячеек, для которых нужно выполнить подсчет.
Диапазон может содержать числа, массивы или ссылки на числа.
Обязательный аргумент.
Критерий – число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.
Обязательный аргумент.
Критерий проверки необходимо заключать в кавычки.
Критерий не чувствителен к регистру. К примеру, функция не увидит разницы между словами «налог» и «НАЛОГ».
Примеры использования функции Счётесли.
Вопросительный знак соответствует одному любому символу,
звездочка— любому количеству произвольных символов.
Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак
Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Т (без учета регистра), можно воспользоваться следующей формулой:
Если необходимо подсчитать количество ячеек, которые содержат ровно четыре символа, можно использовать формулу:
В функции Счётесли используется только один критерий.
Чтобы провести подсчет по нескольким условиям, необходимо воспользоваться функцией Счётеслимн.
Функция Счётеслимн
Счётеслимн (диапазон1; условие1; [диапазон2]; [условие2]; …).
Функция аналогична функции Счётеслимн, за исключением того, что может содержать до 127 диапазонов и условий, где первое является обязательным, а последующие – нет.
Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и диапазон условия 1. Эти диапазоны могут не находиться рядом друг с другом.
Пример использования:
Счётеслимн(C1:C8;”>15.01.2015″;C1:C8;” 15.01.2015” и “ 
Замечание. Критерий не чувствителен к регистру букв, поэтому можно вводить как прописные, так и строчные буквы.
Если ваши данные содержат несколько вариантов слов, которые вы хотите сосчитать, то вы можете использовать подстановочные знаки для подсчета всех ячеек, содержащих определенное слово, фразу или буквы, как часть их содержимого.
К примеру, в нашей таблице есть несколько заказчиков «Корона» из разных городов. Нам необходимо подсчитать общее количество заказов «Корона» независимо от города.
Мы подсчитали количество заказов, где в наименовании заказчика встречается «коро» в любом регистре. Звездочка (*) используется для поиска ячеек с любой последовательностью начальных и конечных символов, как показано в приведенном выше примере. Если вам нужно заменить какой-либо один символ, введите вместо него знак вопроса (?).
Кроме того, указывать условие прямо в формуле не совсем рационально, так как при необходимости подсчитать какие-то другие значения вам придется корректировать её. А это не слишком удобно.
Рекомендуется условие записывать в какую-либо ячейку и затем ссылаться на нее. Так мы сделали в H9. Также можно употребить подстановочные знаки со ссылками с помощью оператора конкатенации (&). Например, вместо того, чтобы указывать «* Коро *» непосредственно в формуле, вы можете записать его куда-нибудь, и использовать следующую конструкцию для подсчета ячеек, содержащих «Коро»:
Подсчет ячеек, начинающихся или заканчивающихся определенными символами
Вы можете употребить подстановочный знак звездочку (*) или знак вопроса (?) в зависимости от того, какого именно результата вы хотите достичь.
Если вы хотите узнать количество ячеек, которые начинаются или заканчиваются определенным текстом, независимо от того, сколько имеется других символов, используйте:
=СЧЁТЕСЛИ(A2:A22;”*р”) – считать заканчивающиеся буквой «р».
Если вы ищете количество ячеек, которые начинаются или заканчиваются определенными буквами и содержат точное количество символов, то поставьте вопросительный знак (?):
=СЧЁТЕСЛИ(С2:С22;”. д”) – находит количество буквой «д» в конце и текст в которых состоит из 5 букв, включая пробелы.
= СЧЁТЕСЛИ(С2:С22,”??”) – считает количество состоящих из 2 символов, включая пробелы.
Примечание. Чтобы узнать количество клеток, содержащих в тексте знак вопроса или звездочку, введите тильду (
?*”) будут подсчитаны все позиции, содержащие знак вопроса в диапазоне С2:С22.
Подсчет чисел по условию.
В отношении чисел редко случается, что нужно подсчитать количество их, равных какому-то определённому числу. Тем не менее, укажем, что записать нужно примерно следующее:
Гораздо чаще нужно высчитать количество значений, больших либо меньших определенной величины.
Чтобы подсчитать значения, которые больше, меньше или равны указанному вами числу, вы просто добавляете соответствующий критерий, как показано в таблице ниже.
критерии
Описание
Подсчитайте, где значение больше 5.
Подсчет со числами менее 5.
Определите, сколько раз значение равно 5.
Подсчитайте, сколько раз не равно 5.
Если больше или равно
Подсчет, когда больше или равно 5.
Если меньше или равно
Считаем количество крупных заказов на сумму более 10 000. Обратите внимание, что условие подсчета мы записываем здесь в виде текстовой строки и поэтому заключаем его в двойные кавычки.
Вы также можете использовать все вышеприведенные варианты для подсчета ячеек на основе значения другой ячейки. Вам просто нужно заменить число ссылкой.
Замечание. В случае использования ссылки, вы должны заключить математический оператор в кавычки и добавить амперсанд (&) перед ним. Например, чтобы подсчитать числа в диапазоне D2: D9, превышающие D3, используйте =СЧЕТЕСЛИ(D2:D9,”>”&D3)
Если вы хотите сосчитать записи, которые содержат математический оператор, как часть их содержимого, то есть символ «>», « 5*”) будет подсчитывать все позиции в диапазоне D2: D9 с таким содержимым, как «Доставка >5 дней» или «>5 единиц в наличии».
Примеры с датами.
Если вы хотите сосчитать клетки с датами, которые больше, меньше или равны указанной вами дате, вы можете воспользоваться уже знакомым способом, используя формулы, аналогичные тем, которые мы обсуждали чуть выше. Все вышеприведенное работает как для дат, так и для чисел.
Позвольте привести несколько примеров:
критерии
Описание
Даты, равные указанной дате.
Подсчитывает количество ячеек в диапазоне E2:E22 с датой 1 июня 2014 года.
Даты больше или равные другой дате.
Сосчитайте количество ячеек в диапазоне E2:E22 с датой, большей или равной 01.06.2014.
Даты, которые больше или равны дате в другой ячейке, минус X дней.
Определите количество ячеек в диапазоне E2:E22 с датой, большей или равной дате в H2, минус 7 дней.
Помимо этих стандартных способов, вы можете употребить функцию СЧЕТЕСЛИ в сочетании с функциями даты и времени, например, СЕГОДНЯ(), для подсчета ячеек на основе текущей даты.
критерии
Равные текущей дате.
До текущей даты, то есть меньше, чем сегодня.
После текущей даты, т.е. больше, чем сегодня.
Даты, которые должны наступить через неделю.
В определенном диапазоне времени.
Синтаксис и использование функции СЧЕТЕСЛИ в Excel
Функция СЧЕТЕСЛИ в Excel используется для подсчета ячеек в пределах заданного диапазона, которые соответствуют определенному критерию или условию.
Например, вы можете использовать функцию СЧЕТЕСЛИ, чтобы узнать, сколько ячеек на вашем листе содержит число больше или меньше указанного вами числа. Другое типичное использование функции СЧЕТЕСЛИ в Excel – подсчет ячеек с определенным словом или началом с конкретной буквы (букв).
Синтаксис функции СЧЕТЕСЛИ очень прост:
Как видите, есть только 2 аргумента функции СЧЕТЕСЛИ, оба из которых обязательны:
Вот простейший пример функции СЧЕТЕСЛИ в Excel. Формула =СЧЁТЕСЛИ(C2:C7;”Иванов Иван”) подсчитывает, сколько заявок поступало от Иванова Ивана:
Функция СЧЕТЕСЛИ в Excel для текста и чисел (точное совпадение)
Выше мы рассмотрели пример функции СЧЕТЕСЛИ, которая подсчитывает текстовые значения, соответствующие определенному критерию.
Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ со ссылкой на ячейку
Формула ЕСЛИ в Excel – примеры нескольких условий
Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.
Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.
Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2 
В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку
Дополнительная информация
Подсчет значений с множественными критериями (Часть 1. Условие И) в MS EXCEL
=SUMIF(B1:B5,”>9″) в файле примера): в других случаях. Для подсчета результата и просто установить курсорМастера функций
Считаем с учетом всех критериев (логика И).
Этот вариант является самым простым, поскольку функция СЧЕТЕСЛИМН предназначена для подсчета только тех ячеек, для которых все указанные параметры имеют значение ИСТИНА. Мы называем это логикой И, потому что логическая функция И работает таким же образом.
Для каждого диапазона – свой критерий.
Предположим, у вас есть список товаров, как показано на скриншоте ниже. Вы хотите узнать количество товаров, которые есть в наличии (у них значение в столбце B больше 0), но еще не были проданы (значение в столбце D равно 0).
Задача может быть выполнена таким образом:
Видим, что 2 товара (крыжовник и ежевика) находятся на складе, но не продаются.
Одинаковый критерий для всех диапазонов.
Если вы хотите посчитать элементы с одинаковыми критериями, вам все равно нужно указывать каждую пару диапазон/условие отдельно.
Например, вот правильный подход для подсчета элементов, которые имеют 0 как в столбце B, так и в столбце D:
Получаем 1, потому что только Слива имеет значение «0» в обоих столбцах.
Использование упрощенного варианта с одним ограничением выбора, например =СЧЁТЕСЛИМН(B2:D11;0), даст другой результат – общее количество ячеек в B2: D11, содержащих ноль (в данном примере это 5).