Как просуммировать определенные ячейки в excel по условию: Функция СУММЕСЛИ

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

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

Это видео — часть учебного курса Сложение чисел в Excel.

Советы: 

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

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

Важно: Функция СУММЕСЛИ возвращает неверные результаты, если она используется для сопоставления строк длиннее 255 символов или строки #VALUE !.

Синтаксис

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

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

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

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

  Например, критерии могут быть выражены как 32, «>32», B5, «3?», «apple*», «*~?», или TODAY().

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

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

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

  диапазон	Диапазон_суммирования.	Фактические суммарные ячейки
  A1:A5	B1:B5	B1:B5
  A1:A5	B1:K5	B1:B5

Примеры

Пример 1

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

Стоимость имущества	Комиссионные	Данные
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, а затем — клавишу Enter. Кроме того, вы можете настроить ширину столбцов в соответствии с содержащимися в них данными.

Категория	Продукты	Объем продаж
Овощи	Помидоры	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 ₽

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

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

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

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

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

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

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

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

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

Общие сведения о формулах в Excel

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

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

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

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

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

Формула суммы по условию критерия выборки значений в Excel

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

Примеры формулы для суммы диапазонов с условием отбора в Excel

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

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

В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.

Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки «<0» (меньше чем ноль) используется строка «>0» (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.



Пример логического выражения в формуле для суммы с условием

Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно (<1000) придется использовать знак «меньше или равно» (<=1000) иначе цены со значением ровно 1000 не будут включены в расчеты. Более того в таком случае мы уже не можем использовать в другой формуле «больше или равно» (>=1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:

Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!

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

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

Синтаксис формулы суммирования по условию в Excel

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

Ниже на рисунке представлена таблица отчета продаж по клиентам за месяц. Напротив, каждого клиента указана сумма, на которую совершил покупки клиент на протяжении месяца. Некоторые клиенты за месяц совершили по несколько покупок, поэтому их наименование дублируется в списке. Необходимо узнать на какую общую сумму купил товара «Клиент3» в данном месяце. Здесь весьма уместно использовать формулу с заполнением третьего аргумента функции СУММЕСЛИ:

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

• сроки с текстовым значением;
• числовые значения;
• логические выражения;
• формулы с функциями;
• ссылки на ячейки.

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

Создание критериев условий для функции СУММЕСЛИ

Второй аргумент функции называется «Критерий». Данный логический аргумент используется и в других подобных логических функциях: СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. В каждом случаи аргумент заполняется согласно одних и тех же правил составления логических условий. Другими словами, для всех этих функций второй аргумент с критерием условий является логическим выражением возвращающим результат ИСТИНА или ЛОЖЬ. Это значит, что выражение должно содержать оператор сравнения, например: больше (>) меньше (<) равно (=) неравно (<>), больше или равно (>=), меньше или равно (<=). За исключением можно не указывать оператор равно (=), если должно быть проверено точное совпадение значений.

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

Таблица правил составления критериев условий:

Чтобы создать условие	Примените правило	Пример
Значение равно заданному числу или ячейке с данным адресом.	Не используйте знак равенства и двойных кавычек.	=СУММЕСЛИ(B1:B10;3)
Значение равно текстовой строке.	Не используйте знак равенства, но используйте двойные кавычки по краям.	=СУММЕСЛИ(B1:B10;»Клиент5″)
Значение отличается от заданного числа.	Поместите оператор и число в двойные кавычки.	=СУММЕСЛИ(B1:B10;»>=50″)
Значение отличается от текстовой строки.	Поместите оператор и число в двойные кавычки.	=СУММЕСЛИ(B1:B10;»<>выплата»)
Значение отличается от ячейки по указанному адресу или от результата вычисления формулы.	Поместите оператор сравнения в двойные кавычки и соедините его символом амперсант (&) вместе со ссылкой на ячейку или с формулой.	=СУММЕСЛИ(A1:A10;»<«&C1) или =СУММЕСЛИ(B1:B10;»<>»&СЕГОДНЯ())
Значение содержит фрагмент строки	Используйте операторы многозначных символов и поместите их в двойные кавычки	=СУММЕСЛИ(A1:A10;»*кг*»;B1:B10)

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

Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):

Суммирование по неточному совпадению в условии критерия отбора

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

Суммируются все значения ячеек в диапазоне B2:B16 в соответствии со значениями в ячейках диапазона A2:A16, в которых после третьего символа фрагмент строки «-защита».

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

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

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

1. 1 Простое сложение в Excel
2. 2 Суммирование ячеек по условию
3. 3 Функция «СУММЕСЛИ»
4. 4 Функция «СУММЕСЛИМН»
5. 5 Итог

Простое сложение в Excel

Складывать значения ячеек можно с помощью простой функции «СУММ», которая суммирует выбранные значения без определенного условия. Она используется, когда есть ячейки, массив или несколько массивов для сложения. В этом случае достаточно выделить нужные элементы электронной таблицы и применить функцию. Она играет роль обычного калькулятора со знаком «+».

Суммирование ячеек по условию

Excel также предусматривает суммирование с учетом условия. Функция применяется, когда необходимо просуммировать только те значения, которые соответствуют заданному условию. Для нее возможно задать три аргумента:

• Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
• Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как ">5". Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие "Иванов";
• Диапазон суммирования – массив значений, которые нужно сложить.

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

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

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

1. По свободной ячейке кликните левой клавишей мыши, чтобы выбрать место, где будет отображаться результат операции.
2. Кликните по иконке, расположенной рядом со строкой ввода формул, чтобы вызвать меню с функциями.
3. Выберите категорию «Математические» и найдите в списке функций «СУММЕСЛИ».
4. Кликните по кнопке «ОК», чтобы подтвердить свой выбор.
5. В появившемся окне «Аргументы функции» нужно заполнить обязательные поля: «Диапазон» и «Критерий». Например, нужно узнать, сколько килограмм фруктов продал продавец Иванов. Для этого выделите диапазон столбца «Продавец» полностью.
6. В строке «Критерий» пропишите фамилию продавца: «Иванов». Кавычки можно не указывать, программа вставляет их автоматически.
7. Поставьте курсор мыши в поле диапазона суммирования и выделите столбец «Количество (кг)» в таблице.
8. Нажмите на кнопку «ОК» внизу, чтобы произвести суммирование с условием, или из данной таблицы узнать, сколько килограмм фруктов продал Иванов.

В выделенной ячейке отобразится результат сложения, а в верхней строке – функция. Вычислить значение можно без вызова окна «Аргументы функции», если прописать все вручную. При этом базовая запись должна выглядеть так: =СУММЕСЛИ(X; «Условие»; Y), где X – это диапазон, а Y – диапазон суммирования. Значения не обязательно вписывать, их достаточно выделить с помощью мышки в таблице. Все элементы функции разделены знаком «;».

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

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

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

Чтобы суммировать ячейки с несколькими условиями, действуйте согласно следующей инструкции:

1. Выделите пустую ячейку, в которой будет отображаться конечный результат, затем нажмите на кнопку fx, которая находится рядом со строкой функций.
2. В разделе «Математические» в окне «Вставка функций» нажмите «СУММЕСЛИМН», затем подтвердите выбор, нажав на кнопку «ОК».
3. В появившемся окне в строке «Диапазон суммирования» введите ячейки, который находятся в столбце «Количество».
4. В «Диапазон условия» выделите все ячейки в столбце «Товар».
5. В качестве первого условия пропишите значение «Яблоки».
6. После этого необходимо задать второе условие и диапазон для него. В данной таблице столбец «Покупатели» является значением для диапазона. Выделите его в строку, затем в втором условии пропишите фамилию Евдокимов.
7. Нажмите на кнопку «ОК», чтобы программа посчитала, сколько яблок купил Евдокимов.

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

Итог

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

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

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

Это видео является частью учебного курса Добавление чисел в Excel.

Советы: 

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

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

Важно: Функция СУММЕСЛИ возвращает неверные результаты, когда вы используете ее для сопоставления строк длиннее 255 символов или строки #ЗНАЧ! .

Синтаксис

СУММЕСЛИ(диапазон, критерии, [сумма_диапазон])

Синтаксис функции СУММЕСЛИ имеет следующие аргументы:

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

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

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

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

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

  Суммарный_диапазон должен быть того же размера и формы, что и 9. 0003 диапазон . Если это не так, производительность может пострадать, и формула будет суммировать диапазон ячеек, который начинается с первой ячейки в sum_range , но имеет те же размеры, что и range . Например:

  диапазон	сумма_диапазон	Фактические суммированные ячейки
  А1:А5	Б1:Б5	Б1:Б5
  А1:А5	Б1:К5	Б1:Б5

Примеры

Пример 1

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

Значение свойства	Комиссия	Данные
100 000 долларов США	7000 долларов	250 000 долларов США
200 000 долларов	14 000 долларов США
300 000 долларов США	21 000 долларов США
400 000 долларов США	28 000 долларов США
Формула	Описание	Результат
=СУММЕСЛИ(A2:A5;»>160000″,B2:B5)	Сумма комиссионных за недвижимость стоимостью более 160 000 долларов США.	63 000 долларов США
=СУММЕСЛИ(A2:A5;»>160000″)	Сумма стоимости имущества свыше 160 000 долларов США.	900 000 долларов
=СУММЕСЛИ(A2:A5,300000,B2:B5)	Сумма комиссионных за стоимость имущества, равную 300 000 долларов США.	21 000 долларов
=СУММЕСЛИ(A2:A5,»>» и C2,B2:B5)	Сумма комиссионных за стоимость недвижимости выше значения в C2.	49 000 долларов США

Пример 2

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

Категория	Еда	Продажи
Овощи	Помидоры	2300 долларов США
Овощи	Сельдерей	5 500 долларов США
Фрукты	Апельсины	800 долларов
	Сливочное масло	400 долларов
Овощи	Морковь	4 200 долларов США
Фрукты	Яблоки	1200 долларов
Формула	Описание	Результат
=СУММЕСЛИ(A2:A7;»Фрукты»,C2:C7)	Сумма продаж всех продуктов в категории «Фрукты».	2000 долларов
=СУММЕСЛИ(A2:A7;»Овощи»,C2:C7)	Сумма продаж всех продуктов питания категории «Овощи».	12 000 долларов США
=СУММЕСЛИ(B2:B7;»*es»,C2:C7)	Сумма продаж всех продуктов, оканчивающихся на «es» (помидоры, апельсины и яблоки).	4300 долларов
=СУММЕСЛИ(A2:A7,»»,C2:C7)	Сумма продаж всех продуктов питания, для которых не указана категория.	400 долларов

Верх страницы

Нужна дополнительная помощь?

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

См. также

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

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

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

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

Функция IFS (Office 365, Excel 2016 и более поздние версии)

Обзор формул в Excel

Как избежать неработающих формул

Обнаружение ошибок в формулах

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

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

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

Использование СУММЕСЛИ, СЧЁТЕСЛИ и связанных функций для быстрого анализа данных (бесплатная предварительная версия)

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

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

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

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

СУММЕСЛИ в Excel — синтаксис и основные способы использования

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

Функция доступна в Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних версиях.

Синтаксис следующий:

СУММЕСЛИ(диапазон, критерий, [сумма_диапазон])

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

• Диапазон (обязательно) — диапазон ячеек для оценки по критериям .
• Критерий (обязательно) — условие, которое должно быть выполнено. Он может быть представлен в виде числа, текста, даты, логического выражения, ссылки на ячейку или другой функции Excel. Например, можно ввести такие критерии, как «5», «вишня», «25.10.2014», «<5» и т. д.
• Sum_range (необязательный) — диапазон для суммирования при выполнении условия. Если опущено, то суммируется диапазон .

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

Базовая формула СУММЕСЛИ

Чтобы лучше понять синтаксис СУММЕСЛИ, рассмотрим следующий пример. Предположим, у вас есть список продуктов в столбце A, регионы в столбце B и суммы продаж в столбце C. Ваша цель — получить общий объем продаж для определенного региона, скажем, 9.0541 Север . Чтобы сделать это, давайте создадим формулу СУММЕСЛИ в Excel в ее простейшей форме.

Вы начинаете с определения следующих аргументов:

• Диапазон — список регионов (B2:B10).
• Критерий — «Север» или ячейка, содержащая интересующий регион (F1).
• Sum_range — суммируемые суммы продаж (C2:C10).

Сложив аргументы вместе, мы получим следующую формулу:

=СУММЕСЛИ(B2:B10, "север", C2:C10)

или

=СУММЕСЛИ(B2:B10, F1, C2:C10)

Обе формулы суммируют только продажи в регионе Север :

Примечание. Параметр sum_range фактически определяет только верхнюю левую ячейку диапазона, подлежащего суммированию. Оставшаяся область определяется размерами аргумента диапазона. На практике это означает, что аргумент sum_range не обязательно должен быть того же размера, что и 9.0541 диапазон аргумент, т.е. е. он может иметь разное количество строк и столбцов. Однако верхняя левая ячейка всегда должна быть правой. Например, в приведенной выше формуле вы можете указать C2, или C2:C4, или даже C2:C100 в качестве аргумента sum_range , и результат все равно будет правильным. Однако наилучшей практикой является предоставление одинакового размера range и sum_range .

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

Как использовать СУММЕСЛИ в Excel — примеры формул

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

СУММЕСЛИ больше или меньше

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

• Больше (>)
• Больше или равно (>=)
• Менее (<)
• Меньше или равно (<=)

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

=СУММЕСЛИ(C2:C10, ">3", B2:B10)

Если целевое число в другая ячейка , скажем, F1, соедините логический оператор и ссылку на ячейку:

=СУММЕСЛИ(C2:C10, ">"&F1, B2:B10)

Аналогичным образом можно суммировать значения меньше заданного числа. Для этого используйте оператор меньше чем (<):

=СУММЕСЛИ(C2:C10, "<3", B2:B10)

СУММ ЕСЛИ равно

Формула СУММЕСЛИ с критерием "равно" работает как для чисел, так и для текста. В таких критериях знак равенства фактически не требуется.

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

=СУММЕСЛИ(C2:C10, 3, B2:B10)

или

=СУММЕСЛИ(C2:C10, "=3", B2:B10)

До

сумма, если 0 равно 4 ячейке , введите ссылку на ячейку для критерия :

=СУММЕСЛИ(C2:C10, F1, B2:B10)

Где B2:B10 — количество, C2:C10 — продолжительность доставки, а F1 — желаемое значение срок поставки.

Аналогичным образом можно использовать критерий "равно" с текстовыми значениями. Например, чтобы сложить Яблоки суммы, выберите любую из формул ниже:

=СУММЕСЛИ(A2:A10, "яблоки", B2:B10)

=СУММЕСЛИ(A2:A10, "=яблоки", B2:B10)

=СУММЕСЛИ(A2:A10, F1, B2:B10)

Где A2:A10 — список элементов для сравнения со значением в F1.

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

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

СУММ ЕСЛИ не равно

Для построения критерия "не равно" используйте логический оператор "<>".

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

Например, для суммирования сумм с отгрузкой, отличной от 3 дней, формула выглядит следующим образом:

=СУММЕСЛИ(C2:C10, "<>3", B2:B10)

Чтобы найти общее количество все элементы, кроме Яблоки , формула:

=СУММЕСЛИ(A2:A10, "<>яблоки", B2:B10)

Когда критерий находится в другой ячейке, объединить "не равно" оператор и ссылку на ячейку, например:

=СУММЕСЛИ(A2:A10, "<>"&F1, B2:B10)

СУММ, ЕСЛИ пусто

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

Если "пустой" означает ячейки, которые не содержат абсолютно ничего (без формулы, без строки нулевой длины, возвращаемой какой-либо другой функцией), тогда используйте "=" для критериев. Например:

=СУММЕСЛИ(B2:B10, "=", C2:D10)

Если «пусто» включает пустых строки (например, ячейки с формулой типа ="" ), то используйте "" для критериев:

=СУММЕСЛИ(B2:B10, "", C2:D10)

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

СУММА, ЕСЛИ не пусто

Чтобы сделать вид «если ячейка не пуста, то сумма» формулы, используйте "<>" в качестве критерия. Это суммирует все ячейки, содержащие что-либо в них, включая строки нулевой длины.

Например, вот как можно суммировать продажи для всех регионов, т. е. если столбец B не пуст:

=СУММЕСЛИ(B2:B10, "<>", C2:D10)

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

При суммировании чисел в одном столбце на основе текстовых значений в другом столбце важно различать точное и частичное соответствие.

Критерии	Пример формулы	Описание
Сумма, если она равна	Точное совпадение: =СУММЕСЛИ(A2:A8, "бананы", C2:C8)	Суммируйте значения в ячейках C2:C8, если ячейка в столбце A той же строки содержит только слово «бананы» и не содержит других слов или символов. Ячейки, содержащие «зеленые бананы», «зеленые бананы» или «бананы!» не включены.
Сумма, если ячейка содержит	Частичное совпадение: =СУММЕСЛИ(A2:A8, "*бананы*", C2:C8)	Суммируйте значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит слово «бананы» отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «зеленые бананы», «зеленые бананы» или «бананы!» суммируются.
Сумма, если она не равна	Точное совпадение: =СУММЕСЛИ(A2:A8, "<>бананы", C2:C8)	Суммируйте значения в ячейках C2:C8, если ячейка в столбце A содержит любое значение, кроме "бананы". Если ячейка содержит «бананы» вместе с некоторыми другими словами или символами, такими как «желтые бананы» или «бананы желтые», такие ячейки суммируются.
Сумма, если ячейка не содержит	Частичное совпадение: =СУММЕСЛИ(A2:A8, "<>*бананы*", C2:C8)	Суммируйте значения в ячейках C2:C8, если ячейка в столбце A не содержит слова «бананы» отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «бананы желтые» или «бананы желтые», не суммируются.

Примеры реальных формул см. в разделах Сумма, если она равна, и Сумма, если не равна.

В следующем разделе мы более подробно рассмотрим формулы СУММЕСЛИ с частичным совпадением.

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

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

• Вопросительный знак (?) для соответствия любому одиночному символу в определенной позиции.
• Звездочка (*) для соответствия любому количеству символов.

Пример 1. Суммирование значений на основе частичного совпадения

Предположим, вы хотите суммировать продажи для всех северных регионов, включая Север , Северо-Восток и Северо-Запад . Для этого поставьте звездочку сразу после слова «север»:

=СУММЕСЛИ(B2:B10, «север*», C2:D10)

Также подойдет звездочка с обеих сторон:

=СУММЕСЛИ(B2:B10, "*север*", C2:D10)

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

=СУММЕСЛИ(B2:B10, F1&"*", C2:D10)

Или

=СУММЕСЛИ(B2:B10, "*"&F1&"*", C2:D10)

Пример 2.

Сумма, если ячейка содержит * или ?

Чтобы сопоставить буквальный вопросительный знак или звездочку , поместите тильду (~) перед символом, например. "~?" или "~*".

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

=СУММЕСЛИ(B2:B10, "*~*", C2:D10)

Если критерий (* в нашем случае) введен в отдельную ячейку, то соедините тильду и ссылку на ячейку, как здесь :

=СУММЕСЛИ(B2:B10, "*"&"~"&F1, C2:D10)

Пример 3. Сумма, если другая ячейка содержит текст

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

Чтобы сложить значения в ячейках C2:C8, если ячейка в столбце A содержит какие-либо текстовые символы:

=СУММЕСЛИ(A2:A8,"?*", C2:C8)

Суммировать значения в C2:C8, если ячейка в столбце A содержит любое текстовое значение, включая строки нулевой длины:

=СУММЕСЛИ(A2:A8,"*", C2:C8)

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

Как использовать Excel СУММЕСЛИ с датами

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

Если вы хотите суммировать продажи товаров, поставленных до 10 сентября 2020 г., критерии можно выразить следующим образом:

=СУММЕСЛИ(C2:C10, "<9/10/2020", B2: B10)

или

=СУММЕСЛИ(C2:C10, "<"&ДАТА(2020,9,10), B2:B10)

или

=СУММЕСЛИ(C2:C10, "<"&F10, "<"&F10, "<"&ДАТА(2020,9,10), B2:B10) , B2:B10)

