Как найти суммесли в excel

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

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

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

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

Функция СУММЕСЛИ позволяет суммировать ячейки, которые удовлетворяют определенному критерию (заданному условию). Аргументы команды следующие:

1. Диапазон – ячейки, которые следует оценить на основании критерия (заданного условия).
2. Критерий – определяет, какие ячейки из диапазона будут выбраны (записывается в кавычках).
3. Диапазон суммирования – фактические ячейки, которые необходимо просуммировать, если они удовлетворяют критерию.

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

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

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

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

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

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

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

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

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

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

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

Аргументов у СУММЕСЛИМН может быть сколько угодно, но минимум – это 5.

1. Диапазон суммирования. Если в СУММЕСЛИ он был в конце, то здесь он стоит на первом месте. Он также означает ячейки, которые необходимо просуммировать.
2. Диапазон условия 1 – ячейки, которые нужно оценить на основании первого критерия.
3. Условие 1 – определяет ячейки, которые функция выделит из первого диапазона условия.
4. Диапазон условия 2 – ячейки, которые следует оценить на основании второго критерия.
5. Условие 2 – определяет ячейки, которые функция выделит из второго диапазона условия.

И так далее. В зависимости от количества критериев, число аргументов может увеличиваться в арифметической прогрессии с шагом 2. Т.е. 5, 7, 9.

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

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

Значит, будем применять команду СУММЕСЛИМН.

• диапазон суммирования – ячейки с зарплатой;
• диапазон условия 1 – ячейки с указанием должности сотрудника;
• условия 1 – продавец;
• диапазон условия 2 – ячейки с указанием пола сотрудника;
• условие 2 – женский (ж).

Итог: все продавцы-женщины в январе получили в сумме 51100 рублей.

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

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

У нас появилась дополнительная строчка. Сразу обращаем внимание, что диапазон условий и суммирования автоматически расширился до 15 строки.

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Функция СУММЕСЛИ в Excel за пару минут

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

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

1. Уточнение функций ячеек. По выбранным ячейкам будет осуществляться соответствие заданным параметрам расчета или выполнения арифметических действий.
2. Параметр «условие». Для этого необходимо использовать функционал «кавычки». В данном случае будет учитываться требуемый критерий, где необходимо выполнить для «условия» арифметическое действие суммирования.
3. Функции Excel арифметического действия «суммирования». Если потребуется выполнить необходимое вычисление метаданных для указанного диапазона ячеек в Excel.

Определяем искомые аргументы применения табличной части для опции СУММЕСЛИ в Excel.

1. Для вычисления Excel функции поиска используем должности наших сотрудников, но при этом заголовок не будет входит в расчётную базу опции.
2. Определяем искомый параметр метаданных, в нашем случае это «продавец». Обращаем внимание, что этот искомый аргумент мы должны заключить в «кавычки».
3. Диапазон суммирования выбирает данные по аргументу «продавец», и ведет расчет в ячейках от F2 до F14.

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

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

Пример
Возьмем нашу таблицу с заработной платой.

Хочешь построить карьеру мечты?
Подпишись и получай подборку лучших вакансий на рынке и полезные материалы от ведущих HR

Подписаться

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel Starter 2010 Еще. Меньше

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

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

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

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

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

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

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

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

Необходимые действия

Использование подстановочных знаков

Подстановочные знаки, такие как вопросительный знак (?) или звездочка (*), в аргументах Условие1, 2 можно использовать для поиска сходных, но не совпадающих значений.

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

Например, формула =СУММЕСЛИМН(A2:A9; B2:B9; «=Я*»; C2:C9; «Арте?») будет суммировать все значения с именем, начинающимся на «Арте» и оканчивающимся любой буквой.

Различия между функциями СУММЕСЛИ и СУММЕСЛИМН

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

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

Одинаковое количество строк и столбцов для аргументов, задающих диапазоны ячеек

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

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

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

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

=СУММЕСС(D2:D11,A2:A11,»Южный»,C2:C11,»Мясо»)

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

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

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

=СУММЕСЛИМН(D2:D11,

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

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

=СУММЕСЛИМН(D2:D11;A2:A11;»Южный»;

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

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

=СУММЕСЛИМН(D2:D11;A2:A11,»Южный»;C2:C11,»Мясо»)

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

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

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

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

Попробуйте попрактиковаться

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

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

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

Что следует ввести