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

Заполняем аргументы функции

Функция ЕСЛИ предполагает, что пользователь создаёт запрос и указывает два варианта ответа на него. Этот запрос и варианты ответа — и есть три аргумента функции.

«Лог_выражение» (логическое выражение) — запрос пользователя, который функция будет проверять. В нашем примере это стоимость автомобилей.

Нужно, чтобы функция определила автомобили стоимостью менее или равной 2 500 000 рублей. Порядок действий следующий:

1. Ставим курсор в окно «Лог_выражение» в построителе формул.

2. Выбираем первое значение столбца «Цена, руб.» — ячейку Е2. Обозначение ячейки переносится в окно «Лог_выражение» построителя формул и одновременно с этим появляется в строке ссылок.

Выделяем первую ячейку столбца, в котором нужно проверить условие пользователяСкриншот: Excel / Skillbox Media

3. Дополняем значение E2 запросом пользователя: <=2500000. Одновременно с этим формула в строке ссылок принимает вид:fx=ЕСЛИ(E2<=2500000).

Так выглядит первый аргумент функции после заполненияСкриншот: Excel / Skillbox Media

«Значение_если_истина» — результат, который функция принесёт в ячейку, если значение совпадёт с запросом пользователя. В случае с примером — что напишет функция, если проверяемая стоимость авто будет меньше либо равна 2 500 000 рублей.

Наша задача — определить, подходят автомобили каталога под запрос клиента или нет. Поэтому в окне аргумента истины введём значение «Подходит» — можно вводить его без кавычек, Excel добавит их самостоятельно.

Заполняем значение ИСТИНАСкриншот: Excel / Skillbox Media

«Значение_если_ложь» — результат, который функция принесёт в ячейку, если значение не совпадёт с запросом пользователя. В нашем примере — что напишет функция, если проверяемая стоимость авто будет больше 2 500 000 рублей.

Введём в качестве аргумента ЛОЖЬ значение «Не подходит».

Итоговая формула в строке ссылок примет вид:fx=ЕСЛИ(E2<=2500000;»Подходит»;»Не подходит»).

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

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

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

Начните запись со стандартного ее объявления =СУММЕСЛИМН, откройте скобки и напишите, какой диапазон ячеек будет суммироваться. Не забудьте закрыть объявление аргумента знаком ;.

Следом идет второй аргумент — совпадение по названию в ячейке. Запись этого параметра уже рассматривалась выше и еще раз ее можно увидеть на скриншоте.

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

Полностью строка этой функции имеет вид =СУММЕСЛИМН(C2:C25;A2:A25;«Сентябрь»;B2:B25;«>29»), а вам остается лишь подстроить ее под себя и получить правильный результат. Может показаться, что объявлять каждый аргумент и не запутаться в символах сложно, но при раздельном подходе никаких проблем возникнуть не должно.

Подробнее: 10 популярных математических функций Microsoft Excel

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

Опишите, что у вас не получилось.

Наши специалисты постараются ответить максимально быстро.

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

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
D2 и “>=”&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

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

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);–(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :

Результатом вычисления A2_A13=D2 является массив Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2_A13=D2 , а затем нажав F9 ;
Результатом вычисления B2:B13 является массив, т.е. просто значения из столбца B ;
Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является . При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив . Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15 ).

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Как СУММЕСЛИМН работает с датами?

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

Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце А попадает в последние 7 дней, включая сегодняшний день (предполагается, что сегодня 7 февраля):

Замечание. Когда вы при составлении ограничения используете другую функцию Excel вместе с логическим оператором, нужно использовать амперсанд (&) для объединения всего выражения в виде текста, например «<=»&СЕГОДНЯ().

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

Однако СУММЕСЛИМН сложение делает гораздо проще и понятнее, не так ли?

Как работает СУММЕСЛИМН?

При помощи СУММЕСЛИМН можно найти сумму величин, для которых есть много условий. Она появилась впервые в MS Excel 2007, поэтому вы можете использовать ее во всех современных версиях программы.

По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:

Первые 3 аргумента являются обязательными, а вот дополнительные диапазоны и связанные с ними условия являются необязательными.

диапазон_суммирования — требуется одна или более ячеек для суммирования. Это может быть отдельная клеточка, область или именованный диапазон. Суммируются только ячейки с чисами; пустые и текстовые значения игнорируются.

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