Где F1 — целевая дата.

Чтобы суммировать ячейки на основе сегодняшней даты , включите в критерии функцию СЕГОДНЯ. Например, вот как вы вычисляете общее количество продаж с датой доставки до сегодняшнего дня:

=СУММЕСЛИ(C2:C10, "<"&СЕГОДНЯ(), B2:B10)

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

Например, для суммирования значений в столбце B, если дата в столбце C равна между двумя датами , используйте следующую формулу:

=СУММЕСЛИМН(B2:B10, C2:C10, ">="&F1 , C2:C10, "<="&G1)

Где B2:B10 — диапазон суммы, C2:C10 — список дат для проверки, F1 — дата начала и G1 — дата окончания.

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

Как выполнить СУММЕСЛИ из другого листа

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

Например, приведенная ниже формула суммирует значения в C2:C10 на листе Данные на основе критериев в B3 на листе 1 :

=СУММЕСЛИ(Данные!B2:B10, B3, Данные!C2 :C10)

Как правильно использовать ссылки на ячейки в критериях СУММЕСЛИ

Чтобы создать гибкую формулу, вы обычно вставляете все переменные параметры в предопределенные ячейки вместо того, чтобы «жестко кодировать» их. С Excel SUMIF это может быть проблемой.

В простейшем случае при суммировании «если равно» вы просто используете ссылку на ячейку для критериев. Например:

