Excel счет если массив

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

Синтаксис и особенности функции

Сначала рассмотрим аргументы функции:

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

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

В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.

Рекомендации для правильной работы функции:

• Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
• Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
• Функция не учитывает регистр текстовых значений.
• При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
• Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.



Функция СЧЕТЕСЛИ в Excel: примеры

Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.

У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

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

Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.

Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:

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

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).

Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:

Формула посчитала «кровати» и «банкетки».

Используем в функции СЧЕТЕСЛИ условие поиска «не равно».

Формула: =СЧЁТЕСЛИ(A1:A11;»<>»&»стулья»). Оператор «<>» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

При применении ссылки формула будет выглядеть так:

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

1. Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».



2. Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.



3. Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).



4. Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.



5. Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

Посчитаем количество реализованных товаров по группам.

1. Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.



2. Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» — «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).

Скачать примеры функции СЧЕТЕСЛИ в Excel

Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.

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

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

Диапазон — это диапазон, в котором вы хотите подсчитать ячейки, содержащие числа, текст или даты.

Критерий — критерий в форме числа, выражения, ссылки на ячейку или текста, определяющий, какие ячейки следует подсчитывать. Например, критерий может быть выражен следующим образом: 32, «32», «> 32», «яблоки» или B4.

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

Данные будут взяты из диапазона A15: A25.

Подсчитывает количество ячеек, содержащих числа, равные или превышающие 10. Критерий указывается в формуле

Подсчитывает количество ячеек, содержащих числа, равные или меньшие 10. Критерий указывается по ссылке

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

Примечание. Чтобы подсчитать значения, соответствующие нескольким критериям, см. Подсчет значений с несколькими критериями. Чтобы подсчитать числа с более чем 15 значащими цифрами, прочитайте статью Подсчет значений ТЕКСТА с одним критерием в MS EXCEL.

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

Функция СЧЁТЕСЛИ () также подходит для подсчета текстовых значений.

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

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

Если вам нужно подсчитать количество дат, принадлежащих определенному месяцу, вам нужно создать дополнительный столбец для расчета месяца, а затем написать формулу = СЧЁТЕСЛИ (B20: B30; 2)

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

Обычно в качестве аргумента функции СЧЁТЕСЛИ () указывается только одно значение. Например, = СЧЁТЕСЛИ (H2: H11; I2). Если вы укажете ссылку на весь диапазон ячеек с критериями в качестве критерия, функция вернет массив. В файле примера формула = СЧЁТЕСЛИ (A16: A25; C16: C18) возвращает массив .

Чтобы вставить формулу, выберите диапазон ячеек того же размера, что и диапазон, содержащий критерии. В строке формул введите формулу и нажмите CTRL + SHIFT + ENTER, то есть введите ее как формулу массива.

Также прочтите: При включенной отладке jit любые необработанные исключения

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

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

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

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

Выражение COUNTIF (A6: A14; A6: A14) возвращает массив чисел , указывая, что значение 1 из списка в диапазоне A6: A15 — единственный, даже в диапазоне 4 значений, одно значение 3, три значения 4. Это позволяет подсчитывать количество неповторяющихся значений по формуле = СУММПРОИЗВ (- (СЧЁТЕСЛИ (LA6: LA14; LA6: LA14) = 1)) .

Формула = СЧЁТЕСЛИ (A6: A14; «вычисляет ранг в порядке убывания для каждого числа из диапазона A6: A15. Вы можете проверить это, выделив формулу в строке формул и нажав F9. Значения будут соответствовать вычисленному рангу в столбце B (с помощью функции RANK ()) Этот подход применяется в статьях «Динамическая сортировка таблицы в MS EXCEL» и «Выбор уникальных значений с сортировкой в ​​MS EXCEL.

Функция COUNT подсчитывает количество ячеек, содержащих числа, и количество чисел в списке аргументов. Функция COUNT используется для определения количества числовых ячеек в диапазонах и массивах чисел. Например, чтобы вычислить количество чисел в диапазоне A1: A20, вы можете ввести следующую формулу: = COUNT (A1: A20). Если в этом примере пять ячеек в диапазоне содержат числа, результатом будет 5.

Синтаксис

Аргументы функции COUNT перечислены ниже.

Значение1 Обязательное. Первый элемент, ссылка на ячейку или диапазон, для которого вы хотите подсчитать количество чисел.

Value2;… — необязательный аргумент. До 255 дополнительных элементов, ссылок на ячейки или диапазонов, в которых вы хотите подсчитать количество чисел.

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

Замечания

Подсчитываются аргументы, которые являются числами, датами или текстовыми представлениями чисел (например, число, заключенное в кавычки, например «1″).

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

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

Если аргумент является массивом или ссылкой, учитываются только числа. Пустые ячейки, логические значения, текст и значения ошибок в массиве или ссылке игнорируются.

Смотрите также: Смарт-устройство на телевизоре Samsung

Если вам нужно подсчитать логические значения, текстовые элементы или значения ошибок, используйте функцию COUNT.

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

Пример

Скопируйте образец данных из приведенной ниже таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы просмотреть результаты формул, выберите их и нажмите F2, затем нажмите Enter. При необходимости измените ширину столбцов, чтобы увидеть все данные.

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

Синтаксис и особенности функции

Давайте сначала рассмотрим аргументы функции:

• Диапазон — это группа значений, которые необходимо проанализировать и подсчитать (обязательно).
• Критерий: условие, при котором вы хотите подсчитать ячейки (обязательно).

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

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

Рекомендации по правильному функционированию функции:

• Если функция СЧЁТЕСЛИ ссылается на диапазон в другой книге, эта книга должна быть открыта.
• Аргумент критерия должен быть заключен в кавычки (кроме ссылок).
• Функция не чувствительна к регистру для текстовых значений.
• вы можете использовать подстановочные знаки при формулировании условия подсчета. «?» — любой персонаж. «*» — любая последовательность символов. Чтобы формула искала эти знаки напрямую, мы ставим перед ними тильду ().
• Чтобы формула работала правильно, ячейки с текстовыми значениями не должны содержать пробелов или непечатаемых символов.

Считаем числовые значения в диапазоне. Условие подсчета является критерием.

У нас есть такая таблица:

Считаем количество ячеек с числами больше 100. Формула: = СЧЁТЕСЛИ (B1: B11; «> 100»). Диапазон — B1: B11. Критерий подсчета: «> 100». Результат:

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

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

Формула: = СЧЁТЕСЛИ (A1: A11; «табуреты»). ИЛИ:

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

Читайте также: Как привязать боковые кнопки на мышке

Формула с подстановочным знаком: = СЧЁТЕСЛИ (A1: A11; «табуляция*»).

Чтобы вычислить количество значений, оканчивающихся на «e», содержащих любое количество символов: = СЧЁТЕСЛИ (A1: A11; «* e»). У нас есть:

В формуле подсчитывались «кровати» и «скамейки».

Мы используем поисковый запрос «не равно» в функции СЧЁТЕСЛИ».

Формула: = СЧЁТЕСЛИ (A1: A11; «» & «стулья»). Оператор означает не равно. Символ амперсанда (&) объединяет данный оператор и значение слова «стулья».

При применении ссылки формула будет выглядеть так:

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

1. Посчитаем, сколько ячеек содержит текст «столы» и «стулья». Формула: = СЧЁТЕСЛИ (A1: A11; «столы») + СЧЁТЕСЛИ (A1: A11; «стулья»). Несколько выражений COUNTIF используются для указания нескольких условий. К ним присоединяется оператор «+».
2. Условия — это ссылки на ячейки. Формула: = СЧЁТЕСЛИ (A1: A11; A1) + СЧЁТЕСЛИ (A1: A11; A2). Функция ищет текстовые «таблицы» в ячейке A1. Текст «стулья» основан на критерии в ячейке A2.
3. Мы подсчитываем количество ячеек в диапазоне B1: B11 со значением больше или равным 100 и меньше или равным 200. Формула: = СЧЁТЕСЛИ (B1: B11; «> = 100») — СЧЁТЕСЛИ (B1: B11 ; «> 200»).
4. Мы применяем разные диапазоны в формуле СЧЁТЕСЛИ. Это возможно, если интервалы смежные. Формула: = СЧЁТЕСЛИ (A1: B11; «> = 100») — СЧЁТЕСЛИ (A1: B11, «> 200»). Ищите значения на основе двух критериев в двух столбцах одновременно. Если диапазоны не являются смежными, используется функция СЧЁТЕСЛИ.
5. Если критерием является ссылка на диапазон ячеек с условиями, функция возвращает массив. Чтобы ввести формулу, вам нужно выбрать столько ячеек, сколько есть в диапазоне с критериями. После ввода аргументов одновременно нажмите комбинацию клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

СЧЁТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому опытному пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

Считаем количество проданных товаров по группам.

1. Сначала отсортируем таблицу так, чтобы одинаковые значения располагались рядом друг с другом.
2. Первым аргументом формулы «INTERMEDIATE.TOTAL» является «Номер функции». Это числа от 1 до 11, которые обозначают статистическую функцию для вычисления промежуточного результата. Подсчет количества ячеек производится под числом «2» (функция «СЧЁТ»).

Формула нашла количество значений для группы «Стулья». При большом количестве строк (более тысячи) такое сочетание функций может пригодиться.

Функция СЧЁТЕСЛИ

​Смотрите также​​iva2185​ диапазона условия.​ всего 3 аргумента.​ диапазон ячеек, в​ указывать до 127​ означает «не равно».​СРЗНАЧЕСЛИ​ поле Количество ящиков на​ критерию, когда ее​ операторов имеются и​«OK»​Для неопытного пользователя легче​ 2. Условие ИЛИ),​ выделив A2:A13=D2, а​ с любой буквой​ Также можно использовать​Примечание:​: Спасибо, именно то,​И так далее. В​ Но иногда последний​