условие1- обязательное первое условие, которое должно быть выполнено. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, «> = 10», A1, «яблоко» или СЕГОДНЯ().

Все, что следует далее — это дополнительные диапазоны и связанные с ними критерии. Они не являются обязательными, но если у вас только одно ограничение, то зачем вам эта функция? Просто используйте СУММЕСЛИ. Тем не менее, вы можете использовать до 127 пар диапазон/условие.

Важно! Функция СУММЕСЛИМН работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным критериям (все требования соблюдаются для этой ячейки)

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

Предположим, что вам нужно свести значения с более чем одним условием, например суммой продаж продуктов в определенном регионе. Это хороший случай для использования функции СУММЕСС в формуле.

Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж «Мясо» (из столбца C) в регионе «Южный» (из столбца A).

Вот формула, с помощью которая можно сопровождать эту формулу:

Результат — значение 14 719.

Рассмотрим каждую часть формулы более подробно.

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

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

Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие («Южный»)

Обратите внимание на запятую между аргументами:. Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные

Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные.

Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.

Если вы ввели в Excel функцию СУММЕСС, если вы не помните аргументов, справка готова. После того как вы введете =СУММЕСС(, под формулой появится автозавершенная формула со списком аргументов в правильном порядке.

На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится «Южный» (критерий1).

По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.

Если вы нажмете кнопку СУММЕСС в автозавершении формул, откроется статья с дополнительной справкой.

Задача 5 (Два критерия к разным столбцам, условие отбора ИЛИ)

Найдем сумму продаж Белова
ИЛИ Продаж
>6000 Т.е. нужно отобрать строки, в которых в столбце Продавец
значится Белов
ИЛИ в столбце Продажи
имеется значение >6000
.

Критерии должны располагаться в разных строках и в разных столбцах, т.к. отбираются строки, у которых в поле Продавец значение Белов
ИЛИ
строки, у которых в поле Продажи значение >6000
(функция БДСУММ
()
как бы совершает 2 прохода по таблице с разными критериями для 2-х разных полей).

Записать саму формулу можно так =БДСУММ(B8:C13;C8;G2:H4)

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

=СУММЕСЛИ(B9:B13;G3;C9:C13)+СУММЕСЛИ(C9:C13;H4)-СУММЕСЛИМН(C9:C13;B9:B13;G3;C9:C13;H4)
или

=СУММЕСЛИ(B9:B13;»белов»;C9:C13)+СУММЕСЛИ(C9:C13;»>6000″)-СУММЕСЛИМН(C9:C13;B9:B13;»белов»;C9:C13;»>6000″)

Использование СЧЕТЕСЛИ для подсчета дубликатов.

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

1. Ищем дубликаты в одном столбце

Эта простое выражение СЧЁТЕСЛИ($A$2:$A$24;A2)>1 найдет все одинаковые записи в A2: A24.

А другая формула СЧЁТЕСЛИ(B2:B24;ИСТИНА) сообщит вам, сколько существует дубликатов:

Для более наглядного представления найденных совпадений я использовал условное форматирование значения ИСТИНА.

2. Сколько совпадений между двумя столбцами?

Сравним список2 со списком1. В столбце Е берем последовательно каждое значение из списка2 и считаем, сколько раз оно встречается в списке1. Если совпадений ноль, значит это уникальное значение. На рисунке такие выделены цветом при помощи условного форматирования.

Выражение =СЧЁТЕСЛИ($A$2:$A$24;C2) копируем вниз по столбцу Е.

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

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

Подсчитаем количество уникальных значений в списке2:

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

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

3. Сколько дубликатов и уникальных значений в строке?

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

Считаем количество дубликатов:

Видим, что 13 выпадало 2 раза.

Подсчитать уникальные значения:

Синтаксис

СУММЕСЛИ(диапазон; условие; )

Аргументы функции СУММЕСЛИ описаны ниже.

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

Условие .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Например, условие может быть представлено в таком виде: 32, “>32”, B5, “32”, “яблоки” или СЕГОДНЯ().

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

Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие)

Диапазон_суммирования .Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).

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

СУММИРОВАТЬ все значения между двумя датами для определенного продукта

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

Например, предположим, что у вас есть тот же набор данных (как показано ниже), но на этот раз вы хотите узнать общие продажи принтеров, которые произошли между двумя указанными датами (1 января и 31 января).

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

Ниже приведена формула, которая сделает это:

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

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

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

В приведенной выше формуле я использовал оператор not-equal-to-operator (), чтобы исключить получение значений для Scanner в результате.

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

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

Надеюсь, вы нашли этот урок полезным.

Использование операторов сравнения.

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

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

= СУММЕСЛИ (MI2: MI21; SI2: SI21; «Красный»; RE2: RE21; «> 100”)

Но рациональнее использовать ссылки, как это сделано на рисунке:

= СУММЕСЛИ (MI2: MI21; SI2: SI21; I2; RE2: RE21; I4)

Примечание

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

Синтаксис этой функции, помимо работы с числами, текстом и датами, точно такой же, как и для СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу об условной сумме.

Как еще мы можем решить нашу проблему?

Способ 2. Используем функцию СУММПРОИЗВ.

Давайте подробнее рассмотрим, как работает SUMPRODUCT():

= СУММПРОИЗВ (- (B2: B21 = $ I $ 12), — (D2: D21> I13), E2: E21)

Результат вычисления B2: B21 = $ I $ 12 — это массив

{ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ.: ЛОЖЬ

ИСТИНА означает, что код покупателя соответствует условию, например, слово Красный. Этот массив можно увидеть, выбрав в строке формул B2: B21 = $ I $ 12, а затем нажав F9.

А что это за странные минусовые знаки перед этими выражениями? Дело в том, что нам нужны не эти логические выражения, а числа, чтобы затем их можно было умножить и сложить. Если Excel выполняет математическую операцию с логическим выражением, он автоматически преобразует его в число. А знак минус означает умножение на -1. А если умножить на -1 дважды, результат не изменится. Мы помним это из школьной математики

И в результате логический массив превратится в массив чисел {0: 1: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 1: 0: 0: 0: 1: 0: 0: 0}.

Результат вычисления D2: D21> I13 — это массив

{ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ}.

ИСТИНА соответствует пределу «количество больше 100». Здесь мы также используем двойное отрицание для преобразования логических значений в числа.

И, наконец, результатом вычисления B2: B13 является массив {11250: 23210: 12960: 3150: 5280: 9750: 3690: 18300: 5720: 6150: 8400: 2160: 7200: 1890: 17050: 3450: 15840: 2250 : 7200: 8250}, т.е только числа из столбца E.

Результатом умножения этих трех массивов на элемент будет {0: 23210: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 15840: 0: 0: 0 }. Складываем эти продукты и получаем 39050.

Способ 3. Формула массива.

И еще один вариант расчета — применяем формулу массива. В I14 пишем:

= СУММ ((L2: L21 = I12) * (L2: L21> I13) * (L2: E21))

Не забудьте нажать CTRL + SHIFT + ENTER в конце, чтобы обозначить это выражение как формулу массива. Программа автоматически добавит фигурные скобки в начало и в конец. Опять получаем результат 39050.

Способ 4. Автофильтр.

Другой альтернативой является использование автоматического фильтра. Для этого преобразуйте диапазон данных A1: F21 в интеллектуальную таблицу. Напоминаю, что для этого в меню «Главная» выберите «Форматировать как таблицу». Далее добавляем итоговую строку (вкладка «Дизайн») и устанавливаем необходимые фильтры.

Без формул будет определено общее количество отфильтрованных строк.

Суммирование значений по заданным аргументам при помощи функций ВПР и СУММ

При работе с числами часто необходимо не только извлечь цифры из таблицы, но вместе с тем и сложить числа из выбранных строк или столбцов. В таких случаях применяется сочетание функций ВПР() и СУММ().

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

Таблица данных — ежемесячные продажи:

Эта задача решается через использование в функции ВПР() массива в третьем аргументе (номер_столбца).

=СУММ (ВПР (искомое значение, интервальный просмотр, {2,3,4}, ЛОЖЬ)).

Исходя из примера выше, в массиве {2, 3, 4} рассматриваемого аргумента для суммирования чисел из столбцов 2,3 и 4 используются функции ВПР().

Для получения искомого числа из столбцов от B до M, сочетание функций ВПР() и СУММ() выглядит так:

=СУММ (ВПР (B2, ‘Monthly sales’! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, ЛОЖЬ))

{=СУММ (ВПР(B2, ‘Monthly sales’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, ЛОЖЬ))}

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

СОВЕТ. Искомое значение может указываться при помощи ссылки на столбец , как показано на рисунке 2. Поскольку рассматриваемая в примере таблица создана посредством инструмента «Вставка” → «Таблица», она стала полнофункциональной. В такой таблице внесенные изменения по умолчанию дублируются на выбранный столбец в вышестоящую ячейку.Исходя из указанной информации, можно сделать вывод о том, что совмещать функции ВПР() и СУММ() в работе достаточно просто. Хотя предложенный способ – не единственно верный путь, в частности, при работе с таблицами с большим количеством данных. В связи с тем, что каждый аргумент делает запрос к функции ВПР(), применение большого количества формул массива отрицательно сказывается на скорости работы книги Excel. Отсюда вывод: большое количество формул в книге и аргументов в массиве может существенно замедлить работу Excel.

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

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

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

=СУММЕСЛИМН( диапазон_суммирования; диапазон_условия1; условие1; ; …)

диапазон_суммирования – обязательный аргумент, принимающий ссылку на ячейки с числами, для которых будет вычислено суммарное значение с учетом установленных критериев;
диапазон_условия1 – обязательный аргумент, принимающий ссылку на ячейки, в которых содержатся данные для проверки на соответствие заданному критерию;
условие1 – обязательный аргумент, принимающий ссылку на ячейку с условием поиска, текстовую запись логического выражения (например, «>=100» или «0»), проверяемый текст (например, «Продавец_1», «Товар_1») или числовое значение (Например, 20), определяющие ячейки в диапазоне условия1, для которых будут отобраны и просуммированы данные из диапазона суммирования.

Последующие аргументы являются необязательными для заполнения, а их смысл соответствует аргументам диапазон_условия1; условие1. Всего может быть задано до 127 условий отбора данных для суммирования.

Если в качестве аргумента диапазон_суммирования была передана ссылка на диапазон ячеек, содержащие текстовые строки или пустые значения, функция СУММЕСЛИМН будет их игнорировать.
В качестве условий суммирования данных могут выступать числовые значения, логические выражения, текстовые строки, формулы, ссылки на ячейки, содержащие условия.
Условия проверки в виде текста или выражений с логическими и математическими символами должны быть указаны в кавычках.
Максимальная длина условия, переданного в качестве аргумента условие1 (условие2 и т. д.) не должна превышать 255 символов.
Для создания неточных текстовых критериев поиска требуемых значений можно использовать подстановочные знаки «?» и «*», замещающие один или несколько символов соответственно.
Если в диапазоне ячеек, переданных в качестве аргумента диапазон_суммирования, есть ячейки, содержащие логические ИСТИНА или ЛОЖЬ, они будут автоматически преобразованы к числовым значениям 1 и 0 соответственно и будут учтены в итоговой сумме.
Число ячеек в диапазонах суммирования и условиях должно быть одинаковым, иначе функция СУММЕСЛИМН вернет код ошибки #ЗНАЧ!.

Синтаксис функции БДСУММ()

Для использования этой функции требуется чтобы:

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

БДСУММ(база_данных;поле;условия
)
База_данных
представляет собой диапазон ячеек с данными связанными логически, т.е. таблицу. Верхняя строка таблицы должна содержать заголовки всех столбцов.Поле
— Заголовок столбца, по которому производится суммирование (т.е. столбец с числами). Аргумент Поле
можно заполнить введя:

текст с заголовком столбца в двойных кавычках, например «Возраст» или «Урожай»,
число (без кавычек), задающее положение столбца в таблице (указанной в аргументе база_данных
): 1 — для первого столбца, 2 — для второго и т.д.
ссылку на заголовок столбца.

Условия
— интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев). Структура таблицы с критериями отбора для БДСУММ()
аналогична структуре для .

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

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

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

Но как быть, если нам нужно быстро посчитать заработные платы только продавцов? В дело вступает использование функции СУММЕСЛИ.

Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Поэтому проставляем E2:E14.
Критерий выбора в нашем случае – продавец. Заключаем слово в кавычки и ставим вторым аргументом.
Диапазон суммирования – это заработные платы, потому что нам нужно узнать сумму зарплат всех продавцов. Поэтому F2:F14.

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

Аналогично можно подсчитать зарплаты всех менеджеров, продавцов-кассиров и охранников. Когда табличка небольшая, кажется, что все можно сосчитать и вручную, но при работе со списками, в которых по несколько сотен позиций, целесообразно использовать СУММЕСЛИ.