=СУММЕСЛИ(C2:C10, F1, B2:B10)

Но когда ссылка на ячейку используется вместе с логическим оператором , критерии должны быть представлены в виде строки. Таким образом, вы используете двойные кавычки (""), чтобы начать текстовую строку, и амперсанд (&), чтобы объединить и закончить строку. Например:

=СУММЕСЛИ(C2:C10, ">"&F7, B2:B10)

Обратите внимание, что операторы сравнения заключены в кавычки, а ссылки на ячейки - нет.

Почему моя формула СУММЕСЛИ не работает?

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

1. СУММЕСЛИ поддерживает только одно условие

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

2. Диапазон и диапазон_сумм должны иметь одинаковый размер.

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

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

Учитывая вышеизложенное, приведенная ниже формула фактически суммирует ячейки в C2:C10, а не в C2:D10. Почему? Потому что диапазон состоит из 1 столбца и 9 строк, как и sum_range .

=СУММЕСЛИ(B2:B10, "север", C2:D10)

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

3. Range и sum_range должны быть диапазонами, а не массивами

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

5. Синтаксис критериев СУММЕСЛИ

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

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

=СУММЕСЛИ(B2:B10, "север*", C2:D10)

=СУММЕСЛИ(C2:D10, ">100")

=СУММЕСЛИ(B2:B10, "<>север) ", C2:D10)