​ отношении содержащихся данных​ критериев.​ Знак амперсанда (&)​(AVERAGEIF) и​ складе одновременно >=10 и​ поле Дата поступления удовлетворяет обоим​ другие формулы, занимающиеся​

​.​ всего производить подсчет​

• ​ Часть3, Часть4.​ затем нажав клавишу​

​ в конце.​

• ​ функцию СЧЁТЕСЛИМН.​

• ​ Мы стараемся как можно​

Синтаксис

​ что нужно!!!​

Примеры

​ содержат ошибочных символов.​Количество ячеек со значением​ актуальными справочными материалами​: Код =СУММПРОИЗВ((F3:L3=»я»)+(F3:L3=»8-12″))​ может увеличиваться в​ будет работать только​ в качестве второго​

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

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

​Рассмотрим задачу, когда 1​​ «Количество на складе»​ рассмотрим вариант, когда​.​ Excel​ =0. Второе значение (ячейка​ значение.​ и значение в​ и сообщить, помогла​ — по цвету​женщиной.​Имеем таблицу, в которой​ Например, из таблицы,​ стоимость которых не​ условиями.​ ячеек по определенному​О подсчете с множественными​

support.office.com

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

​ текстовый критерий применяются​ (См. файл примера).​ используются также и​Есть и другой вариант​

​Функция​B3​Попробуйте воспользоваться функцией ПЕЧСИМВ​ ячейке B4, в​ ли она вам,​ ячейки или с​

Задача1

​Значит, будем применять команду​ указаны фамилии сотрудников,​ содержащей поля «Наименование»,​

​ превышает 20000 рублей.​Посчитаем, сколько ячеек содержат​​ критерию. Работает с​​ критериями можно почитать​ к значениям текстового​СУММЕСЛИМН(​ значения, вписанные непосредственно​ запуска​СЧЁТ​) =5, которое удовлетворяет​ или функцией СЖПРОБЕЛЫ.​ результате чего получается​ с помощью кнопок​​ учетом дня недели?​​ СУММЕСЛИМН.​ их пол и​ «Стоимость», «Диагональ экрана»​​Вид исходной таблицы:​​ текст «столы» и​ числовыми и текстовыми​ в этом разделе.​ столбца, а другой​диапазон_условия1; условие1; [диапазон_условия2; условие2]…​

​ в поле аргумента.​Мастера функций​

​относится к большой​ критерию >=5, поэтому​Для удобства используйте именованные​

​ формула =СЧЁТЕСЛИ(B2:B5;»<>75″). Результат —​ внизу страницы. Для​Yurii_74​Прописываем аргументы.​ зарплата, начисленная за​ необходимо выбрать устройства,​Для расчета количества телевизоров​

​ «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»).​ значениями, датами.​

• ​СЧЁТ​ (числовой) — к значениям​)​Любым из описанных в​. Для этого после​ группе статистических операторов,​​ второе значение в​​ диапазоны.​
• ​ 3.​ удобства также приводим​: По цвету без​
• ​диапазон суммирования – ячейки​ январь-месяц. Если нам​ цена которых не​ компании LG, стоимость​ Для указания нескольких​Сначала рассмотрим аргументы функции:​СЧЁТЕСЛИ​ столбца с числами (см. файл​​Диапазон_условия1​​ первом способе вариантов​ выделения ячейки нужно​ в которую входит​ массиве =1 и​Функция СЧЁТЕСЛИ поддерживает именованные​
• ​=СЧЁТЕСЛИ(B2:B5;»>=32″)-СЧЁТЕСЛИ(B2:B5;»>85″)​ ссылку на оригинал​ использования макросов не​ с зарплатой;​

​ нужно просто посчитать​ превышает 1000 долларов,​ которых не превышает​ условий используется несколько​Диапазон – группа значений​

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

​Количество ячеек со значением,​ (на английском языке).​ получится. Но если​

​диапазон условия 1 –​ общее количество денег,​ производителем является фирма​ 20000 рублей используем​ выражений СЧЕТЕСЛИ. Они​ для анализа и​СУММ​Найдем число партий товара​ необходимо проверить соответствие​

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

Задача2

​ объединены между собой​ подсчета (обязательный).​СУММЕСЛИ​

​ Яблоки с Количеством ящиков​ заданному условию1;​СЧЁТ​. На ленте в​ ней по своим​ элементы массивов и​фрукты​ 32 и меньшим​ СЧЁТЕСЛИ можно подсчитать​

​ — то вполне​ должности сотрудника;​ работникам, мы используем​ составляет 5 дюймов.​Описание аргументов:​ оператором «+».​

​Критерий – условие, по​

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

​СУММЕСЛИМН​ на складе не менее 10 (строка​Условие1​. В поле​ блоке инструментов​

​ задачам функция​ суммирует их. Получаем​;»>=32″)-СЧЁТЕСЛИ(​ или равным 85,​​ количество ячеек, отвечающих​​ реально (с использованием​

​условия 1 – продавец;​

​ функцию СУММ, указав​ В качестве условий​A2:A11 –диапазон первого условия,​Условия – ссылки на​ которому нужно подсчитать​Самые часто используемые функции​ таблицы соответствует критерию,​

​. Условие в форме числа,​​«Значение1»​«Библиотека функций»​СЧЁТЗ​ – 2.​фрукты​ в ячейках В2–В5.​ определенному условию (например,​

excel2.ru

Применение функции СЧЕТ в Microsoft Excel

​ функции​​диапазон условия 2 –​​ диапазоном все заработные​ можно указать “Samsung*”​ ячейки которого хранят​ ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2).​ ячейки (обязательный).​ в Excel –​ когда ее поле​ выражения, ссылки на​указываем адрес диапазона​

​жмем на кнопку​. Но, в отличие​

Работа с оператором СЧЁТ

​3. Другим вариантом использования​​;»>85″). Именованный диапазон может​​ Результат — 3.​ число клиентов в​ДЕНЬНЕД​ ячейки с указанием​ платы.​ (подстановочный символ «*»​ текстовые данные с​​ Текст «столы» функция​​В диапазоне ячеек могут​ это функции, которые​ Фрукт совпадает с​ ячейку или текста,​ с данными, а​​«Вставить функцию»​​ от предмета нашего​ функции СУММПРОИЗВ() является​ располагаться на текущем​=СЧЁТЕСЛИ(A2:A5;»*»)​ списке из определенного​

​):​ пола сотрудника;​Но как быть, если​ замещает любое количество​ названием фирмы и​ ищет в ячейке​ находиться текстовые, числовые​​ подсчитывают и складывают.​​ критерием Яблоки, и​​ которые определяют, какие​​ в поле​​.​​ обсуждения, она учитывает​ формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)). Здесь,​ листе, другом листе​Количество ячеек, содержащих любой​ города).​например:​условие 2 – женский​ нам нужно быстро​ символов), “>1000” (цена​ величиной диагонали;​ А1. Текст «стулья»​ значения, даты, массивы,​ Подсчитывать и складывать​ когда другое поле Количество​ ячейки требуется учитывать.​«Значение2»​Существует ещё один вариант,​ ячейки, заполненные абсолютно​ знак Умножения (*)​ этой же книги​ текст, в ячейках​Самая простая функция СЧЁТЕСЛИ​=СУММПРОИЗВ(—(F3:L3=»я»)—(F3:L3=»8-12″);—(ДЕНЬНЕД(F$1:L$1;2)=6))​

​ (ж).​ посчитать заработные платы​ свыше 1000, выражение​»LG*» – условие поиска​ — на базе​​ ссылки на числа.​​ (суммировать) можно на​​ ящиков на складе >=10). ​​ Например, условие может​вписываем логическое выражение​ наверное, самый простой,​​ любыми данными. Оператор​​ эквивалентен Условию И.​ или листе другой​

​ А2–А5. Подстановочный знак​​ означает следующее:​​в 1ой строке​​Итог: все продавцы-женщины в​​ только продавцов? В​ должно быть указано​ с подстановочным знаком​​ критерия в ячейке​​ Пустые ячейки функция​​ основе одного или​​Для наглядности, строки в​ быть выражено следующим​«ИСТИНА»​ но вместе с​СЧЁТ​4. Формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))​

​ книги. Чтобы одна​​ «*» обозначает любое​=СЧЁТЕСЛИ(где нужно искать;что нужно​

​ должны стоять даты.​​ январе получили в​

Способ 1: Мастер функций

​ дело вступает использование​ в кавычках), 5​ «*» (любое количество​ А2.​​ игнорирует.​​ нескольких критериев.​​ таблице, удовлетворяющие критериям,​​ образом: 32, «>32»,​

1. ​. Жмем на кнопку​ тем требующий хорошей​, о котором мы​ эквивалентна вышеупомянутой формуле​ книга могла ссылаться​​ количество любых символов.​​ найти)​

   

   ​iva2185​ сумме 51100 рублей.​​ функции СУММЕСЛИ.​​ (точное числовое значение,​ символов после «LG»;​Посчитаем число ячеек в​​В качестве критерия может​​Для подсчета количества ячеек,​ выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)​​ B4 или «яблоки»;​​«OK»​​ памяти. Выделяем ячейку​​ поведем подробный разговор,​

   

   ​ =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2))  Единственное, после​ на другую, они​ Результат — 4.​Например:​: Спасибо огромное… А​Функции СУММЕСЛИ и СУММЕСЛИМН​Прописываем аргументы.​ кавычки необязательны);​​B2:B11 – диапазон второго​​ диапазоне В1:В11 со​

