Функция excel суммесли (sumif)

Функция Excel СУММЕСЛИ (SUMIF) — примеры использования

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

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

Функцию СУММЕСЛИ можно успешно приспособить для решения самых различных задач. Поэтому мы в этой статье рассмотрим не 1 (один), а 2 (два) примера. Первый связан с суммированием по заданному критерию, второй – с «подтягиванием» данных, то есть в качестве альтернативы ВПР.

Excel COUNT функция

Описание

Освободи Себя COUNT Функция используется для подсчета количества ячеек, содержащих числа, или подсчета чисел в списке аргументов. Возьмите пример, COUNT(1,2,3,4,11,2,1,0) возвращает 8.

Синтаксис и аргументы

Синтаксис формулы

COUNT(value1,, …)

аргументы

Value1: Обязательно: первый элемент, ссылка на ячейку или диапазон. Value2: Необязательно: элемент, ссылка на ячейку или диапазон, в котором вы хотите подсчитать числа.

Возвращаемое значение

Освободи Себя COUNT функция возвращает количество ячеек, содержащих числа.

Замечания

• Функция COUNT подсчитывает количество чисел в предоставленных аргументах, например, = COUNT (0,1,2,3, ”text”) возвращает 4, поскольку в аргументах есть только 4 числа.
• Функция COUNT может подсчитывать количество чисел, дат или текстового представления чисел (числа, заключенные в кавычки, означает «8»).
• Аргументы — до 255 дополнительных элементов.
• Аргументы, которые нельзя преобразовать в числа, не учитываются.
• Если аргумент является массивом или ссылкой, будут учитываться только числовые значения в массиве или ссылке.
• Пустые ячейки, логические значения, значения ошибок или тексты не учитываются.

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

Пример 1

На листе есть список значений. Теперь вы можете подсчитать числа в этом списке по следующей формуле:

=COUNT(B2:B10)

Нажмите Enter Клавиша для подсчета количества чисел в диапазоне B2: B10.

Пример 2

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

=COUNT(3,5,6,2,4,1,3,1)

Нажмите Enter клавиша возврата 8.

Скачать образец файла

Относительные функции:

Excel COUNTA функцияОсвободи Себя COUNTA функция возвращает количество ячеек, содержащих число, текст, значение ошибки или пустой текст «», за исключением реальных пустых ячеек.

Excel COUNTBLANK функцияОсвободи Себя COUNTBLANK функция возвращает количество пустых ячеек. Функция СЧИТАТЬПУСТОТЫ — это встроенная статистическая функция, которую можно использовать как функцию рабочего листа (WS) в Excel.

Excel COUNTIF функцияОсвободи Себя COUNTIF Функция — это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию.

Excel COUNTIFS функцияОсвободи Себя COUNTIFS Функция возвращает количество ячеек, соответствующих одному или нескольким критериям. Функция СЧЁТЕСЛИМН является одной из встроенных статистических функций, и ее можно использовать как функцию рабочего листа (WS) в Excel.

Относительные статьи:

Считайте ячейки равнымиЕсли вы хотите подсчитать ячейки, точно соответствующие заданному критерию, вы можете выбрать COUNTIF функция или SUMPRODUCT (в сочетании с EXACT function) функция для точного подсчета ячеек без учета регистра или с учетом регистра.

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

Считайте ячейки равными x или yЕсли вы хотите подсчитывать ячейки, если они соответствуют хотя бы одному из заданных условий (логика ИЛИ) в Excel, т.е. подсчитывать ячейки, равные либо x, либо y, вы можете использовать несколько COUNTIF формулы или объедините COUNTIF формула и SUM формула для выполнения этой работы.

Подсчитайте ячейки равными x и yЗдесь, в этом руководстве, будут представлены некоторые формулы для подсчета ячеек, соответствующих всем заданным условиям, которые мы называем AND логика с COUNTIFS функции.

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

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

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

– суммирует все значения ячеек в диапазоне A2:A10, которые больше 10.

Очень просто, правда? А теперь давайте рассмотрим немного более сложный пример. Предположим, что у нас есть таблица, в которой перечислены имена продавцов и их номера ID (Lookup table). Кроме этого, есть ещё одна таблица, в которой те же ID связаны с данными о продажах (Main table). Наша задача – найти сумму продаж для заданного продавца. Здесь есть 2 отягчающих обстоятельства:

• Основная таблица (Main table) содержит множество записей для одного ID в случайном порядке.
• Вы не можете добавить столбец с именами продавцов к основной таблице.

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