=СУММЕСЛИ(C2:C10, "<=9/10/2020", B2:B10)

Когда за логическим оператором следует ссылка на ячейку или другая функция, критерии должен быть представлен в виде строки. Таким образом, вы используете амперсанд (&) для объединения логического оператора и ссылки или функции. Например:

=СУММЕСЛИ(B2:B10, ">"&F1)

=СУММЕСЛИ(C2:D10, "<="&СЕГОДНЯ(), B2:B10)

4. СУММЕСЛИ из другой книги не работает

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

Например, эта формула будет работать нормально, пока открыта Книга1:

=СУММЕСЛИ([Книга1.xlsx]Лист1!$A$2:$A$9,"яблоки",[Книга1.xlsx]Лист1!$ F$2:$F$9)

И он перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что диапазоны, на которые есть ссылки в закрытых книгах, разыменовываются в массивы. И так как массивы не поддерживаются в диапазон и аргументы sum_range , СУММЕСЛИ выдает ошибку #ЗНАЧ! ошибка.

6. СУММЕСЛИ не распознает регистр текста

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

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

Практическая рабочая тетрадь для скачивания

Примеры Excel SUMIF (файл . xlsx)

Вас также может заинтересовать:

7 способов условного суммирования в Excel – Excel Strategies, LLC

