Примеры функции суммесли и суммеслимн в excel

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

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

Щелкните ячейку, в которой вы хотите получить результат. Тип, в камере.

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

Введите ячейку C3: C10 или щелкните C3 и нажмите Ctrl, Сдвиг, и Клавиша со стрелкой вниз чтобы выделить до C10, затем добавьте запятую.

Добавить Диапазон критериев 1. Щелкните первые данные в столбце фруктов и введите ячейки B3: B10 или же Ctrl, Сдвиг и Клавиша со стрелкой вниз чтобы выделить ячейки.

Введите Mango как Критерии.

Теперь я добавлю Второй диапазон критериев. Перейти к Покупатель столбец и щелкните первые данные в таблице и Ctrl, Сдвиг, и Клавиша со стрелкой вниз выделить или напечатать A3: A10, запятая.

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

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

в Sum_range поле ввода, тип потому что это количество продаж, которое мы хотим сложить.

в Criteria_ range тип поля ввода потому что это диапазон, в котором находятся фрукты, которые мы ищем.

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

в Criteria_ Range 2 тип поля ввода , потому что это второй диапазон, который вы ищите, его зовут Кевин Сахадео.

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

Затем нажмите ОК. Вы увидите результат.

Надеюсь, этот пост окажется для вас полезным.

Читать дальше: Как создать сводную таблицу и сводную диаграмму в Excel.

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

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

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

Объявите функцию СУММЕСЛИМН и сначала запишите тот диапазон, который будете считать. В моем случае это количество груш.
Далее нужно вычленить из списка продукции только груши, для чего используйте критерий текста точно так же, как это уже было показано в предыдущем примере.
Второй критерий – цена, которая должна превышать 10 за единицу. Соответственно, впишите блок с неравенством A1:A10;»>10″, где A1:A10 – диапазон ячеек, а >10 – критерий.
Нажмите клавишу Enter и ознакомьтесь с результатом. На следующем скриншоте вы видите, что груш с ценой больше 10 довольно много, все их количество суммировалось и отображается в отдельной ячейке.

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

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

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

В рассмотренном выше примере нужно отыскать сумму по заказам, реализованным менеджером Воробьевым И. И. заказчику ИП Васькову А. П. Таковым образом, критериев отбора два: сотрудник и заказчик. Как и в прошлом примере, ставим курсор в ячейку, где будет итог. «Формулы»-«Математические»-СУММЕСЛИмн. Возникает окно «Аргументы». Заполняется оно несколько непревзойденно от предшествующего примера.

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

Функция СУММЕСЛИ в Excel: объяснение

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

Если у вас все еще есть проблемы, есть много хороших примеров на официальном веб- сайте Microsoft или просто просмотрите этот простой пример SUMIF в Excel ниже.

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

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

Задача3 (2 критерия Дата)

Другой задачей может быть нахождение суммарных продаж за период (см. файл примера Лист “2 Даты” ). Используем другую исходную таблицу со столбцами Дата продажи и Объем продаж .