Перед тем, как мы начнём, позвольте напомнить Вам синтаксис функции СУММЕСЛИ (SUMIF):

• range (диапазон) – аргумент говорит сам за себя. Это просто диапазон ячеек, которые Вы хотите оценить заданным критерием.
• criteria (критерий) – условие, которое говорит формуле, какие значения суммировать. Может быть числом, ссылкой на ячейку, выражением или другой функцией Excel.
• sum_range (диапазон_суммирования) – необязательный, но очень важный для нас аргумент. Он определяет диапазон связанных ячеек, которые будут суммироваться. Если он не указан, Excel суммирует значения ячеек, в первом аргументе функции.

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

1. range (диапазон) – так как мы ищем по ID продавца, значениями этого аргумента будут значения в столбце B основной таблицы (Main table). Можно задать диапазон B:B (весь столбец) или, преобразовав данные в таблицу, использовать имя столбца Main_table.

2. criteria (критерий) – так как имена продавцов записаны в просматриваемой таблице (Lookup table), используем функцию ВПР для поиска ID, соответствующего заданному продавцу. Имя записано в ячейке F2, поэтому для поиска используем формулу:

   Конечно, Вы могли бы ввести имя как искомое значение напрямую в функцию ВПР, но лучше использовать абсолютную ссылку на ячейку, поскольку так мы создаём универсальную формулу, которая будет работать для любого значения, введённого в эту ячейку.

3. sum_range (диапазон_суммирования) – это самая простая часть. Так как данные о продажах записаны в столбец C, который называется Sales, то мы просто запишем Main_table.

Всё, что Вам осталось сделать, это соединить части в одно целое, и формула СУММЕСЛИ+ВПР будет готова:

Сумма, если Дата больше, чем

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

В примере показано, ячейка H4 содержит формулу:

Эта формула суммирует суммы в столбце D, если Дата в столбце C больше 1 октября 2019 года.

Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.

В данном случае, мы хотим чтобы дата была больше, чем 1 октября 2019 года, поэтому мы используем оператор больше чем (>).

Обратите внимание, что мы должны поставить оператор «больше, чем» в двойные кавычки и присоединить к нему амперсанд (&)

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

​ заданных аргументом​​СУММЕСЛИ​​ следующим образом:​​ 64.​​ текста или выражений​​ функции ЕСЛИ.​​ в Excel по​
​Excel, чтобы посчитать сумму​ тогда как ячейки,​Диапазон_условия1​,​созданы, чтобы облегчить​позволяет подсчитать условную​Поскольку функция​ используейте формулу =»Объем​3. Другим вариантом использования​ значения и текст​: Давно бы так.​
​ с логическими и​ (для ввода жмем​ шкале).​МИНУСЫ: Формулы массива​ всегда равно 1,​​ нескольким столбцам и​ по условию, выборочно.​​ которые содержат значение​​(B2:B9). Кроме того,​​»яблоки»​​ эту задачу и​​ сумму в Excel,​​СУММ​ продаж за период​ функции СУММПРОИЗВ() является​ игнорируются.​=СУММ(СУММЕСЛИ(A1:A40;{«AAA»;»FFF»;»OOO»};B1:B40))​
​ математическими символами должны​ CTRL+SHIFT+Enter):​Вид таблицы данных:​ при вычислениях на​ а ЛОЖЬ всегда​ строкам».​Символы в формулах​ ЛОЖЬ, возвращают 0​ функция выполняет поиск​или​
​ дать пользователям максимально​ т.е. сумму ячеек,​поддерживает работу не​ с «&ТЕКСТ(D6;»дд.ММ.гг»)&» по​ формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)).​A2:A13 B2:B13 Интервал_условия1; интервал_условия2;​Если критериев много,​ быть указаны в​Функция СУММЕСЛИМН возвращает массив​
​Для расчета суммарного балла​ больших диапазонах данных​ равно 0 мы​Предположим что у​ помогают выбрать из​ (нуль).​ имени​»32″​ удобный инструмент. Надеюсь,​​ которые удовлетворяют определенному​ только с отдельными​ «&ТЕКСТ(E6;»дд.ММ.гг»)​4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2))​ …​ лучше вынести их​ кавычках.​ значений для критериев​ согласно установленным критериям​ заметно увеличивают время​ продолжаем работать с​ Вас есть вот​ таблицы конкретные данные.​Действие​»Артем»​.​ что этот урок​ критерию. Функция​ ячейками, но и​В последней формуле использован​​ похожа на вышеупомянутую​   представляют собой от​ в отдельный диапазон:​Максимальная длина условия, переданного​ «Китай» и «Грузия»​ используем следующую формулу:​ пересчёта.ПРИМЕЧАНИЯ: Количество обрабатываемых​ массивами как с​ такой отчёт по​ Как использовать функцию​
​Описание​в диапазоне ячеек,​Диапазон_условия2, Условие2, …​ помог Вам освоить​СУММЕСЛИ​
​ целыми диапазонами, то​ Пользовательский формат.​​ формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После​ 1 до 127​=СУММ(ЕСЛИ(A1:A67=ТРАНСП(F2:F6);B1:B67))​ в качестве аргумента​ соответственно, которые суммируются​Описание аргументов:​ массивов ограничено 255.​​ числами 0 и​ продажам торговых представителей:​​ «СУММЕСЛИ», смотрите в​Использование подстановочных знаков​​ заданных аргументом​​    (необязательный аргумент)​​ базовые функции суммирования​​может содержать только​ вышеприведенную формулу можно​Немного модифицируем условие предыдущей​ ее ввода нужно​ диапазонов, в которых​Вторая — формула​ условие1 (условие2 и​ функцией СУММ.​C3:C14 – диапазон ячеек​ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая​ 1.​Из него Вам​ статье «Функция «СУММЕСЛИ»​Подстановочные знаки, такие как​​Диапазон_условия2​Дополнительные диапазоны и условия​ в Excel, и​ один критерий.​ модифицировать:​ задачи: найдем суммарные​​ вместо​ проверяется соответствующее условие.​​ массива.​​ т. д.) не​​Примечание: если критерии переданы​​ с баллами, из​​ версия Excel​​Перемножив полученные значения​ необходимо узнать сколько​ в Excel».​ вопросительный знак (?)​(C2:C9). Затем функция​ для них. Можно​​ теперь Вы сможете​Например, следующая формула суммируем​Истинная мощь функции​ продаж за месяц(см.​ENTER​D2 «>=»&E2 Условие1; условие2;​Как вводить формулу​
​ должна превышать 255​​ в качестве константы​ которых будут автоматически​Способ 4:​​ массивов между собой​карандашей​​У нас такая​​ или звездочка (*),​

excelworld.ru>

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

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

В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.

Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.

Как видно, запись формулы выглядит гораздо проще и понятнее.

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

Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано. В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

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

Как работает функция СЧЕТЕСЛИМН?

Она вычисляет количество соответствий в нескольких диапазонах на основе одного или множества критериев.

Синтаксис функции выглядит следующим образом:

СЧЕТЕСЛИМН(диапазон1;условие1; …)

• диапазон1 (обязательный) – определяет первую область, к которой должно применяться первое условие ( условие1).
• условие1 (обязательное) – устанавливает требование к отбору в виде числа , ссылки на ячейку , текстовой строки , выражения или другой функции Excel. Определяет, какие ячейки должны учитываться.
• … (необязательные) – это дополнительные области и связанные с ними критерии. Вы можете указать до 127 таких пар.

На самом деле, вам не нужно помнить этот синтаксис наизусть. Microsoft Excel отобразит аргументы функции, как только вы начнете печатать; аргумент, который вы вводите в данный момент, будет выделен жирным шрифтом.

Что нужно запомнить?

1. Диапазонов поиска может быть от 1 до 127. Для каждого из них указывается свое условие. Учитываются только те случаи, которые отвечают всем предъявленным требованиям.
2. Каждый дополнительный диапазон должен иметь одинаковое число строк и столбцов с первым. Иначе получите ошибку #ЗНАЧ!
3. Допускаются как смежные, так и несмежные диапазоны.
4. Если в аргументе указана ссылка на пустую ячейку , функция обрабатывает его как нулевое значение (0).
5. В критериях можно использовать символы подстановки – звездочка (*) и знак вопроса (?). Далее мы расскажем об этом подробнее.

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

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

Формулу используем следующую (но записываем ее, как формулу массива, то есть вводим ее через комбинацию клавиш CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;”Товар_1″;C2:C14;{“Китай”;”Грузия”}))

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

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

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

6

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

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

ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений

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

Предположим, что у нас есть список товаров с данными о продажах за несколько месяцев, с отдельным столбцом для каждого месяца. Источник данных – лист Monthly Sales:

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

Решение этой задачи – использовать массив констант в аргументе col_index_num (номер_столбца) функции ВПР. Вот пример формулы:

Как видите, мы использовали массив {2,3,4} для третьего аргумента, чтобы выполнить поиск несколько раз в одной функции ВПР, и получить сумму значений в столбцах 2, 3 и 4.

Теперь давайте применим эту комбинацию ВПР и СУММ к данным в нашей таблице, чтобы найти общую сумму продаж в столбцах с B по M:

Важно! Если Вы вводите формулу массива, то обязательно нажмите комбинацию Ctrl+Shift+Enter вместо обычного нажатия Enter. Microsoft Excel заключит Вашу формулу в фигурные скобки:

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

Возможно, Вам стало любопытно, почему формула на рисунке выше отображает , как искомое значение. Это происходит потому, что мои данные были преобразованы в таблицу при помощи команды Table (Таблица) на вкладке Insert (Вставка). Мне удобнее работать с полнофункциональными таблицами Excel, чем с простыми диапазонами. Например, когда Вы вводите формулу в одну из ячеек, Excel автоматически копирует её на весь столбец, что экономит несколько драгоценных секунд.

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

Эту проблему можно преодолеть, используя комбинацию функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) вместо VLOOKUP (ВПР) и SUM (СУММ). Далее в этой статье Вы увидите несколько примеров таких формул.

Примеры суммеслимн

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

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

1. Примеры с датами продаж:

Задача: найти количество продуктов проданных 22.02.2019г.

В свободной ячейке пишете следующее: =СУММЕСЛИМН(С2:С8;B2:B8;B2)

1. Примеры с двумя условиями:

Задача: определить сумму яблок, проданных 22.02.2019г.:

Функция будет выглядеть следующим образом: =СУММЕСЛИМН(C2:C8;B2:B8;B2;A2:A8;»Яблоко»)

где B2:B8;B2 – первое условие, а A2:A8;»Яблоко» – второе. Диапазон суммирования по столбцу Количество.

1. Примеры с текстом и звездочкой:

Задача: найти количество яблок, проданных Артемом.

Содержимое пустой ячейки: =СУММЕСЛИМН(C2:C8;A2:A8;»Яблоко»;D2:D8;»А*»)

Использование подстановочного знака звездочка помогает искать продавца, имя которого начинается на букву А. При таком условии считается число яблок, проданных Артемом и Ангелиной, поскольку их имена начинаются на букву А. Чтобы найти именно продажи Артема, необходимо изменить аргумент на «Ар*».

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

Задача: Найти дату продажи Ангелиной продуктов больше 10 единиц.

Функция имеет следующий вид: =СУММЕСЛИМН(B2:B8;D2:D8;»Ангелина»;C2:C8;»>10″)

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

Пример 2. Используем подстановочные знаки в функции СРЗНАЧЕСЛИ

Вы можете использовать подстановочные знаки в критерии функции.

В Excel существует три подстановочных знака – ?, *, ~.

• знак “?” – сопоставляет любой одиночный символ;
• знак “*” – сопоставляет любые дополнительные символы;
• знак “~” – используется, если нужно найти сам вопросительный знак или звездочку.

На примере выше, функция проверяет список данных в диапазоне “А2:А6” на соответствие критерию с подстановочными знаками “*а*”, который подразумевает любые данные содержащие букву “а”. Так как в диапазоне данных “А2:А6” этому критерию соответствуют все имена кроме “Олег” => среднее арифметическое будет вычислено по диапазону ячеек B2:B6 (исключая ячейку B5) = “29.5”.

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

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

Общий вид

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

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

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

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

В результате получили 180. Функция проработала все указанные нами условия и выдала корректный результат. Также можно повторить подобные манипуляции для других типов товара, сменив критерий «одежда» на какой-то другой.

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

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

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

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

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

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

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

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

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

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

По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:

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

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

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

условие1– обязательное первое условие, которое должно быть выполнено. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, “> = 10”, A1, “яблоко” или СЕГОДНЯ().

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

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

СУММЕСЛИ — условная сумма в Excel

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

Например, следующая формула суммируем только положительные числа диапазона A1:A10

Обратите внимание, что условие заключено в двойные кавычки

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

Меняем условие, меняется и результат:

Условие можно комбинировать, используя оператор конкатенации. В примере ниже формула вернет сумму значений, которые больше значения в ячейке B1.

Во всех примерах, приведенных ранее, мы производили суммирование и проверяли условие по одному и тому же диапазону. А что делать, если необходимо просуммировать один диапазон, а условие проверять по-другому?

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

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

Нажав Enter получим результат:

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

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