7 способов вычислить условное суммирование значений в Excel.

         Excel предлагает разные способы выполнения одной и той же задачи. Это особенно очевидно в случае использования функций Excel, где мы можем просто выбрать ту, которая предлагает наилучшее решение, или, что более реалистично, ту, которую нам удобнее использовать. В качестве примера решим следующий сценарий: Мы предлагаем онлайн-курсы Excel как на нашем внутреннем сайте, так и на платформе Udemy. Udemy взимает с нас 50% комиссионных за все продажи курсов, а также предлагает рекламные тарифы для увеличения нашего объема. В результате мы продаем один и тот же контент по разным ценам. Глядя на показатели продаж на неделе Благодарения, давайте посчитаем общий объем чистых продаж для всех транзакций Udemy (выделено). Давайте используем различные функции Excel для выполнения необходимых расчетов.

Метод 1. Использование функции ПРОМЕЖУТОЧНЫЕ ИТОГОВ при фильтрации строк
         Мы рассмотрели использование функции ПРОМЕЖУТОЧНЫЕ ИТОГ в нашем последнем посте. Этот метод имеет свои ограничения, так как мы должны фактически фильтровать наши данные и не можем выполнять этот расчет на лету. В противном случае, это может быть маршрут, по которому мы можем пойти.

Метод 2. Использование функции СУММЕСЛИ . В нашем примере диапазон критериев — веб-платформа: 9.0003 $C6:$C$22 , нашим условием является используемая веб-платформа: «Udemy» , и диапазон, который мы хотели бы рассчитать, , если найдено совпадение , это Net Sales: $G$6:$ G$22 . Наша формула становится следующей:

