Суммеслимн в excel много условий

Обновлено: 06.01.2025

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

Давайте рассмотрим как это сделать.

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

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

Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.

Синтаксис функции выглядит так:

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

диапазон_суммирования – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
[диапазон_условия2], условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;

Формула для суммирования значений между двумя датами

Итак, как я уже писал выше, у нас есть таблица с данными продаж по каждому дню. Наша задача посчитать сумму продаж за период с 1 июня 2018 по 15 июня 2018 года.

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

После ввода этой формулы, функция вернет значение 559 134₽. Это значение соответствует сумме продаж за период с 1 июня по 15 июня 2018 года.

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

Как работает эта формула

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

Давайте разложим формулу на составные части для понимания ее работы:

Первым делом мы указываем диапазон с данными продаж (B2:B28), среди которого нам нужно выбрать какие значения мы будем суммировать
Затем, мы указываем диапазон с данными, к которому будет применяться проверка на соответствие условию. В нашем случае это диапазон с датами (A2:A28)
Следующим шагом мы задаем условие по отношению к диапазону с датами, по которому формула должна определить какие данные суммировать. Мы указали первое условие, что дата должна быть больше или равна 01.06.2018
Заключительным шагом мы задаем второе условие к диапазону с датами (A2:A28), по которому формула должна суммировать данные за период меньший или равный 15.06.2018

Как результат, функция суммирует значения в диапазоне с 1 по 15 июня 2018 года.

Как суммировать значения между двумя динамическими датами

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

В этом случае нам поможет следующая формула:

Как работает эта формула

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

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

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

Если у вас остались вопросы по этому примеру оставляйте их в комментариях.

Функция СУММЕСЛИМН — одна из математических и тригонометрических функций, которая суммирует все аргументы, удовлетворяющие нескольким условиям. Например, с помощью функции СУММЕСЛИМН можно найти число всех розничных продавцов, (1) проживающих в одном регионе, (2) чей доход превышает установленный уровень.

Это видео — часть учебного курса Усложненные функции ЕСЛИ.

Синтаксис

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

=СУММЕСЛИМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Артем")

=СУММЕСЛИМН(A2:A9; B2:B9; "<>Бананы"; C2:C9; "Артем")

Имя аргумента

Диапазон_суммирования (обязательный аргумент)

Диапазон ячеек для суммирования.

Диапазон_условия1 (обязательный аргумент)

Диапазон, в котором проверяется Условие1.

Диапазон_условия1 и Условие1 составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента Диапазон_суммирования.

Условие1 (обязательный аргумент)

Условие, определяющее, какие ячейки суммируются в аргументе Диапазон_условия1. Например, условия могут вводится в следующем виде: 32, ">32", B4, "яблоки" или "32".

Диапазон_условия2, Условие2, … (необязательный аргумент)

Дополнительные диапазоны и условия для них. Можно ввести до 127 пар диапазонов и условий.

Примеры

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

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

=СУММЕСЛИМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Артем")

Суммирует количество продуктов, названия которых начинаются с Я и которые были проданы продавцом Артем. Для поиска совпадающих названий продуктов в Criteria_range1 B2:B Criteria_range2 9 в группе Условия1(=A*) используется подп. Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 20.

=СУММЕСЛИМН(A2:A9; B2:B9; "<>Бананы"; C2:C9; "Артем")

Суммирует количество продуктов, которые не являются бананами и которые были проданы продавцом по имени Артем. Он исключает бананы, используя<>в критерии1, "<>Бананы"и ищет имя "Том" в Criteria_range2 C2:C9. Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 30.

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

Вместо ожидаемого результата отображается 0 (нуль).

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

Неверный результат возвращается в том случае, если диапазон ячеек, заданный аргументом Диапазон_суммирования, содержит значение ИСТИНА или ЛОЖЬ.

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

Ячейки в аргументе Диапазон_суммирования, которым присвоено значение ИСТИНА, оцениваются как 1. Ячейки, которым присвоено значение ЛОЖЬ, оцениваются как 0 (ноль).

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

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

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".

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

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.

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

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.

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

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

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2="Копейка")*(B2="Григорьев")

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

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

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