2. ​ быть ссылка, число,​ которые содержат числа,​​Формулу запишем в следующем​​Диапазон_условия2, условие2… ​, чтобы выполнить вычисление.​ на листе и​​ ведет подсчет только​​ ее ввода нужно​​ обе должны быть​​=СЧЁТЕСЛИ(A2:A5;»????ки»)​​=COUNTIF(A2:A5;»Лондон»)​​ можно уточнить, -​ хороши тем, что​​Диапазоном в данном случае​​[диапазон_условия2;условие2];… — пара последующих​

   

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

   

3. ​ Функция СЧЕТЕСЛИ работает​СЧЁТ​В формуле предполагается, что​ и условия для​ выделенной области. Как​ на клавиатуре​ в числовом формате.​ENTER​Примечание:​ которых содержит ровно​СЧЁТЕСЛИ(диапазон;критерий)​ — что означают​ под изменение условий.​ всех должностей сотрудников,​

   ​ смысл которых соответствует​»​ меньшим или равным​ только с одним​(COUNT).​ диапазон, к которому​ них. Разрешается использовать​ видим, программа подсчитала​Shift+F3​Какие же данные относятся​нажать​ С помощью функции СЧЁТЕСЛИ​ 7 знаков и заканчивается​Имя аргумента​ двойные минусы в​​ Т.е. мы можем​​ потому что нам​ аргументам диапазон_условия1 и​Результат вычислений:​ 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).​​ условием (по умолчанию).​​=СЧЁТ(A1:A5)​

   

4. ​ применяется первый критерий​ до 127 пар​ количество ячеек с​.​ к числовым? Сюда​

​CTRL+SHIFT+ENTER​​ нельзя подсчитать количество​

Способ 2: вычисление с применением дополнительного аргумента

​ буквами «ки», в​Описание​ формуле??​ изменить данные в​ нужно будет определить​ условие1 соответственно. Всего​Пример 2. В таблице​Применим в формуле СЧЕТЕСЛИ​ Но можно ее​

1. ​=COUNT(A1:A5)​ (>=10 или «>=»&D2) это​ диапазонов и условий.​ числовыми значениями и​​Во всех трёх случаях​​ однозначно относятся собственно​​5. Формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет​​ ячеек с определенным​ диапазоне A2–A5. Подставочный​диапазон​​Yurii_74​​ ячейках, и суммы​​ сумму заработных плат.​​ может быть указано​​ содержатся данные о​​ несколько диапазонов. Это​

   

2. ​ «заставить» проанализировать 2​Для подсчета ячеек по​ диапазон ​Каждый дополнительный диапазон должен​ в общую сумму​ запустится окно​ числа, а также​ еще один вариант​ фоном или цветом​ знак «?» обозначает​​    (обязательный)​​: Только сегодня разобрался​ будут изменяться вместе​ Поэтому проставляем E2:E14.​ до 127 диапазонов​ покупках в интернет​ возможно, если диапазоны​ критерия одновременно.​ одному критерию (например,​B2:B13​ состоять из такого​

Способ 3: ручное введение формулы

​ к ним добавила​​Мастера функций​​ формат даты и​ многокритериального подсчета значений.​ шрифта. Однако Excel​ отдельный символ. Результат —​Группа ячеек, для которых​ с этим .​ с ними. Например,​Критерий выбора в нашем​ и условий для​ магазине бытовой техники​

​ являются смежными. Формула:​

1. ​Рекомендации для правильной работы​ больше 9), используйте​​. Первый и второй​​ же количества строк​

   

2. ​ ещё одно значение,​. Для перехода к​ времени. Логические значения​6. Формула =БСЧЁТА(A1:B13;A1;D14:E15) требует​​ поддерживает пользовательские функции,​​ 2.​

​ нужно выполнить подсчет.​ Это простой перевод​ при подсчете заработных​ случае – продавец.​ отбора значений.​ за определенный период​ =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения​ функции:​ функцию​ диапазон в данном​​ и столбцов, что​​ которое мы записали​

​ окну аргументов в​ (​​ предварительного создания таблицы​​ в которых используются​Проблема​Диапазон​ логических значений ИСТИНА,​ плат оказалось, что​ Заключаем слово в​Примечания:​ времени. Определить соотношение​ по двум критериям​Если функция СЧЕТЕСЛИ ссылается​СЧЕТЕСЛИ​ случае совпадают, т.к.​ и аргумент диапазон_условия1. Эти​ словом​ категории​«ИСТИНА»​ с условиями. Заголовки​

​ операции VBA (Visual​

lumpics.ru

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

​Возможная причина​может содержать числа,​ ЛОЖЬ в 1​ мы забыли учесть​ кавычки и ставим​

​Во втором и последующем​ проданных продуктов фирм​ сразу в двух​ на диапазон в​(COUNTIF).​ 2-й критерий  (B2:B13.​

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

​ диапазоны могут не​​«ИСТИНА»​​«Статистические​

• ​,​​ этой таблицы должны​ Basic для приложений)​Для длинных строк возвращается​
• ​ массивы, именованный диапазон​​ или 0 (первый​ одну сотрудницу, которая​ вторым аргументом.​ диапазонах условий ([диапазон_условия2],​ LG, Samsung и​ столбцах. Если диапазоны​ другой книге, то​=СЧЁТЕСЛИ(A1:A5;»>9″)​Альтернативными решениями задачи являются​
• ​ находиться рядом друг​​в поле аргументов.​» или​«ЛОЖЬ»​ в точности совпадать​ над ячейками, выполняемые​

​ неправильное значение.​ или ссылки на​ минус переводит ИСТИНУ​ работает продавцом. Мы​Диапазон суммирования – это​ [диапазон_условия3] и т.​ Bosch продавцом с​ несмежные, то применяется​

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

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

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

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

​ текстовые значения игнорируются.​ — минус единицу​ одну строчку через​

​ что нам нужно​ должно соответствовать их​

​ общему количеству реализованного​Когда в качестве критерия​Аргумент «Критерий» нужно заключать​ на нескольких критериях​формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))​​ подстановочные знаки: вопросительный​​ непосредственно в ячейку,​«СЧЁТ»​учитывает только тогда,​ в одной строке​

​ шрифта. Вот пример​ используется для сопоставления​

• ​Узнайте, как выбирать диапазоны​
• ​ в единицу). Кстати,​
• ​ правую кнопку мыши​
• ​ узнать сумму зарплат​ количеству в диапазоне,​ товара всеми продавцами.​

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

​ указывается ссылка на​ в кавычки (кроме​ (например, содержащие «green»​формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13))​ знак (?) и​

​ а в поле​. Выделяем его и​ когда они являются​ соответствует Условию И.​ подсчета количества ячеек​ строк длиннее 255 символов.​ на листе.​

​ в этом случае​ и команду ВСТАВИТЬ.​ всех продавцов. Поэтому​ заданном аргументом диапазон_условия1.​Вид исходной таблицы:​

​ диапазон ячеек с​ ссылок).​ и больше 9),​=БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют наличия​ звездочку (*). Вопросительный​ лишь стояла бы​

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

​ кликаем по кнопке​ именно её непосредственным​Здесь есть один трюк:​ определенного цвета с​Для работы с такими​критерий​ двойной минус можно​

​У нас появилась дополнительная​ F2:F14.​ В противном случае​Для получения искомого значения​ условиями, функция возвращает​Функция не учитывает регистр​ применяйте функцию​ отдельной таблички с​ знак соответствует любому одиночному​

​ ссылка на него,​«OK»​ аргументом. Если же​

​ в качестве второго​ использованием VBA.​

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

​ критериями.​ символу; звездочка —​

• ​ то к общей​
• ​.​
• ​ они просто находятся​
• ​ аргумента функции БСЧЁТА()​Произведем подсчет строк, удовлетворяющих​ СЦЕПИТЬ или оператор​

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

​Число, выражение, ссылка на​ всё равно в​ внимание, что диапазон​ автоматически проработала список​ код ошибки #ЗНАЧ!.​Для поиска сразу нескольких​ формулы нужно выделить​При формулировании условия подсчета​(COUNTIFS).​

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

​ (поле) нужно ввести​ сразу двум критериям,​ сцепления &. Пример:​ ячейку или текстовая​ итоге — *​ условий и суммирования​ должностей, выбрала из​Рассматриваемая функция выполняет проверку​ значений в векторе​ такое количество ячеек,​

​ можно использовать подстановочные​=СЧЁТЕСЛИМН(A1:A5;»green»;B1:B5;»>9″)​​ текстовый критерий с​​ Если нужно найти​ не прибавилась.​ запустить другим способом.​