Формулы строятся аналогично задаче 2: = СУММЕСЛИМН(B6:B17;A6:A17;”>=”&D6;A6:A17;”

Примечание . Даты могут быть представлены в числовой форме см. статью Как Excel хранит дату и время

При необходимости даты могут быть введены непосредственно в формулу = СУММЕСЛИМН(B6:B17;A6:A17;”>=15.01.2010″;A6:A17;”

Чтобы вывести условия отбора в текстовой строке используейте формулу =”Объем продаж за период с “&ТЕКСТ(D6;”дд.ММ.гг”)&” по “&ТЕКСТ(E6;”дд.ММ.гг”)

В последней формуле использован Пользовательский формат .

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

Разглядим все вероятные ситуации внедрения одноименной функции.

Вид

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

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

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

C3:C12 – тип одежки;
«одежка» – аспект;
F3:F12 – интервал суммирования.

С несколькими условиями

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

F3:F12 – спектр суммирования;
«одежка» и 50 – аспекты;
C3:C12 и E3:E12 – спектры типов продукта и цены единицы соответственно.

Внимание! В функции СУММЕСЛИМН аргумент спектра суммирования стоит в начале формулы. Будьте внимательны!

С динамическим условием

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

Для вставки новейшей строчки необходимо надавить ПКМ на интересующей ячейке и избрать «Вставить» – «Строчку».

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

На этом я заканчиваю. Вы узрели главные примеры использования функции СУММЕСЛИ в Excel. Если есть какие-то советы либо вопросцы – милости прошу в комменты.

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

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

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

=СУММЕСЛИМН( диапазон суммирования; диапазон проверки на соответствие первому критерию ( то есть первому условию); первый критерий ( условие, которому должна соответствовать ячейка в диапазоне проверки первого критерия); диапазон проверки на соответствие второму критерию ( второму условию); второй критерий ( второе условие)… и так до 127 диапазонов проверки критериев и самих критериев).

Приведем пример работы СУММЕСЛИМН. Предположим следующее. Наименования товаров заданы в диапазоне B3:B50, количество упаковок каждого товара находится в графе С3:С50, а в диапазоне D3:D50 указаны соответствующие заданным позициям товара расценки. Наша задача – найти общее количество упаковок рубашек с ценой ниже 3000. Получаем следующую задачу:

«Найти общую сумму в диапазоне С3:С50, но при этом в диапазоне B3:B50 должно содержаться слово «рубашка», а в диапазоне D3:D50 значение должно быть меньше 3000». Итоговая формула будет выглядеть следующим образом.

=СУММЕСЛИМН(C3:C50;B3:B50;”рубашка”;D3:D50;”<3000″)

Рисунок 12

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

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

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

Почему функция СУММЕСЛИ может не работать?

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

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

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

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

Предварительно давайте разберемся что деле он очень каждая функция имеет логическом языке означает Помимо самой функции, условий, например, функция 45, то возвращает функциями Excel, можно на вкладке «Формулы», был истинным, формулазначение_если_истина хороши тем, чтоПолучилось 92900. Т.е. функция знаете, как использовать клиент» выдавалось лишь соединить с операторами Допустим, мы хотим это такое. Вложенной простой и запомнить

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

свою пару скобок, «ЛОЖЬ». Если оставить внутри «ЕСЛИ» могутВПР строку «Сдал», иначе решать достаточно сложные вы увидите, как возвращает значение ИСТИНА.(обязательно)

автоматически проработала список оператор в Excel, тогда, когда были

сравнения. К ним называют функцию, в все переменные не и случайно поставив
пустым одно изили «Не сдал». задачи. В этом Excel вычисляет формулу.=ЕСЛИ(ИЛИ(A5=»красный»;B5=»зеленый»);ИСТИНА;ЛОЖЬ)Значение, которое должно возвращаться,
под изменение условий. если несколько условий выполнены оба заданных относятся параметры: «ИЛИ», именно. В этом которой прописано несколько
составит труда: значений, отвечающих за Но в первомПРОСМОТР уроке мы попробуем

=ЕСЛИ(НЕ(A5>B2);ИСТИНА;ЛОЖЬ)Если значение A5 («синий») если лог_выражение имеет Т.е. мы можем них только продавцов

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

Теперь же давайте разберем долго искать ошибку. истины или лжи, может располагаться вИтак, в этом уроке увидеть, что 2 на простых примерах, B2, возвращается значение значение B5 («зеленый»)значение_если_ложь ячейках, и суммы зарплаты.Суммировать в программе Excel

Пример вложенной функции ЕСЛИ в Excel

«Если» используется, чтобы если оценка учащегося=ЕСЛИ(ИЛИ(A1=B1;B1=C1;A1=C1);есть равные значения;нет равных прописано сразу несколько все вышеизложенное поДля закрепления понимания стоит то при его любом из трёх мы рассмотрели логическую человека из 5 а также научимся ИСТИНА, в противном

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

на практике рассмотреть, выборе результатом будет частей синтаксической конструкции. функцию не прошли переаттестацию. использовать сразу несколько случае возвращается значение значение ИСТИНА, вЗначение, которое должно возвращаться, с ними. Например, всех менеджеров, продавцов-кассиров Но с усовершенствованной при делении на 5, но большеТо есть мы указали, Рассмотрим все на» как работает функция «0».

При работе со сложнымиЕСЛИФункции

ЛОЖЬ. В этом противном случае возвращается если лог_выражение имеет при подсчете заработных и охранников. Когда

версией команды СУММ, ноль, а также 3. Должен быть что если в примере.=ЕСЛИ «ЕСЛИ» в Excel.Отдельно стоит отметить случай, задачами, используется функцияво всей ееЕСЛИЕСЛИ случае A5 больше значение ЛОЖЬ. В значение ЛОЖЬ. плат оказалось, что табличка небольшая, кажется, которая называется СУММЕСЛИ, еще в нескольких отображен комментарий: «проходит». минимум двух ячейкахДопустим, что у нас

» — название самой Примеры, приведённые ниже, когда вместо логического «ЕСЛИ» с несколькими красе и примерах,можно вкладывать другв одной формуле. B2, поэтому формула этом случае второй

Расширение функционала функции ЕСЛИ

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

Функция возвращает значение ЛОЖЬ. аргумент имеет значение этих функций по одну сотрудницу, которая сосчитать и вручную, данной операции. обозначается редактором, как – «нет». Итак, одинаковые значения, то

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

Заключение

но при работеПо названию команды можно «ДЕЛ/0» и встречается проходят лишь те формула должна вывести студентов и их»Простейшим примером для разбора «ИСТИНА» или «ЛОЖЬ», большинства пользователей возникает использованием сразу нескольких принятия решений вимеет всего три

операторы И, ИЛИ возвращает значение ИСТИНА.

следующих статьях: И,

fb.ru⁪>

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

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

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

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

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

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

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

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

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

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

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

Обратите внимание на запятую между аргументами:. =СУММЕСЛИМН(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. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.

Выборочное суммирование

Как в экселе посчитать сумму выборочных ячеек, решает функция «СУММ» раздела «Формулы» панели управлений.

Все операции выполняются наведением курсора на разделы панели управления и ячейки с данными, щелканьем («клик») левой кнопкой мыши, в следующей последовательности:

любая пустая ячейка;
раздел «ФОРМУЛЫ» панели управлений;
раздел «Вставить функцию»;
раздел «Категория», выбрать «Математические»;
раздел «Выберите функцию» выбрать «СУММ», перейти к разделу «Аргументы функции»;
ячейка таблицы с первым слагаемым;
раздел «Аргументы функции», строка «Число 2»;
ячейка таблицы со вторым слагаемым.

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

Программа позволяет находить более сложные суммы данных, объединенных между собой условием (СУММ ЕСЛИ).

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

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

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

Как в Excel посчитать сумму в столбце: все способы

Шаг 3 — Примените формулу ко всему столбцу. Вы можете использовать маркер Fill, чтобы выбрать и перетащить его, или просто скопировать и вставить ячейку C3 во все оставшиеся ячейки (что автоматически настроит ссылку и даст правильный результат).

СУММЕСЛИ или СУММЕСЛИМН в Excel — что лучше использовать?

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

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

Но обратите внимание:

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

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

Почему СУММЕСЛИМН удобнее при суммировании по одному критерию?

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

Поэтому совет практика: использовать только функцию СУММЕСЛИМН.

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

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

Сообщество Excel Analytics | обучение Excel

Описание функции

Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазона, соответствующие указанному критерию. Например, в столбце с числами необходимо просуммировать только значения, превышающие число 5. Для этого можно использовать указанную ниже формулу: =СУММЕСЛИ(B2:B25,»>5″)
В данном примере на соответствие критерию проверяются суммируемые значения. При необходимости условие можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула: =СУММЕСЛИ(B2:B5; «Иван»; C2:C5)
суммирует только те значения из диапазона C2:C5
, для которых соответствующие значения из диапазона B2:B5
равны “Иван”.
Еще один пример использования функции представлен на рисунке ниже:

Пример использования СУММЕСЛИ для подсчета прибыли с условием

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

Функция СУММЕСЛИ популярна, поскольку почти в каждой таблице нужно посчитать сумму чисел в ячейках, игнорируя значения, не попадающие под основное условие. Благодаря этой формуле подсчет не становится чем-то сложным и длительным. Стандартное оформление функции выглядит как =СУММЕСЛИ(Диапазон;Критерий;Диапазон_суммирования), а «Диапазон суммирования» указывается только при условии, когда есть фактические ячейки, сложение которых выполняется при любых обстоятельствах. Если данные в «Диапазоне суммирования» отсутствуют, проверяться будут все ячейки, входящие в «Диапазон».

«Диапазон»«Критерий»A1:A100«Критерий»

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

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

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

Возможные варианты данной функции:

сумма, если больше;
сумма, если меньше;
сумма, если равно;
сумма, если не равно;
сумма если больше или равно;
сумма если меньше или равно.

Отдельная статья описывает функцию СУММЕСЛИ Excel. Точное совпадение. Частичное совпадение. С текстовыми критерий. Содержит описание, примеры и иллюстрации. Статья находиться по этой ссылке: Функция СУММЕСЛИ в MS Excel. Точное совпадение. Частичное совпадение. Инструкция и примеры.

Самый простой и понятный способ вызвать данную функцию, это нажать в правом верхнем углу, рядом с кнопкой ∑, стрелку вниз (Рис. 1).

Рис 1

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

Рис 2

Появляется диалоговое окно – Вставка функции. Самое верхнее, это поле для поиска нужной функции. Вводим название функции, выбираем ее в результатах поиска и нажимаем ОК (Рис. 3).

Рис. 3

Появляется диалоговое окно — Аргументы функции (Рис. 4).

Рис. 4

В нем есть три ключевых поля для заполнения:

Диапазон — здесь указываться диапазон ячеек, в которых будет происходить поиск по указанному критерию;
Критерий — здесь будет указан сам критерий поиска;
Диапазон _ суммирования, здесь указывается диапазон ячеек, данные в которых будут суммироваться, исходя из критерия поиска. Применяется не во всех случаях.

Что бы выбрать нужный диапазон нажимаете на стрелочку в конце строки диапазона и выбираете нужные Вам ячейки с помощью мыши. Можно сразу выбрать нужный диапазон, не нажимая на стрелочку, но сначала нужно поставив курсор на нужную строку в диалоговом окне. Можно прописать диапазон вручную. Результат от этого не зависит. После того как все готово, нажимает ОК (Рис. 5).

Рис. 5

Синтаксис

Аргументы

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

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

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

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

Замечания

В аргументе условие можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует одному любому символу, а звездочка — любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (~).
Функция СУММЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов со строкой #ЗНАЧ!.
Аргумент диапазон_суммирования может не совпадать по размерам с аргументом диапазон. При определении фактических ячеек, подлежащих суммированию, в качестве начальной используется верхняя левая ячейка аргумента диапазон_суммирования, а затем суммируются ячейки части диапазона, соответствующей по размерам аргументу диапазон.

Пример формулы СУММЕСЛИ и ВПР

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

Нам необходимо найти объем выручки по конкретному товару. В этом примере у нас сложность в том, что вторая таблица содержит сразу несколько позиций выручки по одному коду и добавить столбец с именами ко второй табличке не является возможным. Пусть результатом нашей работы будет объем выручки по товару «Порошок». Определим первый аргумент для функции СУММЕСЛИ. Поскольку мы ищем по коду товара, указываем диапазон из второй таблицы «D130:D142». Вторым аргументом у нас будет функция ВПР, которая задаст условие. И третьим аргументом будет диапазон, по котором происходит суммирование – Е130:Е142:

Теперь разберем формулу ВПР. Ее синтаксис выглядит следующим образом: (искомое значение; диапазон поиска; номер столбца в диапазоне с возвращаемым значением; возврат приблизительного или точного совпадения). Первым аргументом будет ячейка Е147, в которой содержится значение продукта. Вторым аргументом будет первая таблица А130:В137. Третьим аргументом будет номер столбца, по которому будет возвращаться значение. Также укажем четвертый аргумент «ЛОЖЬ» для точного совпадения:

Теперь складываем наши формулы в одну и получаем сумму выручки по продукту «Порошок»:

Функция ВПР извлекла из таблицы значение d-02, которое соответствовало искомому «Порошку». Затем СУММЕСЛИ суммировала все значения из столбца «Выручка», которые соответствовали коду «d-02», поскольку это значение было условием для суммирования.