Функция суммеслимн в excel

Функция СУММЕСЛИМН в Excel

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

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

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

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

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

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

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

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

Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL (Часть 2.Условие И)

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

В качестве исходной таблицы возьмем таблицу с двумя столбцами (полями): текстовым «Фрукты» и числовым «Количество на складе» (См. файл примера ).

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

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

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

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

Читать еще: Excel функция если с несколькими условиями

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

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

Результатом вычисления A2_A13=D2 является массив <ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики. Массив можно увидеть, выделив в Строке формул A2_A13=D2 , а затем нажав F9;
Результатом вычисления B2:B13 является массив<3:5:11:98:4:8:56:2:4:6:10:11>, т.е. просто значения из столбца B;
Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является <0:0:0:0:4:8:56:0:0:0:0:0>. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
Разберем второе условие: Результатом вычисления —( B2:B13>=E2) является массив <0:0:1:1:0:1:1:0:0:1:1:1>. Значения в столбце «Количество ящиков на складе», которые удовлетворяют критерию >=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)

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

Другой задачей может быть нахождение сумм ящиков только тех партий товаров, у которых количество ящиков попадает в определенный интервал, например от 5 до 20 (см. файл примера Лист 2Числа).

Формулы строятся аналогично задаче 1: =СУММЕСЛИМН(B2:B13;B2:B13;»>=»&D2;B2:B13;» =$D$2;$B2 =»&D6;A6:A17;» =15.01.2010″;A6:A17;» Похожие задачи

СУММЕСЛИМН (функция СУММЕСЛИМН)

Функция СУММЕСЛИМН — одна из математических и тригонометрических функций, которая суммирует все аргументы, удовлетворяющие нескольким условиям. Например, с помощью функции СУММЕСЛИМН можно найти число всех розничных продавцов, (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; «Артем»)

Суммирует количество продуктов, названия которых начинаются с Я и которые были проданы продавцом Артем. Подстановочный знак (*) в аргументе Условие1 («=Я*») используется для поиска соответствующих названий продуктов в диапазоне ячеек, заданных аргументом Диапазон_условия1 (B2:B9). Кроме того, функция выполняет поиск имени «Артем» в диапазоне ячеек, заданных аргументом Диапазон_условия2 (C2:C9). Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 20.

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

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

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

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

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

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

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

Для наглядности я перенес данную таблицу на один лист с исходными данными.

Синтаксис функции СУММЕСЛИМН:

СУММЕСЛИМН( диапазон_суммирования ; диапазон_условий1 ; условия1 ;[ диапазон_условий2 ; условия2 ];. )
диапазон_суммирования — В нашем случае нам необходимо просуммировать количество подключенных услуг, поэтому это столбец Количество и диапазон Е2:E646

Далее указываются условия по которым необходимо просуммировать услуги. У нас три условия:

должна совпадать фамилия сотрудника;
должна совпадать услуга;
должен совпадать город.

диапазон_условий1 — первое условие у нас сотрудники и диапазон условий это столбец с именами ФИО сотрудников A2:A646

условия1 — это сам сотрудник, так как мы начинаем прописывать формулу напротив сотрудника Апанасенко Е.П то и условия1 у нас будет ссылка на его ячейку G3

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

Продолжим, следующая условие это услуга

диапазон_условий2 — это столбец с услугами D2:D646

условия2 — это ссылка на услугу 1, то есть H2

Вот как должна выглядеть наша формула на текущий момент:

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2

Добавляем третье условие по городам

диапазон_условий3 — диапазон условий по городам это столбец «Город клиента» и диапазон B2:B646

условия3 — это ссылка на город в раскрывающемся списке G1

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

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2; B2:B646; G1 )

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

Во-первых все диапазоны условий у нас не двигаются и постоянны поэтому закрепим их с помощью знака доллара (выделить данный диапазон в формуле и нажать клавишу F4):

Диапазон суммирования у нас так же постоянный E2:E646 → $E$2:$E$646

Так же условия3 по городу G1 y нас всегда находится только в ячейке G1 и не должен смещаться при протягивании, поэтому так же закрепляем данную ячейку

G1 → $G$1

Услуги (условия2) при протягивании вправо должны меняться по столбцам, а вот строка при протягивании вниз не должна меняться, поэтому закрепляем только строку

H2 → H$2

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

G3 → $G3

Итоговая формула будет выглядеть следующим образом

=СУММЕСЛИМН( $E$2:$E$646 ; $A$2:$A$646 ; $G3 ; $D$2:$D$646 ; H$2; $B$2:$B$646 ;$ G$1 )

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

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

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

Синтаксис СУММЕСЛИМН и распространенные ошибки

Аргументы функции СУММЕСЛИМН:

Диапазон ячеек для нахождения суммы. Обязательный аргумент, где указаны данные для суммирования.
Диапазон ячеек для проверки условия 1. Обязательный аргумент, к которому применяется заданное условие поиска. Найденные в этом массиве данные суммируются в пределах диапазона для суммирования (первого аргумента).
Условие 1. Обязательный аргумент, составляющий пару предыдущему. Критерий, по которому определяются ячейки для суммирования в диапазоне условия 1. Условие может иметь числовой формат, текстовый; «воспринимает» математические операторы. Например, 45; « , = и др.).

Примеры функции СУММЕСЛИМН в Excel

У нас есть таблица с данными об оказанных услугах клиентам из разных городов с номерами договоров.

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

Как использовать функцию СУММЕСЛИМН в Excel:

Вызываем «Мастер функций». В категории «Математические» находим СУММЕСЛИМН. Можно поставить в ячейке знак «равно» и начать вводить название функции. Excel покажет список функций, которые имеют в названии такое начало. Выбираем необходимую двойным щелчком мыши или просто смещаем курсор стрелкой на клавиатуре вниз по списку и жмем клавишу TAB.
В нашем примере диапазон суммирования – это диапазон ячеек с количеством оказанных услуг. В качестве первого аргумента выбираем столбец «Количество» (Е2:Е11). Название столбца не нужно включать.
Первое условие, которое нужно соблюсти при нахождении суммы, – определенный город. Диапазон ячеек для проверки условия 1 – столбец с названиями городов (С2:С11). Условие 1 – это название города, для которого необходимо просуммировать услуги. Допустим, «Кемерово». Условие 1 – ссылка на ячейку с названием города (С3).
Для учета вида услуг задаем второй диапазон условий – столбец «Услуга» (D2:D11). Условие 2 – это ссылка на определенную услугу. В частности, услугу 2 (D5).
Вот так выглядит формула с двумя условиями для суммирования: =СУММЕСЛИМН(E2:E11;C2:C11;C3;D2:D11;D5).

Результат расчета – 68.

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

Теперь можно посмотреть, сколько услуг 2 оказано в том или ином городе (а не только в Кемерово). Формулу немного видоизменим: =СУММЕСЛИМН($E$2:$E$11;$C$2:$C$11;F$2;$D$2:$D$11;$D$5).

Все диапазоны для суммирования и проверки условий нужно закрепить (кнопка F4). Условие 1 – название города – ссылка на первую ячейку выпадающего списка. Ссылку на условие 2 тоже делаем постоянной. Для проверки из списка городов выберем «Кемерово»:

Результат тот же – 68.

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