=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

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

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:

Добрый день друзья!

Я вот решил что уделяю мало внимания функциям, которые используются в Excel и поэтому решил не откладывать это дело в долгий ящик, а написать цикл статей о различных нужных функциях. Первой «синичкой» станет статья, функция СУММЕСЛИМН в Excel. Я уже описывал и рассказывал в деталях о функциях СУММ, ЕСЛИ и СУММЕСЛИ, а вот пришло время розширить знания о возможностях суммирования еще и функцией СУММЕСЛИМН. Что можно о ней сказать, чем же она отличается от других функций суммирования и чем она может оказаться вам полезной. Все ранее рассматриваемые функции (за исключением, функции ЕСЛИ) поддерживают поиск по 1 аргументу, а в функции ЕСЛИ, аж целых 7, тогда как функция СУММЕСЛИМН в Excel поддерживает поиск по 127 критериям, а это согласитесь веский аргумент в поиске. Сразу замечу, что эта функция была введена в работу с версии Excel 2007, поэтому для пользователей более ранних версий, данная статья будет только ознакомительная. Я в принципе смутно себе представляю себе задачу, где нужно использовать такую массу критериев, но всё же это говорит о том, что функции достойна того, что бы ее знали и умели пользоваться. А для этого надо знать, как минимум, ее орфографию, рассмотрим подробнее:

=СУММЕСЛИМН( диапазон для суммирования; диапазон где условия; наше условие; [диапазон где условия; новое условие]; и т.д. до 127 раз), где,

диапазон для суммирования – это тот диапазон, где находятся данные, которые нужно суммировать, когда наши условия будут выполнены;

диапазон где условие – это диапазон откуда выбирается данные согласно нашему критерию для дальнейшего суммирования;

наше условие – это, собственно, есть наше условие, которое нужно выбрать в таблице и просуммировать его значения.

Внимание! Функция СУММЕСЛИМН в Excel умеет и может работать со знаками подстановки такими как «*» — для замены любого количества символов и знаком «?» — для замены любого одного символа, а также функция успешно использует операторы отношения, такие как «=», «>», «<» и т.п.

А теперь давайте на примере рассмотрим, как же собственно всё должно выглядеть, писаться и работать. К примеру, у нас есть компания, которая продает фрукты и овощи в страны СНГ и нам интересно, сколько же было продано «Бананов» в страну «Беларусь», это и будут 2 наших критерия.

=СУММЕСЛИМН(C2:C15;A2:A15;"Беларусь";B2:B15;"Банан") Как видите, наша формула ищет в диапазоне A2:A15 все записи по критерию «Беларусь», потом в диапазоне B2:B15 отбирает из полученного результата всё что равняется критерию «Банан» и окончательный результат суммируется с диапазона C2:C15. Всё результат готов!

Совет! Если вы используете автокопирование формул, тогда вам нужно зафиксировать диапазоны ячеек с помощью знака «$», превратив ее в абсолютную ссылку.

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

До новых встреч!

"Бедность и богатство – суть слова для обозначения нужды и изобилия. Следовательно, кто нуждается, тот не богат, а кто не нуждается, тот не беден.
"
Демокрит

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

СУММЕСЛИ в Эксель

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

Синтаксис функции для нашего примера будет:

Первым аргументом (A2:A13) мы задаем область, в которой будем проверять условие, в нашем случае это фамилии менеджеров, вторым аргументом (E2) мы задаем ячейку, в которой указано условие, третьи аргументом (C2:C13) мы задаем область с выручкой, которую должны просуммировать.

Что будет делать формула? Она сопоставит в каждой строке с фамилиями менеджеров фамилию из условия и фамилию в ячейке и если они совпали, то из соответствующей строки с выручкой возьмет значение и добавит к результату. Если вы все сделали правильно, то сумма выручки по менеджеру Петров должно составить 3 850 рублей.

СУММЕСЛИМН в Эксель

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

В нашем случае мы хотим узнать, какую выручку нам принес менеджер Петров, работая в городе Москва. Синтаксис функции:

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

Читайте также: