Формула суммесли в excel для чайников

Работа во многих профессиональных областях на сегодняшний день не обходится без использования программы «Эксель». Эта программа, как любое орудие труда, становится полезной только в умелых руках. Для успешной деятельности в сфере экономики, финансирования и многих других необходимы базовые знания по Excel. На необъятных просторах интернета представители профессий, относящихся к вышеуказанным областям, сформировали список функций и возможностей, остро необходимых для решения профессиональных задач. В этот список, помимо умения работы со сводными таблицами, вошли функции:

  • СУММЕСЛИ.
  • ЕСЛИ.
  • СУММПРОИЗВ.
  • ВПР.
  • СУММЕСЛИМН.

Следовательно, функция СУММЕСЛИ в «Эксель» является одной из самых востребованных.

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

Функция СУММЕСЛИ: диалоговое окно аргументов функции

Если при анализе таблицы данных в «Эксель» задача стоит в необходимости суммирования показателей, которые соответствуют определенным условиям, значит, пришло время применения функции СУММЕСЛИ. Курсор мыши ставится в ячейку, куда нужно вывести результат. Далее либо функция «Эксель» СУММЕСЛИ выбирается из ленты (Формула – математические – СУММЕСЛИ), либо вводится через горячую клавишу =, которая означает готовность ввести в ячейку формулу или функцию. Если после знака = начать вводить первые буквы названия функции, «Эксель» сам выдаст несколько подходящих, остается только выбрать нужную. Опытные пользователи прописывают формулу СУММЕСЛИ в «Эксель» прямо в строке формул, вписывая аргументы через точку с запятой. Новичку же лучше воспользоваться значком fx, который находится слева от строки формул. Появляется окно «Аргументы функции». Грамотное заполнение всех окон – залог успешной работы с функцией СУММЕСЛИ в «Эксель».

Обычно три поля:

  1. Диапазон – совокупность ячеек, которые программа и будет сравнивать на соответствие заданному условию. Курсор ставится в поле аргумента, и мышью выбираются нужные ячейки.
  2. Критерий – условие, при котором будет производится суммирование. Здесь могут быть числа, выражения, текст, даты. Обычно значение критерия вводится в кавычках.
  3. Диапазон суммирования – ячейки со значениями, которые войдут в итоговую сумму, если соответствующая ячейка в диапазоне соответствует критерию. Курсор ставится в поле аргумента, и мышью выбираются нужные ячейки.

Могут заполняться 2 поля – диапазон и критерий, такое происходит, если первый совпадает с диапазоном суммирования. В диалоговом окне аргументы функции, в самой нижней строке показывается получившееся значение. Если с диапазонами все просто: нужно просто обозначить область анализируемых ячеек, то для заполнения поля «Критерий» нужно знать некоторые тонкости и особенности программы.

Критерии для функции СУММЕСЛИ в «Эксель»

Критериями для функции могут служить:

  1. Логические выражения.
  2. Текст.
  3. Точные даты.
  4. Временной интервал.

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

image

Логическое значение в качестве критерия

Самый частый вариант использования функции СУММЕСЛИ в «Эксель» – это использование логического значения (больше, меньше, равно) в качестве условия. Для вышеуказанной таблицы сумма заказов, равных 2000 рублей, опишется так.

image

Как видно из примера, диапазон и диапазон суммирования совпадают, заполняется только первая строка. Условие прописывается в кавычках (это справедливо всегда для этого поля!). Сразу видно вычисленное значение: 4000. Возвращаемся к начальной таблице: действительно, присутствуют два заказа в сумме 2000 рублей. Вместе они составляют 4000 рублей. Не используя диалоговое окно, можно записать в строке формул: =СУММЕСЛИ(E2:E15;»=2000″), результат будет тем же.

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

  1. «>2000» – функция посчитает сумму всех заказов больше 2000 рублей.
  2. «<2000» – вернется сумма заказов меньше 2000 рублей</li>
  3. «>=2000» – вернется сумма заказов которые больше и равны 2000 рублей.
  4. «<=2000» – сумма заказов меньше и равных 2000 рублям.</li>

Текст в качестве критерия

Пример СУММЕСЛИ в «Эксель» текстовым критерием: подсчитать, на какую сумму продал заказов менеджер Иванов И. И. Так выглядит окно аргументов в этом случае.

Функция вернула значение 13000 рублей. На эту сумму продал товаров менеджер Иванов И. И.

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

В поле критерий будет запись «*миксер*».

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

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

Дата и диапазон дат в качестве критерия

Если в функции СУММЕСЛИ в «Эксель» в качестве критерия для условия должна использоваться конкретная дата, то к оформлению поля применяются те же критерии, что и в случае с числами и с текстом. Тем же знакам =,>,<,<=,>=,<>, соответствуют те же значения, только применимо это к датам. Например, в исходной таблице необходимо суммировать заказы за первый квартал 2018 года. В качестве диапазона выбирается дата продажи, критерий, основываясь на задании, выглядит как «»>=31.03.2018″, в итоге получится следующее.

Значит, 19600 рублей – объем продаж по названной номенклатуре за первый квартал.

Ссылка в поле критерия

Часто итоговый результат от вычислений по СУММЕСЛИ в «Эксель» нужно сделать динамичным, зависящим от изменений в ячейках, входящих в аргументы функции. Если с первым и третьим аргументом все ясно – они являются ссылками изначально, то в поле «Критерий» для того, чтобы ввести ссылку, нужно воспользоваться дополнительным символом – значком амперсанд (&). Знаки для логического выражения необходимо, как и прежде, взять в кавычки, а перед ссылкой поставить &.

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

Аналогично ссылки можно вставить в любое выражение в поле «Критерий» в аргументах функции СУММЕСЛИ в «Эксель».

Рассмотренные выше примеры подразумевают получение суммы по одному условию. Выполнения нескольких условий формулой СУММЕСЛИ в «Эксель» не добиться. Для реализации этой задачи используется более расширенная функция – СУММЕСЛИМН, введенная в программу, начиная с версии 2007 года.

Функция СУММЕСЛИ. Первый из трех китов Excel

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

Вам приходилось суммировать данные по сотрудникам или клиентам из большой таблицы, выбирать сколько выручки было по той или иной номенклатуре? Вы фильтровали по фамилиям/позициям, а затем вносили числа руками в отдельные ячейки? Может быть считали на калькуляторе? А если строк больше тысячи? Как посчитать быстро?

Вот тут пригодится СУММЕСЛИ!

Задача1. Существует статистика по товарам, городам и какие показатели были достигнуты по этим позициям. Необходимо рассчитать: «на какую сумму продано номенклатуры Товар1?»

Если Вы хотите, чтобы функция выглядела как на следующей картинке, т.е. имела красивые диапазоны, что позволит удобно использовать их по всей книге и не использовать $ для закрепления ссылок. Читайте статья Как сделать красивые диапазоны.

Как работает СУММЕСЛИМН?

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

Чтобы лучше запомнить порядок аргументов этой функции, посмотрите на рисунок:

Бонус

И не один, а целых два!

Во-первых, в Excel 2007 и более поздних версиях существует небольшое семейство подобных функций, включающее в себя СРЗНАЧЕСЛИМН (для вычисления средних значений) и СЧЁТЕСЛИМН (для подсчета количества значений).

Во-вторых, функции этого семейства могут работать со операторами отношения (>, <, = и т.п.) и знаками подстановки, такими как «*» — любое количество любых символов, и «?» — один любой символ. Так, например, чтобы подсчитать количество самолетов, проданных в Северной и Южной Америке, можно воспользоваться такой формулой:</p>

Скачать файл с исходными данными

русском английском
СУММЕСЛИ SUMIF

Описание

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

=СУММЕСЛИ(B2:B25,">5")

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

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

Дополнительная информация (источник)

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

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

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

диапазон условие диапазон_суммирования

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

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

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

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

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

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

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

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

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

Условие — критерий в форме числа, выражения или текста, определяющий, какие ячейки должны суммироваться. Например, аргумент Условие может быть выражен как 32, «яблоки» или «>32».

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

Примеры

Рассмотрим случай, когда аргумент Диапазон_суммирования опущен. В этом случае суммирование будет производиться по диапазону ячеек, указанному в первом аргументе Диапазон (т.е. он должен содержать числа). В нем же будет производиться поиск значений соответствующих аргументу Условие , которые затем и будут просуммированы. Пусть это будет диапазон B5:B15 , см. Файл примера .

Решим задачи:

  • найти сумму всех чисел больше или равных 10. Ответ: 175. Формула: =СУММЕСЛИ(B5:B15;»>=10″)
  • найти сумму всех чисел меньше или равных 10. Ответ: 42. Формула: =СУММЕСЛИ(B5:B15;»<=10″) </span>
  • найти сумму всех положительных чисел в диапазоне. Формула: =СУММЕСЛИ(B5:B15;»>0″) . Альтернативный вариант с использованием функции СУММПРОИЗВ() выглядит так: =СУММПРОИЗВ((B5:B15)*(B5:B15>0))

Форма задания условия (критерия) достаточно гибка. Например, в формуле =СУММЕСЛИ(B5:B15;D7&D8) критерий <=56 задан через ссылку D7&D8 : в <b>D7 содержится текстовое значение <=, а в <b>D8 – число 56 (см. рисунок ниже). Пользователь, например, может легко изменить критерий с помощью в ячейке D7 . Эквивалентная формула =СУММЕСЛИ(B5:B15;»<=56″) или <span>=СУММЕСЛИ(B5:B15;»<=»&56) </span> или =СУММЕСЛИ(B5:B15;»<=»&D8) </span>или =СУММЕСЛИ(B5:B15;D7 &56) .

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

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

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

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

1. =СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;»>=»&E2)

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

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

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

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

  • Результатом вычисления A2:A13=D2 является массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в A2:A13=D2 , а затем нажав ;
  • Результатом вычисления 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)) .

При необходимости даты могут быть введены непосредственно в формулу =СУММЕСЛИМН(B6:B17;A6:A17;»>=15.01.2010″;A6:A17;»<=06.03.2010″) </p> Чтобы вывести условия отбора в текстовой строке используейте формулу =»Объем продаж за период с «&ТЕКСТ(D6;»дд.ММ.гг»)&» по «&ТЕКСТ(E6;»дд.ММ.гг»)

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

Задача4 (Месяц)

Немного модифицируем условие предыдущей задачи: найдем суммарные продаж за месяц(см. файл примера Лист Месяц ).

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

Для решения 3-й задачи таблица с настроенным автофильтром выглядит так (см. файл примера Лист 2 Даты ).

Предварительно таблицу нужно преобразовать в и включить строку Итогов.

Функция СУММЕСЛИ. Первый из трех китов Excel

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

Вам приходилось суммировать данные по сотрудникам или клиентам из большой таблицы, выбирать сколько выручки было по той или иной номенклатуре? Вы фильтровали по фамилиям/позициям, а затем вносили числа руками в отдельные ячейки? Может быть считали на калькуляторе? А если строк больше тысячи? Как посчитать быстро?

Вот тут пригодится СУММЕСЛИ!

Задача1. Существует статистика по товарам, городам и какие показатели были достигнуты по этим позициям. Необходимо рассчитать: « на какую сумму продано номенклатуры Товар1?»

Прежде, чем преступить к решению 1ой задачи, разберем из чего состоит функция СУММЕСЛИ:

  • Диапазон. Диапазон, в котором содержатся условия поиска. Заполнять обязательно. Для 1ой задачи столбец Товар.
  • Критерий. Можно заполнить числом (85), выражением («>85″), ссылкой на ячейку (B1), функцией (сегодня()). Определяет условие по которому суммируются (!). Все текстовые условия заключаются в кавычки (« ) «>85″. Заполнять обязательно. Для 1ой задачи столбец =Товар1
  • Диапазон_суммирования. Ячейки, для суммирования, если они отличаются от ячеек в Диапазоне. Для 1ой задачи столбец Выручка.

Итак запишем формулу, предварительно занеся аргумент условия в ячейку F3

Не забудьте проверить Посчиталось? Верно? Отлично!

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

А также для подсчета количества повторений той или иной позиции используйте функции СЧЁТЕСЛИ (СЧЁТЕСЛИМН)

Решается она еще проще, чем предыдущие две.

Что такое СУММЕСЛИ?

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

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

=СУММЕСЛИ(Таблица 1 [Продукт]; «Яхта» ;Таблица 1 [Количество]) =СУММЕСЛИ(C2 : C40 ; «Яхта» ; D2 : D40 )

Проблема

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

Тем не менее, есть как минимум три альтернативы:

  1. Использовать формулы массивов, чтобы суммировать данные по нескольким критериям.
  2. Использовать функцию СУММПРОИЗВ
  3. Использовать функцию СУММЕСЛИМН

Есть, правда, еще одно решение, для которого даже Excel не понадобится — съездить в Северную Америку и посчитать яхты, но это, к счастью, не наш метод!

Решение

Итак, воспользуемся третьим из предложенных вариантов решения и запишем вот такую формулу:

=СУММЕСЛИМН(Таблица 1 [Количество];Таблица 1 [Регион]; «Сев.Америка» ;Таблица 1 [Продукт]; «Яхта» )

Обратите внимание, каким образом соотносятся диапазоны данных и аргументы этой функции:

Как работает СУММЕСЛИМН?

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

Чтобы лучше запомнить порядок аргументов этой функции, посмотрите на рисунок:

Бонус

И не один, а целых два!

Во-первых, в Excel 2007 и более поздних версиях существует небольшое семейство подобных функций, включающее в себя СРЗНАЧЕСЛИМН (для вычисления средних значений) и СЧЁТЕСЛИМН (для подсчета количества значений).

Во-вторых, функции этого семейства могут работать со операторами отношения (>, <, = и т.п.) и знаками подстановки, такими как «*» — любое количество любых символов, и «?» — один любой символ. Так, например, чтобы подсчитать количество самолетов, проданных в Северной и Южной Америке, можно воспользоваться такой формулой:</p> =СУММЕСЛИМН(Таблица 1 [Количество];Таблица 1 [Регион]; «*Америка» ;Таблица 1 [Продукт]; «Самолет» )

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

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

Функция SUMIF на Русском Функция СУММЕСЛИ на Английском
СУММЕСЛИ SUMIF

Содержание

Синтаксис

Синтаксис функции СУММЕСЛИ СУММЕСЛИ(Диапазон; Критерий; [Диапазон-Суммирования])

Как видите, функция СУММЕСЛИ имеет 3 аргумента: первые 2 обязательны, а третий — необязательный.

  • Диапазон – это диапазон ячеек для оценки по критериям, например A1: A10.
  • Критерий: условие, которое должно быть выполнено. Критерии могут быть представлены в форме числа, текста, даты, логического выражения, ссылки на ячейку или другой функции Excel. Например, вы можете ввести такие критерии, как «3», «текст», «08.12.2020», «<3», «A1» и т. Д. Обратите внимание, что все текстовые критерии или критерии, содержащие математические символы, должны быть заключены в кавычки («), а числовые критерии не требуют кавычек.</li>
  • Диапазон-Суммирования: Ячейки для суммирования, если условие выполнено. Этот аргумент является необязательным, и вы должны использовать его только в том случае, если вы хотите назначить ячейки, отличные от тех, которые определены в аргументе диапазона. Если аргумент диапазон_суммирования опущен, Excel будет суммировать те же ячейки, к которым применяются критерии (то есть те, которые указаны в аргументе диапазона).

Как использовать СУММЕСЛИ – Примеры.

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

СУММЕСЛИ больше, меньше или равно

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

Поясним примеры из скриншота выше:

  • Пример1. Складывает значения больше 6 в диапазоне ячеек A5: A15.
  • Пример2. Складывает значения в диапазоне ячеек B5: B15, если значение соответствующего столбца A меньше 8.
  • Пример3. Складывает значения в ячейках A5: A15, которые равны значению в ячейке С4. Два варианта написания
  • Пример4. Складывает значения в ячейках B5: B15, если соответствующая ячейка в столбце A отличается от значения в ячейке С4.
  • Пример5. Складывает значения больше или равные 8 в диапазоне A5: A15.
  • Пример6. Складывает значения в ячейках B5: B15, если соответствующее значение в столбце A меньше или равно 8.

СУММЕСЛИ с текстовыми критериями

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

Поясним примеры из скриншота выше:

  • Пример1. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит в точности слово «вишня» и никаких других слов или символов. Ячейки, содержащие «черешня» и «яблоки», не будут включены.
  • Пример2. Складывает значения ячеек В2:В6, если соответствующая ячейка в столбце A содержит слово «шня» отдельно или в сочетании с любым другим словом или символом. Ячейки, содержащие «черешня» и «вишня», будут добавлены т.к содержат в себе «шня».
  • Пример3. Складывает значения ячеек В2: В6, если соответствующая ячейка в столбце A содержит любое значение, кроме «Вишня». 
  • Пример4. Складывает значения ячеек В2: В6, если соответствующая ячейка в столбце A не содержит слова «шня» ни отдельно, ни в сочетании с любым другим словом. В данном случае будет выведена только сумма яблок.

Как и большинство других функций Excel, СУММЕСЛИ не учитывает регистр, что означает, что «Вишня», «ВИШНЯ» и «вишня» дадут точно такой же результат.

Используйте операторы сравнения со ссылками на ячейки

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

= СУММЕСЛИ (A2: A8; «<>» & F1; C2: C8)

Формулы СУММЕСЛИ с подстановочными знаками

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

  • Звездочка (*): представляет любое количество символов
  • Знак вопроса (?): Обозначает один символ в определенной позиции.
  • Амперсанд (&): Обозначает сложение текста. В примере ниже есть описание как это работает.

Поясним примеры из скриншота выше:

  • Пример1. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение т.е в начале и конце любое количество символов.
  • Пример2. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение т.е последняя буква может быть любая. В примере я намерено допустил ошибку и написал вишна.
  • Пример3. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение. критерии включают ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) перед и после ссылки на ячейку для объединения строки.

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

=СУММЕСЛИ(A2:A8; «????»; C2:C8)

Формулы СУММЕСЛИ с условием даты

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

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

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий