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

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

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

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

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

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

Синтаксис

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

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

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

  • Условие   .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Можно включит под вопросицу (?), которая соответствует любому одиночному символу, звездочка (*) — любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак “тильда” (~).

    Например, условия могут быть выражены как 32, “>32”, B5, “3?”, “яблок*”, “*~?”, или СЕГОДНЯ().

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

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

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

    диапазон

    Диапазон_суммирования.

    Фактические суммные ячейки

    A1:A5

    B1:B5

    B1:B5

    A1:A5

    B1:K5

    B1:B5

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

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

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

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

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

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

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

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

Рис 1

.

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

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

Рис 2

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

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

Рис. 3

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

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

Рис. 4

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

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

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

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

Рис. 5

Общие сведения о функции СУММЕСЛИ

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

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

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

Синтаксис

=SUMIF(range, criteria [sum_range]) – английская версия

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

Примеры

Пример 1

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

Стоимость имущества

Комиссионные

Данные

1 000 000 ₽

70 000 ₽

2 500 000 ₽

2 000 000 ₽

140 000 ₽

3 000 000 ₽

210 000 ₽

4 000 000 ₽

280 000 ₽

Формула

Описание

Результат

=СУММЕСЛИ(A2:A5;”>160000″;B2:B5)

Сумма комиссионных за имущество стоимостью больше 1 600 000 ₽.

630 000 ₽

=СУММЕСЛИ(A2:A5; “>160000”)

Сумма по имуществу стоимостью больше 1 600 000 ₽.

9 000 000 ₽

=СУММЕСЛИ(A2:A5;300000;B2:B5)

Сумма комиссионных за имущество стоимостью 3 000 000 ₽.

210 000 ₽

=СУММЕСЛИ(A2:A5;”>” &C2;B2:B5)

Сумма комиссионных за имущество, стоимость которого превышает значение в ячейке C2.

490 000 ₽

Пример 2

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

Категория

Продукты

Объем продаж

Овощи

Помидоры

23 000 ₽

Овощи

Сельдерей

55 000 ₽

Фрукты

Апельсины

8 000 ₽

Масло

4 000 ₽

Овощи

Морковь

42 000 ₽

Фрукты

Яблоки

12 000 ₽

Формула

Описание

Результат

=СУММЕСЛИ(A2:A7;”Фрукты”;C2:C7)

Объем продаж всех продуктов категории “Фрукты”.

20 000 ₽

=СУММЕСЛИ(A2:A7;”Овощи”;C2:C7)

Объем продаж всех продуктов категории “Овощи”.

120 000 ₽

=СУММЕСЛИ(B2:B7;”*ы”;C2:C7)

Объем продаж всех продуктов, названия которых заканчиваются на “ы” (“Помидоры” и “Апельсины”).

43 000 ₽

=СУММЕСЛИ(A2:A7;””;C2:C7)

Объем продаж всех продуктов, категория для которых не указана.

4 000 ₽

К началу страницы

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

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

sum-33.png

Формулы строятся аналогично задаче 1: =СУММЕСЛИМН(B2:B13;B2:B13;”>=”&D2;B2:B13;”

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

Сумма, если больше, Сумма, если меньше. СУММЕСЛИ.

Знаки, которые используются: >, <.

У нас есть 25 строк с названием Пример и номер примера, в таблице. Напротив каждого примера стоит значение (Рис. 6).

Сумма, если больше, Сумма, если меньше.

Рис. 6

Сумма, если больше.

Формула в ячейке, как пример, выглядит вот так:

=СУММЕСЛИ(Е3;Е27; «>500»)

Напротив каждого примера стоит число от 100 до 1300. Нам нужно просуммировать, только значения, которые больше числа 500.

Вызываем функцию, как описано выше и выбираем нужный нам диапазон в строке – Диапазон (Е3;Е27). Это столбец со значениями чисел от 100 до 1300.

Вводим критерий. В строке Критерий, пишем: >500, вручную. Можно скопировать значение критерия с самой таблицы и добавить в ручную только знак >. Кавычки появятся автоматически (Рис. 7).

Сумма, если больше.

Рис. 7

Нажимаем ОК.  Напротив ячейки с словом ИТОГО, появилась сумма чисел больше 500 (Рис. 8)

Сумма, если больше.

Рис. 8

Сумма, если меньше.

Формула в ячейке, как пример, выглядит вот так:

=СУММЕСЛИ(Е3;Е27; «<500»)

Допустим, нам нужно найти сумму в диапазоне значений (E3:E27) меньше числа 500.

Алгоритм точно такой же. В критерии указывает знак меньше <, и необходимое значение. В нашем примере 500.

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

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

См. также

Функция СУММЕСЛИМН суммирует все аргументы, которые соответствуют нескольким условиям

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

Функция СЧЁТЕСЛИ подсчитывает только те значения, которые соответствуют одному условию

Функция СЧЁТЕСЛИМН подсчитывает только те значения, которые соответствуют нескольким условиям

Функция ЕСЛИМН (Office 365, Excel 2016 и более поздние версии)

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Математические и тригонометрические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

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

Функция SUMIF (СУММЕСЛИ) в Excel

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

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...