​ на которую ссылается​ ссылку на заголовок​ которые образуют Условие​ =СЧЁТЕСЛИ(A2:A5;»длинная строка»&»еще одна​ строка, которая определяет,​ — = +.​ автоматически расширился до​

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

​ них только продавцов​ всех условий, перечисленных​ данных (столбце B:B)​ как в диапазоне​ знаки. «?» -​=COUNTIFS(A1:A5,»green»,B1:B5,»>9″)​ подстановочным знаком применяются​ сам вопросительный знак​

​Кроме использования​ Выделяем ячейку для​ аргумент, то в​ столбца с текстовыми​ И.​ длинная строка»).​ какие ячейки нужно​iva2185​ 15 строки.​ и просуммировала их​

​ в качестве аргументов​ в качестве аргумента​ с критериями. После​

​ любой символ. «*»​Для суммирования диапазона ячеек​

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

• ​Мастера функций​
• ​ вывода результата и​
• ​ таком случае оператор​
• ​ значениями, т.к. БСЧЁТА()​В качестве исходной таблицы​Функция должна вернуть значение,​

​ подсчитать.​: Может быть, у​Копируем данные сотрудника и​

excel2.ru

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

• ​ зарплаты.​
• ​ условие1, [условие2] и​
• ​ условие1 была передана​
• ​ введения аргументов нажать​
• ​ — любая последовательность​
• ​ используйте функцию​

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

СЧЁТ

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

​ константа массива {"LG";"Samsung";"Bosch"},​
​ одновременно сочетание клавиш​

СЧЕТЕСЛИ

​ символов. Чтобы формула​СУММ​ (числовой) — к значениям​ тильды (~).​​ пользователь может ввести​​«Формулы»​

​ не берет. Аналогичная​
​ В случае использования БСЧЁТ() нужно​

СЧЁТЕСЛИМН

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

​ поэтому формулу необходимо​
​ Shift + Ctrl​

СУММ

​ искала непосредственно эти​(SUM).​​ столбца с числами (см. файл​​Рассмотрим задачу, когда 2​

​ выражение самостоятельно вручную​
​. На ленте в​

СУММЕСЛИ

​ ситуация с текстовым​ записать другую формулу​ «Фрукты» и числовым​Аргумент​​ «>32», В4, «яблоки»​​ Почитаю с удовольствием…​ в итоговых ячейках​ и охранников. Когда​ все условия выполняются,​ выполнить в качестве​ + Enter. Excel​ знаки, ставим перед​

​=СУММ(A1:A5)​
​ примера, лист 1текст (с​

​ числовых критерия применяются​ в любую ячейку​ группе настроек​ представлением чисел, то​​ =БСЧЁТ(A1:B13;B1;D14:E15). Табличка с​​ «Количество на складе»​критерий​ или «32».​Serge 007​ изменились. Функции среагировали​ табличка небольшая, кажется,​ общая сумма, возвращаемая​ формулы массива. Функция​ распознает формулу массива.​

​ ними знак тильды​
​=SUM(A1:A5)​

СУММЕСЛИМН

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

​: См. пост​
​ на появление в​

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

​ записаны в кавычки​Рассмотрим задачу, когда критерии​
​Рассмотрим задачу, когда критерии​
​ в кавычки.​

​ только один критерий.​

office-guru.ru

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

​№8​ диапазоне еще одного​ сосчитать и вручную,​ единицу.​ элементов, содержащихся в​ в Excel очень​Для нормального функционирования формулы​

Синтаксис и особенности функции

​ на основе одного​

• ​начинающихся​ файл примера, лист​ Но для этого​
• ​«Другие функции»​ или окружены другими​ применяются к значениям​

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

​Если в качестве аргумента​ массиве значений, возвращаемых​ часто используется для​ в ячейках с​ критерия (например, больше​со слова Яблоки и​ 2числовых).​ нужно знать синтаксис​. Из появившегося списка​

​ знаками. Тут тоже,​ из одного столбца.​

• ​ из разных столбцов.​ на другую книгу,​ по нескольким условиям,​ как посчитать кол-во​Аналогично можно не только​
• ​ со списками, в​ условиеN была передана​ функцией СЧЁТЕСЛИМН. Функция​
• ​ автоматизированной и эффективной​ текстовыми значениями не​
• ​ 9), используйте функцию​ с Количеством ящиков на​Найдем число партий товара​ данного оператора. Он​ наводим курсор на​ если они являются​Найдем число партий товара​Найдем число партий товара​ появляется ошибка #ЗНАЧ!.​ воспользуйтесь функцией СЧЁТЕСЛИМН.​
• ​ дат рождения одного​ добавлять, но и​ которых по несколько​ ссылка на пустую​ СЧЁТ возвращает число​

​ работы с данными.​

Функция СЧЕТЕСЛИ в Excel: примеры

​ должно пробелов или​СУММЕСЛИ​ складе не менее 10. ​ с Количеством ящиков на​

​ не сложен:​ позицию​

​ непосредственным аргументом, то​ с Количеством на складе​ с определенным Фруктом​Эта ошибка возникает при​Чтобы использовать эти примеры​ года?​

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

​ Поэтому продвинутому пользователю​ непечатаемых знаков.​(SUMIF). В данном​В отличие от задачи​

​ складе не менее​

​=СУММ(Значение1;Значение2;…)​«Статистические»​ принимают участие в​

​ не менее минимального и​И​

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

​ пустого значения к​ диапазоне A2:A21, то​

​ настоятельно рекомендуется внимательно​​ случае для проверки​

​ 3, в исходной​ 10 и не более​Вводим в ячейку выражение​. В открывшемся меню​ подсчете, а если​

​ не более максимального​с Количеством на​

​ в формуле содержится​ данные из приведенной​: Спасибо. Я стал​ сотрудника), изменять значения​Если к стандартной записи​ числовому 0 (нуль).​ есть число строк​ изучить все приведенные​Посчитаем числовые значения в​ условия и суммирования​

1. ​ таблице присутствуют фрукты​ 50 (строка таблицы соответствует​ формулы​ выбираем пункт​ просто на листе,​ (Условие И -​ складе не менее​ функция, которая ссылается​
2. ​ ниже таблицы и​ немного богаче​ (заменить «январь» на​ команды СУММЕСЛИ в​При использовании текстовых условий​ в таблице. Соотношение​ выше примеры.​ одном диапазоне. Условие​
3. ​ используется один столбец,​ с более сложными​ критерию, когда ее​СЧЁТ​«СЧЁТ»​ то не принимают.​
4. ​ строка таблицы соответствует​ минимального (Условие И​ на ячейки или​ вставьте их на​iva2185​ «февраль» и подставить​ конце добавляются еще​ можно устанавливать неточные​ полученных величин является​Посчитаем количество реализованных товаров​
5. ​ подсчета – один​ поэтому в функции​ названиями: яблоки свежие,​ поле Количество ящиков​согласно её синтаксиса.​.​А вот применительно к​ критерию, когда ее​ — условие при​ диапазон в закрытой​ новый лист в​: Начал разбираться -​ новые заработные платы)​ две буквы –​

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

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

​ чистому тексту, в​ поле удовлетворяет обоим​

1. ​ котором строка считается​ книге. Для работы​ ячейку A1.​
2. ​ и поплыл​ и т.п.​ МН (СУММЕСЛИМН), значит,​ подстановочных символов «*»​В результате вычислений получим:​Сначала отсортируем таблицу так,​У нас есть такая​ два аргумента:​ одновременно подсчитать партии​ критериям одновременно). ​

​ вывода его на​ аргументом данной формулы​

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

exceltable.com

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

​iva2185​ подразумевается функция с​ и «?».​Пример 3. В таблице​ чтобы одинаковые значения​ таблица:​=СУММЕСЛИ(B1:B5;»>9″)​ товара Яблоки и​Для наглядности, строки в​ экран жмем на​ может быть значение,​ цифры, или к​Решение стоится аналогично предыдущей​ оба ее поля​

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

​ чтобы другая книга​Данные​ широкий табель. Видно,​: В прилагаемом примере​ несколькими условиями. Она​Суммировать в программе Excel​ приведены данные о​ оказались рядом.​Посчитаем количество ячеек с​=SUMIF(B1:B5,»>9″)​

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

​ Яблоки свежие нужно​ таблице, удовлетворяющие критериям,​ кнопку​ представленное в виде​ ошибочным выражениям (​

​ задачи. Например, с​

​ одновременно соответствуют критериям).​ была открыта.​яблоки​ что рабочих суббот​ табеля у слесаря​

​ применяется в случае,​

• ​ умеет, наверное, каждый.​ количестве отработанных часов​Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ»​ числами больше 100.​Чтобы суммировать значения ячеек​
• ​ использовать подстановочные знаки.​ выделяются Условным форматированием с правилом =И($B2>=$D$2;$B2​Enter​ ссылки или просто​
• ​«#ДЕЛ/0!»​ использованием функции СЧЁТЕСЛИМН() формула​ Например, число партий​
• ​Действие​

​32​

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

​ у слесаря Иванова​ и мастера учет​ когда нужно задать​ Но с усовершенствованной​ сотрудником на протяжении​ — «Номер функции».​ Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон​ на основе одного​В качестве критерия в​Формулу запишем в следующем​, размещенную на клавиатуре.​ записанное в соответствующее​

​,​

​ выглядит так (см.​ персики (ячейка​

​Результат​апельсины​ — две. Формула,​ рабочих дней ведется​ не один критерий.​ версией команды СУММ,​ некоторого периода. Определить,​ Это числа от​ – В1:В11. Критерий​ критерия (например, «green»),​ ячейке​ виде: =СЧЁТЕСЛИМН(B2:B13;»>=»&D2;B2:B13;»​Как видим, после этих​ поле. Правда, начиная​#ЗНАЧ!​ лист один столбец​D2​Помните о том, что​54​ хотя и не​

​ по-разному: у слесаря​

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

​Аргументов у СУММЕСЛИМН может​ которая называется СУММЕСЛИ,​ сколько раз сотрудник​ 1 до 11,​ подсчета – «>100».​ также используйте функцию​D2​В формуле предполагается, что​ действий итог вычислений​ с версии Excel​

​И т.д.) ситуация​

​ в файле примера):​) с количеством ящиков​

​ функция СЧЁТЕСЛИ не​

​персики​ сбоит, — упорно​ посменно — смены​ быть сколько угодно,​ существенно расширяются возможности​ работал сверх нормы​ указывающие статистическую функцию​ Результат:​СУММЕСЛИ​укажем Яблоки*, где​

​ диапазон, к которому​ выводится на экран​

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

​ 2007, таких значений​ другая. Такие значения​

​=СЧЁТЕСЛИМН(B2:B13;»>=»&D2;B2:B13;»​

​ на складе >=5​

• ​ учитывает регистр символов​75​ выдает 1. Может​ обозначаются «я», и​ но минимум –​ данной операции.​ (более 8 часов)​ для расчета промежуточного​
• ​Если условие подсчета внести​(SUMIF). В данном​ знак * заменяет​ применяется первый критерий​ в выбранной ячейке.​ может быть до​ функция​Подсчитать количество строк, удовлетворяющим​ (ячейка​ в текстовых строках.​яблоки​ быть, оттого, что​ формула имеет вид​ это 5.​По названию команды можно​ в период с​ результата. Подсчет количества​ в отдельную ячейку,​ случае для проверки​ последовательность любых символов идущих правее.​ (>=10 или «>=»&D2)​ Для опытных пользователей​ 255 включительно. В​СЧЁТ​ 2-м критериям (Условие​Е2​Критерий​86​
• ​ 1 число -​ =(СЧЁТЕСЛИ($F3:$L3;»=Я»)), а у​Диапазон суммирования. Если в​ понять, что она​ 03.08.2018 по 14.08.2018.​ ячеек осуществляется под​ можно в качестве​ условия и суммирования​Хотя формула с функцией СЧЁТЕСЛИМН()​

​ это диапазон ​

1. ​ данный способ может​ более ранних версиях​не учитывает в​ И) можно без​). Результат очевиден: 2.​не чувствителен к​Формула​ суббота? Поменял второй​ мастера — пятидневка​ СУММЕСЛИ он был​
2. ​ не просто считает​Вид таблицы данных:​ цифрой «2» (функция​ критерия использовать ссылку:​ используются разные столбцы,​  по сравнению с​B2:B13​ быть даже более​ их было всего​ любом виде.​
3. ​ применения формул с​ Для наглядности, строки​ регистру. Например, строкам​Описание​ параметр в функции​ и время работы​
4. ​ в конце, то​ сумму, но еще​Для вычислений используем следующую​ «СЧЕТ»).​Посчитаем текстовые значения в​

exceltable.com

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

​ поэтому в функции​ предыдущей задачей не​. Первый и второй​ удобный и быстрый.​ 30.​Кроме функций​ помощью стандартного Автофильтра.​

​ в таблице, удовлетворяющие​ «яблоки» и «ЯБЛОКИ»​=СЧЁТЕСЛИ(A2:A5;»яблоки»)​ ДЕНЬНЕД сначала на​ — 8 часов​ здесь он стоит​

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

​ и подчиняется каким-либо​ формулу:​Скачать примеры функции СЧЕТЕСЛИ​ одном диапазоне. Условие​ нужно заполнить три​

1. ​ изменится, часть альтернативных​ диапазон в данном​ Чем предыдущие с​Данные занести в поля​
2. ​СЧЁТ​Установите автофильтр к столбцу​ критериям, выделяются Условным​ будут соответствовать одни​
3. ​Количество ячеек, содержащих текст​ 1, потом, на​ 12 минут, и​ на первом месте.​

​ логическим условиям.​02.08.2018″;A2:A19;»8″)’ class=’formula’>​ в Excel​ поиска – один​ аргумента, последний –​ решений работать не​ случае совпадают, т.к.​ вызовом​

​ можно, набрав с​

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

​и​ Количество ящиков на​ форматированием с правилом =И($A2=$D$2;$B2>=$E$2)​ и те же​ «яблоки» в ячейках​ 3. В любом​ формула работает в​

​ Он также означает​Функция СУММЕСЛИ позволяет суммировать​В качестве первых двух​Формула нашла количество значений​ критерий.​ это диапазон для​ будет (подробнее см.​ 2-й критерий  (B2:B13.​Мастера функций​ клавиатуры конкретные значения​СЧЁТЗ​ складе, выделив заголовок​

​Подсчет можно реализовать множеством​ ячейки.​ А2–А5. Результат — 2.​ случае — И​ виде =(СЧЁТЕСЛИ($F3:$L3;»=8-12″)).​ ячейки, которые необходимо​

​ ячейки, которые удовлетворяют​

1. ​ условий проверки указаны​ для группы «Стулья».​Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:​ суммирования.​ здесь).​Альтернативными решениями задачи являются​и окна аргументов.​
2. ​ или координаты ячеек.​, подсчетом количества заполненных​ столбца и нажав​ формул, приведем несколько:​Использование подстановочных знаков​
3. ​=СЧЁТЕСЛИ(A2:A5;A4)​ каким образом эти​В ячейках M3​ просуммировать.​ определенному критерию (заданному​ даты, которые автоматически​

​ При большом числе​Во втором случае в​=СУММЕСЛИ(A1:A5;»green»;B1:B5)​Рассмотрим задачу, когда 1​ следующие формулы:​Существует несколько способов применения​

​ Но при наборе​ ячеек занимается ещё​CTRL+SHIFT+L. ​1. =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2) Это решение является​В​Количество ячеек, содержащих текст​ вычисления привязаны к​ и M4 надо​Диапазон условия 1 –​ условию). Аргументы команды​ преобразовываются в код​

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

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

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

​критерии​ «персики» (значение ячейки​ месяцу октябрю?​ проставить комбинированную формулу​

1. ​ ячейки, которые нужно​ следующие:​ времени Excel (числовое​ подобное сочетание функций​ ссылку на ячейку.​Для суммирования значений ячеек​ к значениям текстового​формула массива =СУММ((B2:B13<>=D2))​
2. ​СЧЁТ​ просто установить курсор​СЧЁТЕСЛИ​Введите критерии​
3. ​ понятным.​можно использовать подстановочные​ A4) в ячейках​Добавлено через пять​
4. ​ с использованием функции​ оценить на основании​Диапазон – ячейки, которые​ значение), а затем​
5. ​ может оказаться полезным.​Формула с применением знака​ на основе нескольких​ столбца, а 2​

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

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

​ А2–А5. Результат — 1.​ минут……​ СЧЕТЕСЛИ, пригодную для​ первого критерия.​ следует оценить на​ выполняется операция проверки.​

• ​Функция СЧЁТЕСЛИМН предназначена для​
• ​ подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).​

​ критериев (например, «blue»​ других (числовых) — к​

​=БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют​

• ​ является подсчет ячеек,​ выделить соответствующую ячейку​
• ​СЧЁТЕСЛИМН​ же как в​ но позволяет понять​
• ​ (?) и звездочку​
• ​=СЧЁТЕСЛИ(A2:A5;A2)+СЧЁТЕСЛИ(A2:A5;A3)​Правда, формула правильно​ обеих строк.​
• ​Условие 1 – определяет​ основании критерия (заданного​

​ Последний (третий) критерий​ подсчета числа ячеек​Для расчета количества значений,​

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

​ и «green»), используйте​ значениям столбца с​ наличия отдельной таблички​ содержащих числовые данные.​ или диапазон на​. С помощью данных​ задаче2 — т.е.​ работу функции СУММПРОИЗВ(),​ (*). Вопросительный знак​Количество ячеек, содержащих текст​ работает, если​Может быть, есть​ ячейки, которые функция​ условия).​ – количество рабочих​ из диапазона, удовлетворяющих​ оканчивающихся на «и»,​ функцию​

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

​ которая может быть​ соответствует одному любому​ «яблоки» (значение ячейки​=СУММПРОИЗВ(—(F3:L3=»я»)—(F3:L3=»8-12″);—(ДЕНЬНЕД(F$1:L$1;2)=7)), т.е. считать​ какие-то другие варианты​ выделит из первого​Критерий – определяет, какие​ часов больше 8.​

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

exceltable.com

СЧЕТЕСЛИ с несколькими условиями

​ полезна для подсчета​​ символу, а звездочка —​ A2) и «апельсины»​ серые рабочие дни​ решения?​ диапазона условия.​ ячейки из диапазона​В результате расчетов получим​ нескольким критериям, и​ любое число знаков:​(SUMIFS). Первый аргумент​Найдем число партий товара​ критерия заданы в​ вносить дополнительные данные​ второго из них​ дополнительных условий. Этой​
​ в нижней части​ с множественными критериями​ любой последовательности знаков.​ (значение ячейки A3)​ — воскресеньями….​Помогите с формулой,​
​Диапазон условия 2 –​ будут выбраны (записывается​ следующее значение:​
​ возвращает соответствующее числовое​ =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:​

​ – это диапазон​​ Яблоки с Количеством ящиков​ форме дат и​​ для расчета непосредственно​​ можно занести в​

​ группе статистических операторов​​ окна).​ в других случаях. ​

​ Если требуется найти​​ в ячейках А2–А5.​

​Yurii_74​​ пожалуйста…​ ячейки, которые следует​ в кавычках).​Функция имеет следующую синтаксическую​ значение. В отличие​Формула посчитала «кровати» и​ для суммирования.​ на складе не менее 10​ применяются к значениям​ в поле аргументов​ поле​ посвящена отдельная тема.​

​Примечание​​Разберем подробнее применение функции​ непосредственно вопросительный знак​ Результат — 3. В​: Проставьте в первой​Yurii_74​ оценить на основании​Диапазон суммирования – фактические​​ запись:​​ от функции СЧЁТЕСЛИ,​
​ «банкетки».​
​=СУММЕСЛИМН(C1:C5;A1:A5;»blue»;B1:B5;»green»)​
​ и не более​ одного из столбцов.​

​ формулы или записывая​​«Значение2»​Урок:​: подсчет значений с​ СУММПРОИЗВ():​ (или звездочку), необходимо​ этой формуле для​

​ строке именно даты​​: Можно просто суммировать​ второго критерия.​ ячейки, которые необходимо​=СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)​ которая принимает только​Используем в функции СЧЕТЕСЛИ​=SUMIFS(C1:C5,A1:A5,»blue»,B1:B5,»green»)​ 90 (строка таблицы соответствует​Найдем число партий товара​ их прямо в​и т.д. После​Как посчитать количество заполненных​ множественными критерями также​Результатом вычисления A2:A13=D2 является​ поставить перед ним​ указания нескольких критериев,​

​ (т. е. 01.09.2011;​​ их​Условие 2 – определяет​ просуммировать, если они​Описание аргументов:​

​ один аргумент с​​ условие поиска «не​​Примечание:​​ критерию, когда ее​ с Датой поступления на​ ячейку согласно синтаксиса​ того, как значения​

​ ячеек в Экселе​​ рассмотрен в статьях​ массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение​

​ знак тильды (~).​​ по одному критерию​ 02.09.2011, далее можно​
​=СЧЁТЕСЛИ($F3:$L3;»=Я») + СЧЁТЕСЛИ($F3:$L3;»=8-12″)​ ячейки, которые функция​ удовлетворяют критерию.​диапазон_условия1 – обязательный аргумент,​ критерием отбора данных,​ равно».​Аналогичным образом можно​ поле Фрукт совпадает с критерием Яблоки,​ склад не ранее 25.10.2012 и не​ данного оператора. Кроме​ занесены, жмем на​Урок:​ Подсчет значений с​ ИСТИНА соответствует персики.​Например, =СЧЁТЕСЛИ(A2:A5;»яблок?») возвращает все​ на выражение, функция​ растянуть).​.​ выделит из второго​
​Получается, что у функции​ принимающий ссылку на​
​ рассматриваемая функция позволяет​Формула: =СЧЁТЕСЛИ(A1:A11;»<>»&»стулья»). Оператор «<>»​
​ использовать функцию​ и когда другое​ позднее 24.12.2012 (строка таблицы соответствует​

​ того, среди статистических​​ кнопку​Статистические функции в Excel​ множественными критериями (Часть​ Результат можно увидеть,​ вхождения слова «яблок»​

CyberForum.ru

​ СЧЁТЕСЛИ используется дважды.​

Определенный текст

=СЧЁТЕСЛИ(rng;»*txt*»)

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

В примере, активная ячейка содержит следующую формулу:

=СЧЁТЕСЛИ(B5:B12;»*a*»)

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат «а» путем сопоставления содержимого каждой ячейки с шаблоном «*a*», который поставляется в качестве критериев. Символ «*» (звездочка) является подстановочным в Excel, что означает «совпадают с любым количеством символов», так что эта модель будет считать любую ячейку, которая содержит «а» в любом положении. Количество ячеек, которые соответствуют этому шаблону рассчитывается как число.

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

=СЧЁТЕСЛИ(rng;»*»&a1&»*»)

X или Y

=СУММПРОИЗВ(—((ЕЧИСЛО(НАЙТИ(«abc»;B5:B12))+ЕЧИСЛО(НАЙТИ(«def»;B5:B12)))>0))

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

Когда вы подсчитывать ячейки с критерием «или», вы должны быть осторожны, чтобы не удвоить счет. Например, если вы подсчитываете ячейки, которые содержат «abc» или «def», вы не можете просто сложить вместе две функции СЧЁТЕСЛИ, потому что вы можете удвоить подсчет ячеек, которые содержат и «abc» и «def».

Решение одной формулой

Для решения одной формулойц, вы можете использовать комбинацию СУММПРОИЗВ с ЕЧИСЛО + НАЙТИ. Формула в ячейке E4 будет:

=СУММПРОИЗВ(—((ЕЧИСЛО(НАЙТИ(«abc»;B5:B12)) + ЕЧИСЛО(НАЙТИ(«def»;B5:B12)))>0))

Эта формула основана на формуле, которая находит текст внутри ячейки:

ЕЧИСЛО(НАЙТИ(«abc»;B5:B12)

При заданном диапазоне ячеек, этот фрагмент будет возвращать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы используем это дважды (один раз для «abc» и еще для «def»), мы получим два массива.

Далее мы складываем эти массивы вместе (+), сложение создаст новый единый массив чисел. Каждое число в этом массиве является результатом сложения истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит следующим образом:

{2; 0; 2; 0; 1; 0; 2}

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

И, наконец, СУММПРОИЗВ суммирует полученные числа.

Вспомогательный столбец решений

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

=—(СУММ(СЧЁТЕСЛИ(B4;{«*abc*»;»*def*»}))>0)

СЧЁТЕСЛИ возвращает массив, который содержит два пункта: подсчет для «abc» и подсчет на «def». Чтобы избежать двойного счета, мы складываем элементы, а потом возвращаем результат «истина/ложь» с «>0». Наконец, мы преобразуем значения Истина или Ложь в 1 и 0 с двойным минусом (—).

Итоговый результат равен 1 или 0 для каждой ячейки. Чтобы получить в общей сложности для всех ячеек в диапазоне, вам нужно просуммировать вспомогательный столбец.

Ошибки

=СУММПРОИЗВ(—ЕОШ(rng))

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

В примере, активная ячейка содержит следующую формулу:

=СУММПРОИЗВ(—ЕОШ(B5:B9))

СУММПРОИЗВ принимает один или несколько массивов и вычисляет сумму произведений соответствующих чисел. Если только один массив , он просто суммирует элементы в массиве.

Функция ЕОШ вычисляется для каждой ячейки в rng. Результатом является массив со значениями истина / ложь:

{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}

(—) — Оператор (называемый двойной одинарный) приводит истинные/ложные значения в 0 и 1. Результирующий массив выглядит так:

{1; 0; 1; 0; 0}

СУММПРОИЗВ затем суммирует элементы в этом массиве и возвращает общую сумму, которая, в данном примере, это число 2.

Примечание: ЕОШ подсчитывает все ошибки, кроме # N / A. Если вы хотите, чтобы также рассчитывалось # N / A, используйте функцию ЕОШИБКА вместо ЕОШ.

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

{=СУММ(—ЕОШ(B5:B9))}

Пять символов

=СЧЁТЕСЛИ(rng;»?????»)

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

В примере, активная ячейка содержит следующую формулу:

=СЧЁТЕСЛИ(B5:B10;»?????»)

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат пять символов путем сопоставления содержимого каждой ячейки с шаблоном «?????», который поставляется в качестве критерия для СЧЁТЕСЛИ. «?» символ является подстановочным в Excel, что означает «любой одиночный символ», так что эта модель будет считать ячейки, которые содержат любые пять символов. Подсчет ячеек, которые соответствуют этому шаблону возвращает число, в данном примере, это число 3.

Положительные числа

=СЧЁТЕСЛИ(rng;»>0″)

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

В примере, активная ячейка содержит следующую формулу:

=СЧЁТЕСЛИ(B5:B10; «>0»)

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

Вы можете легко настроить эту формулу для подсчета ячеек на основе других критериев. Например, для подсчета всех ячеек со значением, большим или равным 100, использовать эту формулу:

=СЧЁТЕСЛИ(rng;»>=100″)

Отрицательные числа

=СЧЁТЕСЛИ(rng;»<0″)

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

В примере, активная ячейка содержит следующую формулу:

=СЧЁТЕСЛИ(B5:B10;»<0″)

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

Вы можете легко настроить эту формулу для подсчета клеток на основе других критериев. Например, для подсчета всех ячеек со значением менее -10, используйте следующую формулу:

=СЧЁТЕСЛИ(rng;»<-10″)

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

=СЧЁТЕСЛИ(rng;»<«&А1)

Если в ячейке А1 находится значение «-5», критерии будут «<-5» после конъюнкции.

Цифры

=СЧЁТ(rng)

Для подсчета количества ячеек, которые содержат числа, используйте функцию СЧЁТ. Однако в общем виде формулы (выше) rng представляет собой диапазон ячеек.

В примере, активная ячейка содержит следующую формулу:

=СЧЁТ(B5:B8)

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

Нечетные числа

=СУММПРОИЗВ(—(ОСТАТ(rng;2)=1))

Для подсчета ячеек, которые содержат только нечетные числа, вы можете использовать формулу, основанную на функции СУММПРОИЗВ вместе с функцией ОСТАТ.

В примере, формула в ячейке E4 является:

=СУММПРОИЗВ(—(ОСТАТ(B5:B10;2)=1))

Эта формула рассчитала 4, так как есть 4 нечетных числа в диапазоне В5: В10 (который назван «rng» в формуле).

Функция СУММПРОИЗВ непосредственно работает с массивами.

Одна вещь, которую вы можете сделать довольно легко с СУММПРОИЗВ это выполнить тест на массив, используя один или несколько критериев, затем подсчитать результаты.

В этом случае, мы проводим тест на нечетное число, который использует функцию ОСТАТ:

ОСТАТ(rng;2)=1

ОСТАТ рассчитывает остаток от деления. В этом случае делитель равен 2, поэтому ОСТАТ рассчитывает остаток 1 для любого нечетного числа, а остаток 0 для четных чисел.

В функции СУММПРОИЗВ, этот тест выполняется в каждой ячейке B5: B10, результат представляет собой массив значений истина / ложь:

{ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА}

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

{0; 1; 1; 1; 0; 1}

СУММПРОИЗВ затем просто суммирует эти числа и рассчитывает 4.

Текст

=СЧЁТЕСЛИ(rng;»*»)

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

В примере, активная ячейка содержит следующую формулу:

=СЧЁТЕСЛИ(B5:B9;»*»)

СЧЁТЕСЛИ подсчитывает количество ячеек, которые соответствуют критериям. В этом случае критерий поставляется в качестве шаблонного символа «*», который совпадает с любым количеством символов текста.

Несколько замечаний:

• Логические значения истина и ложь не учитываются, как текст
• Числа не подсчитываются «*», если они не будут введены в виде текста
• Пустая клетка, которая начинается с апострофа ( ‘) будут учитываться.

Вы можете также использовать СУММПРОИЗВ для подсчета текстовых значений наряду с функцией ЕТЕКСТ так:

=СУММПРОИЗВ(—ЕТЕКСТ(rng))

Двойной дефис принуждает результат ЕТЕКСТ от логического значения ИСТИНА или ЛОЖЬ перейти к 1 и 0. СУММПРОИЗВ затем суммирует эти значения вместе, чтобы получить результат.

Чувствительная к регистру версия

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

НАЙТИ чувствительна к регистру, и вы должны дать ему диапазон ячеек, а затем использовать СУММПРОИЗВ для подсчета результатов. Формула выглядит следующим образом:

=СУММПРОИЗВ(—(ЕЧИСЛО(НАЙТИ(text;rng))))

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

Функция COUNTIF (СЧЁТЕСЛИ) в Excel. Как использовать?

Функция СЧЁТЕСЛИ (COUNTIF) в Excel используется для подсчета количества ячеек по заданному критерию.

Что возвращает функция

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

=COUNTIF(range,criteria) – английская версия

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

Аргументы функции

• range (где нужно искать) – диапазон в рамках которого вы хотите осуществить подсчет ячеек;
• criteria (что нужно найти) – критерий, по которому все ячейки в указанном вами диапазоне будут проверены на соответствие и только после этого функция подсчитает количество ячеек.

Дополнительная информация

• Критерием может служить число, выражение, ссылка на ячейку, текст или формула;
• Критерий, указанный как текст или математический/логический символ (=,+,-,/,*) должны быть указаны в двойных кавычках.
• Подстановочные знаки могут быть использованы в качестве критерия. В Excel существует три подстановочных знака – ?, *,

.

• знак “?” – сопоставляет любой одиночный символ;
• знак “*” – сопоставляет любые дополнительные символы;
• знак “

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

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

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

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

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

Критерий — критерий в форме числа, выражения, ссылки на ячейку или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, «32», «>32», «яблоки» или B4.

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

Данные будем брать из диапазона A15:A25 (см. файл примера ).

Критерий

Формула

Результат

Примечание

4

Подсчитывает количество ячеек, содержащих числа равных или более 10. Критерий указан в формуле

3

Подсчитывает количество ячеек, содержащих числа равных или более 11. Критерий указан через ссылку и параметр

Примечание. О подсчете значений, удовлетворяющих нескольким критериям читайте в статье Подсчет значений со множественными критериями. О подсчете чисел с более чем 15 значащих цифр читайте статью Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL.

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

Функция СЧЁТЕСЛИ() также годится для подсчета текстовых значений (см. Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL).

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

Так как любой дате в MS EXCEL соответствует определенное числовое значение, то настройка функции СЧЕТЕСЛИ() для дат не отличается от рассмотренного выше примера (см. файл примера Лист Даты ).

Если необходимо подсчитать количество дат, принадлежащих определенному месяцу, то нужно создать дополнительный столбец для вычисления месяца, затем записать формулу = СЧЁТЕСЛИ(B20:B30;2)

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

Обычно, в качестве аргумента критерий у функции СЧЁТЕСЛИ() указывают только одно значение. Например, =СЧЁТЕСЛИ(H2:H11;I2) . Если в качестве критерия указать ссылку на целый диапазон ячеек с критериями, то функция вернет массив. В файле примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18) возвращает массив <3:2:5>.

Для ввода формулы выделите диапазон ячеек такого же размера как и диапазон содержащий критерии. В Строке формул введите формулу и нажмите CTRL+SHIFT+ENTER, т.е. введите ее как формулу массива.

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

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

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

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

Выражение СЧЁТЕСЛИ(A6:A14;A6:A14) возвращает массив чисел <1:4:4:4:4:1:3:3:3>, который говорит о том, что значение 1 из списка в диапазоне А6:А15 — единственное, также в диапазоне 4 значения 2, одно значение 3, три значения 4. Это позволяет подсчитать количество неповторяющихся значений формулой =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A6:A14;A6:A14)=1)) .

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