=СУММЕСЛИ($C$6:$C$22,"Удеми",$G$6:$G$22)

Метод 3. Использование функции СУММЕСЛИМН несколько условий вместо одного. В нашем примере давайте примем его для использования с единственным условием, которое у нас есть. Однако может не возникнуть практической необходимости использовать функцию СУММЕСЛИМН для суммы одного критерия; при необходимости мы можем просто изменить его для расчета нескольких условий без необходимости переключения используемых функций. Обратите внимание, что параметры в этой функции имеют другой порядок, чем в функции СУММЕСЛИ. Наш sum_range, как и раньше, равен: $G$6:$G$22 . Наш критерий_диапазон1 равен $C$6:$C$22 , и наш критерий «Удеми» . Собрав все части вместе, мы теперь имеем:

=СУММЕСЛИМН($G$6:$G$22,$C$6:$C$22,"Удеми")

          ПРИМЕЧАНИЕ: Все приведенные выше примеры принимают аргументы, которые представляют собой диапазоны (ТОЛЬКО отдельные ячейки или диапазоны ячеек, в то время как в предыдущих примерах в качестве параметров принимаются массивы. Если указан диапазон, они просто преобразуют его в массив.

Метод 4 – Использование функции СУММПРОИЗВ

          =СУММПРОИЗВ(массив1,[массив2]…)

         До Excel 2007 функция СУММПРОИЗВ использовалась для выполнения суммирования по нескольким условиям. Синтаксис этой функции позволяет нам заменять звездочку запятой, что немного упрощает работу с этой функцией. В нашем примере нам нужно «умножить» наш чистый доход на любую запись, найденную как совпадение в столбце веб-платформы для «Udemy» :

. =СУММПРОИЗВ($G$6:$G$22*($C$6:$C$22="Удеми"))

Метод 5. Использование функции ЦСУМ критерии. В нашей базе данных есть весь спектр записей, включая заголовки столбцов: $B$5:$G$22 . Параметр field принимает либо текстовую строку имени столбца, либо номер столбца в диапазоне. Оба: «Чистый доход» и 6 подойдут нам. Наконец, критерии часто требуют от нас настройки некоторых «вспомогательных» ячеек, но в моем случае я удобно использую значение из самой первой записи в нашем диапазоне в качестве моего критерия:

=DSUM($B$5:$G$22,6,$C$5:$C$6)

          ПРИМЕЧАНИЕ. Следующие две функции представляют собой функции массива , и их необходимо вводить с помощью следующей комбинации клавиш: CTRL+SHIFT+ENTER в поле формулы. Для пользователей Mac вам придется использовать КОМАНДА + ВОЗВРАТ .

Способ 6. Используйте функцию СУММ

         Синтаксис очень похож на функцию SUBOTAL, но параметры меняются местами. Нам нужно сначала указать наше условие, а затем «умножить» на диапазон суммы. Не забудьте использовать CTRL+SHIFT+ENTER:

=СУММ(($C$6:$C$22="Удеми")*($G$6:$G$22))

Способ 7. Использование комбинации функций СУММ(ЕСЛИ())

         Мы просто следуем синтаксису функции ЕСЛИ и выполняем суммирование поверх нее. Опять же, вам придется использовать комбинацию CTRL+SHIFT+ENTER, чтобы эта формула работала правильно:

=СУММ(ЕСЛИ($C$6:$C$22="Удеми",$G$6:$G$22,0))

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

Метод 8 (БОНУС!) – Использование сводных таблиц.
         Функция сводных таблиц — это еще один инструмент Excel, который мы можем использовать для расчета требуемых результатов, однако они заслуживают отдельного поста или серии статей.

Примеры с несколькими условиями поиска:

         Использование функции СУММЕСЛИ для одного условия поиска может быть излишним, давайте изменим наши критерии, чтобы включить только со скидкой Удеми курсы. Не стесняйтесь загружать сопутствующую книгу для этого поста и найдите решения ниже. Обратите внимание на создание «вспомогательного» диапазона данных R1:S2 для настройки функции DSUM. Также обратите внимание, что версия оператора IF с массивом не позволяет использовать функцию AND, поэтому мне пришлось использовать подход с вложенной функцией IF.