Всем добрый день, сегодня я открываю рубрику «Функции» и начну с функции СЧЁТЕСЛИ. Честно говоря, не очень-то и хотел, ведь про функции можно почитать просто в справке Excel. Но потом вспомнил свои начинания в Excel и понял, что надо. Почему? На это есть несколько причин:

1. Функций много и пользователь часто просто не знает, что ищет, т.к. не знает названия функции.
2. Функции — первый шаг к облегчению жизни в Экселе.

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

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

Если с первым аргументом более-менее понятно, можно подставить диапазон типа A1:A5 или просто название диапазона, то со вторым уже не очень, потому что возможности задания критерия достаточно обширны и часто незнакомы тем, кто не сталкивается с логическими выражениями.

Самые простые форматы «Критерия»:

• Ячейка строго с определенным значением, можно поставить значения («яблоко»), (B4),(36). Регистр не учитывается, но даже лишний пробел уже включит в подсчет ячейку.
• Больше или меньше определенного числа. Тут уже идет в ход знак равенства, точнее неравенств, а именно («>5»);(«<>10″);(» «&СРЗНАЧ(A1:A100))
• Содержит определенное количество символов, например 5 символов:(«. «)
• Определенный текст, который содержится в ячейке: («*солнце*»)
• Текст, который начинается с определенного слова: («Но*»)
• Ошибки: («#ДЕЛ/0!»)
• Логические значения («ИСТИНА»)

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

Подсчет ячеек в Excel, используя функции СЧЕТ и СЧЕТЕСЛИ

Очень часто при работе в Excel требуется подсчитать количество ячеек на рабочем листе. Это могут быть пустые или заполненные ячейки, содержащие только числовые значения, а в некоторых случаях, их содержимое должно отвечать определенным критериям. В этом уроке мы подробно разберем две основные функции Excel для подсчета данных – СЧЕТ и СЧЕТЕСЛИ, а также познакомимся с менее популярными – СЧЕТЗ, СЧИТАТЬПУСТОТЫ и СЧЕТЕСЛИМН.

Статистическая функция СЧЕТ подсчитывает количество ячеек в списке аргументов, которые содержат только числовые значения. Например, на рисунке ниже мы подсчитали количество ячеек в диапазоне, который полностью состоит из чисел:

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

А вот ячейки, содержащие значения даты и времени, учитываются:

Функция СЧЕТ может подсчитывать количество ячеек сразу в нескольких несмежных диапазонах:

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

Решить обратную задачу, т.е. подсчитать количество пустых ячеек в Excel, Вы сможете, применив функцию СЧИТАТЬПУСТОТЫ:

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

Следующая формула возвращает количество ячеек, значение которых больше содержимого ячейки А4.

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

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

Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Н (без учета регистра), можно воспользоваться следующей формулой:

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

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

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

К примеру, следующая формула подсчитывает ячейки, значения которых больше нуля, но меньше 50:

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

Функции Excel для подсчета данных очень полезны и могут пригодиться практически в любой ситуации. Надеюсь, что данный урок открыл для Вас все тайны функций СЧЕТ и СЧЕТЕСЛИ, а также их ближайших соратников – СЧЕТЗ, СЧИТАТЬПУСТОТЫ и СЧЕТЕСЛИМН. Возвращайтесь к нам почаще. Всего Вам доброго и успехов в изучении Excel.

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

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

Синтаксис и особенности функции

Сначала рассмотрим аргументы функции:

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

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

В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.

Рекомендации для правильной работы функции:

• Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
• Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
• Функция не учитывает регистр текстовых значений.
• При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (

).

Функция СЧЕТЕСЛИ в Excel: примеры

Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.

У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

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

Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.

Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:

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

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).

Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:

Формула посчитала «кровати» и «банкетки».

Используем в функции СЧЕТЕСЛИ условие поиска «не равно».

Формула: =СЧЁТЕСЛИ(A1:A11;»<>«&»стулья»). Оператор «<>» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

При применении ссылки формула будет выглядеть так:

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

1. Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
2. Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.
3. Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).
4. Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
5. Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

Посчитаем количество реализованных товаров по группам.

1. Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.
2. Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» — «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).

Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.

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

Обычно, в качестве аргумента критерий у функции СЧЁТЕСЛИ() указывают только одно значение. Например, =СЧЁТЕСЛИ(H2:H11;I2) . Если в качестве критерия указать ссылку на целый диапазон ячеек с критериями, то функция вернет массив. В файле примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18) возвращает массив {3:2:5}.

Для ввода формулы выделите диапазон ячеек такого же размера как и диапазон содержащий критерии. В Строке формул введите формулу и нажмите CTRL+SHIFT+ENTER , т.е. введите ее как формулу массива .

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

Синтаксис и особенности функции

Сначала рассмотрим аргументы функции:

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

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

В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.

Рекомендации для правильной работы функции:

• Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
• Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
• Функция не учитывает регистр текстовых значений.
• При формулировании условия подсчета можно использовать подстановочные знаки. «?» – любой символ. «*» – любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
• Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.

Функция Счётесли

Счётесли (диапазон; критерий)

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

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

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

Критерий проверки необходимо заключать в кавычки.

Критерий не чувствителен к регистру. К примеру, функция не увидит разницы между словами «налог» и «НАЛОГ».

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

1. Подсчет количества ячеек, содержащих отрицательные значения

   Счётесли(А1:С2;”<0″) Диапазон – А1:С2 , критерий – “<0”

1. Подсчет количества ячеек, значение которых больше содержимого ячейки А4:

   Счётесли(А1:С2;”>”&A4) Диапазон – А1:С2 , критерий – “>”&A4

1. Подсчет количества ячеек со словом “текст” (регистр не имеет значения).

   Счётесли(А1:С2;”текст”) Диапазон – А1:С2 , критерий – “текст”

1. Для текстовых значений в критерии можно использовать подстановочные символы * и ? .

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

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

   Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Т (без учета регистра), можно воспользоваться следующей формулой:

   Счётесли(А1:С2;”Т * “) Диапазон – А1:С2 , критерий – “Т * “

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

Счётесли(А1:С2;”????”) Диапазон – А1:С2 , критерий – “????”

В функции Счётесли используется только один критерий.

Чтобы провести подсчет по нескольким условиям, необходимо воспользоваться функцией Счётеслимн.

Функция Счётеслимн

Счётеслимн (диапазон1; условие1; [диапазон2]; [условие2]; …).

Функция аналогична функции Счётеслимн, за исключением того, что может содержать до 127 диапазонов и условий, где первое является обязательным, а последующие – нет.

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

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

1. Подсчет количества ячеек, в которых находятся даты из определенного периода (например, после 15 января и до 1 марта 2015г.).

Счётеслимн(C1:C8;”>15.01.2015″;C1:C8;”<1.03.2015″)

Диапазон один – C1:C8 , условия – “>15.01.2015” и “<1.03.2015”

Для подсчета текста.

Давайте разбираться, как это работает. На рисунке ниже вы видите список заказов, выполненных менеджерами. Выражение =СЧЕТЕСЛИ(В2:В22,”Никитенко”) подсчитывает, сколько раз этот работник присутствует в списке:

Замечание. Критерий не чувствителен к регистру букв, поэтому можно вводить как прописные, так и строчные буквы.

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

К примеру, в нашей таблице есть несколько заказчиков «Корона» из разных городов. Нам необходимо подсчитать общее количество заказов «Корона» независимо от города.

=СЧЁТЕСЛИ(A2:A22;”*Коро*”)

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

Кроме того, указывать условие прямо в формуле не совсем рационально, так как при необходимости подсчитать какие-то другие значения вам придется корректировать её. А это не слишком удобно.

Рекомендуется условие записывать в какую-либо ячейку и затем ссылаться на нее. Так мы сделали в H9. Также можно употребить подстановочные знаки со ссылками с помощью оператора конкатенации (&). Например, вместо того, чтобы указывать «* Коро *» непосредственно в формуле, вы можете записать его куда-нибудь, и использовать следующую конструкцию для подсчета ячеек, содержащих «Коро»:

=СЧЁТЕСЛИ(A2:A22;”*”&H8&”*”)

Подсчет ячеек, начинающихся или заканчивающихся определенными символами

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

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

=СЧЁТЕСЛИ(A2:A22;”К*”) – считать значения, которые начинаются с « К» .

=СЧЁТЕСЛИ(A2:A22;”*р”) – считать заканчивающиеся буквой «р».

Если вы ищете количество ячеек, которые начинаются или заканчиваются определенными буквами и содержат точное количество символов, то поставьте вопросительный знак (?):

=СЧЁТЕСЛИ(С2:С22;”????д”) – находит количество буквой «д» в конце и текст в которых состоит из 5 букв, включая пробелы.

= СЧЁТЕСЛИ(С2:С22,”??”) – считает количество состоящих из 2 символов, включая пробелы.

Примечание. Чтобы узнать количество клеток, содержащих в тексте знак вопроса или звездочку, введите тильду (~) перед символом ? или *.

Например, = СЧЁТЕСЛИ(С2:С22,”*~?*”) будут подсчитаны все позиции, содержащие знак вопроса в диапазоне С2:С22.

Подсчет чисел по условию.

В отношении чисел редко случается, что нужно подсчитать количество их, равных какому-то определённому числу. Тем не менее, укажем, что записать нужно примерно следующее:

= СЧЁТЕСЛИ(D2:D22,10000)

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

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

Обратите внимание, что математический оператор вместе с числом всегда заключен в кавычки .

В нашем примере

=СЧЁТЕСЛИ(D2:D22;”>10000″)

Считаем количество крупных заказов на сумму более 10 000. Обратите внимание, что условие подсчета мы записываем здесь в виде текстовой строки и поэтому заключаем его в двойные кавычки.

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

Замечание. В случае использования ссылки, вы должны заключить математический оператор в кавычки и добавить амперсанд (&) перед ним. Например, чтобы подсчитать числа в диапазоне D2: D9, превышающие D3, используйте =СЧЕТЕСЛИ(D2:D9,”>”&D3)

Если вы хотите сосчитать записи, которые содержат математический оператор, как часть их содержимого, то есть символ «>», «<» или «=», то употребите в условиях подстановочный знак с оператором. Такие критерии будут рассматриваться как текстовая строка, а не числовое выражение.

Например, =СЧЕТЕСЛИ(D2:D9,”*>5*”) будет подсчитывать все позиции в диапазоне D2: D9 с таким содержимым, как «Доставка >5 дней» или «>5 единиц в наличии».

Примеры с датами.

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

Позвольте привести несколько примеров:

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

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

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

Синтаксис функции СЧЕТЕСЛИ очень прост:

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

Как видите, есть только 2 аргумента функции СЧЕТЕСЛИ, оба из которых обязательны:

• диапазон – определяет одну или несколько ячеек для подсчета. Вы помещаете диапазон в формулу, как обычно, в Excel, например. A1:A20.
• критерии – определяет условие, которое сообщает функции, которую подсчитывают ячейки. Это может быть число, текстовая строка, ссылка на ячейку или выражение (например, “10”, A2, “>=10”).

Вот простейший пример функции СЧЕТЕСЛИ в Excel. Формула =СЧЁТЕСЛИ(C2:C7;”Иванов Иван”) подсчитывает, сколько заявок поступало от Иванова Ивана:

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

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

Вместо ввода текста вы можете использовать ссылку на любую ячейку, содержащую это слово или слова, и получить абсолютно одинаковые результаты, например: =СЧЕТЕСЛИ(С1:С7; С2).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ со ссылкой на ячейку

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

Формула ЕСЛИ в Excel – примеры нескольких условий

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

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

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

В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

Дополнительная информация

• Критерием может служить число, выражение, ссылка на ячейку, текст или формула;
• Критерий, указанный как текст или математический/логический символ (=,+,-,/,*) должны быть указаны в двойных кавычках.
• Подстановочные знаки могут быть использованы в качестве критерия. В Excel существует три подстановочных знака – ?, *, ~.
  • знак “?” – сопоставляет любой одиночный символ;
  • знак “*” – сопоставляет любые дополнительные символы;
  • знак “~” – используется, если нужно найти сам вопросительный знак или звездочку.
• Для функции не важно, с заглавной или строчной буквы написан критерий (“Привет” или “привет”).

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

​=SUMIF(B1:B5,”>9″)​ в файле примера):​ в других случаях. ​Для подсчета результата и​ просто установить курсор​Мастера функций​

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

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

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

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

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

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

или

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

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

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

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

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

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

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

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