Суммирование значений с учетом нескольких условий
Excel для Microsoft 365 Excel для Интернета Excel 2019 Excel 2016 Excel 2013 Еще…Меньше
Предположим, что вам нужно свести значения с более чем одним условием, например суммой продаж продуктов в определенном регионе. Это хороший случай для использования функции СУММЕСС в формуле.
Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж «Мясо» (из столбца C) в регионе «Южный» (из столбца A).
Вот формула, с помощью которая можно сопровождать эту формулу:
=СУММЕСС(D2:D11,A2:A11,»Южный»,C2:C11,»Мясо»)
Результат — значение 14 719.
Рассмотрим каждую часть формулы более подробно.
=СУММЕСЛИМН — это арифметическая формула. Она вычисляет числа, которые в этом случае находятся в столбце D. Прежде всего нужно указать расположение чисел.
=СУММЕСЛИМН(D2:D11,
Другими словами, вы хотите, чтобы формула суммировала числа в этом столбце, если они соответствуют определенным условиям. Это диапазон ячеок является первым аргументом в этой формуле — первым элементом данных, который требуется функции в качестве входных данных.
Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие («Южный»). Обратите внимание на запятую между аргументами:
=СУММЕСЛИМН(D2:D11;A2:A11;»Южный»;
Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные.
Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.
=СУММЕСЛИМН(D2:D11;A2:A11,»Южный»;C2:C11,»Мясо»)
Если вы ввели в Excel функцию СУММЕСС, если вы не помните аргументов, справка готова. После того как вы введете =СУММЕСС(, под формулой появится автозавершенная формула со списком аргументов в правильном порядке.
На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится «Южный» (критерий1).
По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.
Если вы нажмете кнопку СУММЕСС в автозавершении формул, откроется статья с дополнительной справкой.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функцией СУММЕСС, вот примеры данных и формула, в которую она используется.
Вы можете работать с образцами данных и формулами прямо в этой Excel в Интернете книге. Изменяйте значения и формулы или добавляйте свои собственные, чтобы увидеть, как мгновенно изменятся результаты.
Скопируйте все ячейки из приведенной ниже таблицы и вставьте их в ячейку A1 нового листа Excel. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.
|
Регион |
Продавец |
Что следует ввести |
Продажи |
|
Южный |
Орехов |
Напитки |
3571 |
|
Западный |
Егоров |
Молочные продукты |
3338 |
|
Восточный |
Песоцкий |
Напитки |
5122 |
|
Северный |
Песоцкий |
Молочные продукты |
6239 |
|
Южный |
Маринова |
Сельхозпродукты |
8677 |
|
Южный |
Егоров |
Мясо |
450 |
|
Южный |
Егоров |
Мясо |
7673 |
|
Восточный |
Песоцкий |
Сельхозпродукты |
664 |
|
Северный |
Егоров |
Сельхозпродукты |
1500 |
|
Южный |
Маринова |
Мясо |
6596 |
|
Формула |
Описание |
Результат |
|
|
‘=СУММЕСС(D2:D11,A2:A11, |
Суммирует продажи по мясо в |
=СУММЕСС(D2:D11,A2:A11, |
Примечания:
-
Нужны другие примеры? Дополнительные узнать об этом можно в статье о функции СУММЕФС.
-
Если вы хотите подсчитать итоговое значение только для одного диапазона с учетом значения в другом диапазоне, используйте функцию СУММЕСЛИМН.
Нужна дополнительная помощь?
Простые логические функции такие как ЕСЛИ обычно предназначены для работы с одним столбцом или одной ячейкой. 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 символа от начала строки. Нет необходимости использовать сложные формулы с функцией =ЛЕВСИМВ() и т.д. Достаточно лишь воспользоваться операторами многозначных символов чтобы сформулировать простой и лаконичный запрос к базе данных с минимальными нагрузками на системные ресурсы.
Skip to content
В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Хорошо знакомая нам функция ЕСЛИ позволяет производить вычисления в зависимости от выполнения условия. Функция СУММ позволяет складывать числовые значения. А что если нам нужна формула ЕСЛИ СУММ? Для этого случая в Excel имеется специальная функция СУММЕСЛИ.
Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif в английской версии) в таблицах Excel. Начнем с самых простых случаев, как можно использовать при этом знаки подстановки, назначить диапазон суммирования, работать с числами, текстом и датами. Особо остановимся на том, как использовать сразу несколько условий. И, конечно, мы применим новые знания на практике, рассмотрев несложные примеры.
- Как пользоваться СУММЕСЛИ в Excel – синтаксис
- Примеры использования функции СУММЕСЛИ в Excel
- Сумма если больше чем, меньше, или равно
- Критерии для текста.
- Подстановочные знаки для частичного совпадения.
- Точная дата либо диапазон дат.
- Сумма значений, соответствующих пустым либо непустым ячейкам
- Сумма по нескольким условиям.
- Почему СУММЕСЛИ у меня не работает?
Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.
Как пользоваться СУММЕСЛИ в Excel – синтаксис
Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.
Синтаксис функции выглядит следующим образом:
=СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования])
Диапазон – это область, которую мы исследуем на соответствие определённому значению.
Критерий – это значение или шаблон, по которому мы производим отбор чисел для суммирования.
Значение критерия может быть записано прямо в самой формуле. В этом случае не забывайте, что текст нужно обязательно заключать в двойные кавычки.
Также он может быть представлен в виде ссылки на ячейку таблицы, в которой будет указано требуемое ограничение. Безусловно, второй способ является более рациональным, поскольку позволяет гибко менять расчеты, не редактируя выражение.
Диапазон_суммирования — третий параметр, который является необязательным, однако он весьма полезен. Благодаря ему мы можем производить поиск в одной области, а суммировать значения из другой в соответствующих строках.
Итак, если он указан, то расчет идет именно по его данным. Если отсутствует, то складываются значения из той же области, где производился поиск.
Чтобы лучше понять это описание, рассмотрим несколько простых задач. Надеюсь, что они будут понятны не только «продвинутым» пользователям, но и подойдут для «чайников».
Примеры использования функции СУММЕСЛИ в Excel
Сумма если больше чем, меньше, или равно
Начнем с самого простого. Предположим, у нас есть данные о продажах шоколада. Рассчитаем различные варианты продаж.
В I3 записано:
=СУММЕСЛИ(D2:D21;I2)
D2:D21 – это координаты, в которых мы ищем значение.
I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.
Поскольку третий параметр функции не указан, то мы сразу складываем отобранные числа. Область поиска будет одновременно являться и диапазоном суммирования.
Кроме того, в качестве задания для отбора нужных значений можно указать текстовое выражение, состоящее из знаков >, <, <>, <= или >= и числа.
Можно указать его прямо в формуле, как это сделано в I13
=СУММЕСЛИ(D2:D21;«<144»)
То есть подытоживаем все заказы, в которых количество меньше 144.
Но, согласитесь, это не слишком удобно, поскольку нужно корректировать саму формулу, да и условие еще нужно не забыть заключить в кавычки.
В дальнейшем мы будем стараться использовать только ссылку на критерий, поскольку это значительно упрощает возможные корректировки.
Критерии для текста.
Гораздо чаще встречаются ситуации, когда поиск нужно проводить в одном месте, а в другом — суммировать данные, соответствующие найденному.
Чаще всего это необходимо, если необходимо использовать отбор по определённым словам. Ведь текстовые значения складывать нельзя, а вот соответствующие им числа – можно.
Как простой прием использования формулы СУММ ЕСЛИ в Эксель таблицах, рассчитаем итог по выполненным заказам.
В I3 запишем выражение:
=СУММЕСЛИ(F2:F21;I2;E2:Е21)
F2:F21 – это область, в которой мы отбираем подходящие значения.
I2 – здесь записано, что именно отбираем.
E2:E21 – складываем числа, соответствующие найденным совпадениям.
Конечно, можно указать параметр отбора прямо в выражении:
=СУММЕСЛИ(F2:F21;”Да”;E2:Е21)
Но мы уже договорились, что так делать не совсем рационально.
Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.
Подстановочные знаки для частичного совпадения.
При работе с текстовыми данными часто приходится производить поиск по какой-то части слова или фразы.
Вернемся к нашему случаю. Определим, сколько всего было заказов на черный шоколад. В результате, у нас есть 2 подходящих наименования товара. Как учесть их оба? Для этого есть понятие неточного соответствия.
Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.
Символ “?” позволяет заменить собой один любой символ.
Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).
Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –
=СУММЕСЛИ(C2:C21;I2;E2:Е21) , где в E2 записано *[слово]*
либо
=СУММЕСЛИ(C2:C21;»*»&I2&»*»;E2:E21)
где * вставлены прямо в выражение и «склеены» с нужным текстом.
Давайте потренируемся:
- “*черный*” — мы ищем фразу, в которой встречается это выражение, а до него и после него – любые буквы, знаки и числа. В нашем случае этому соответствуют “Черный шоколад” и “Супер Черный шоколад”.
- “Д?” — необходимо слово из 2 букв, первая из которых “Д”, а вторая – любая. В нашем случае подойдет “Да”.
- “???” — найдем слово из любых 3 букв
=СУММЕСЛИ(F2:F21;”???”;E2:E21)
Этому требованию соответствует “Нет”.
- “???????*” — текст из любых 7 и более букв.
=СУММЕСЛИ(B2:B21;“???????*”;E8:E28)
Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.
- “З*” — мы выбираем фразу, первая буква которой “З”, а далее – любые буквы, знаки и числа. Это “Золотой” и “Зеленый”.
- “Черный*” — подходит фраза, которая начинаются именно с этого слова, а далее – любые буквы, знаки и числа. Подходит “Черный шоколад”.
Примечание. Если вам необходимо в качестве задания для поиска применять текст, который содержит в себе * и ?, то используйте знак тильда (~), поставив его перед этими символами. Тогда * и ? будут считаться обычными символами, а не шаблоном:
=СУММЕСЛИ(B2:B21;“*~?*”;E8:E28)
Важное замечание. Если в вашем тексте для поиска встречается несколько знаков * и ?, то тильду (~) нужно поставить перед каждым из них. К примеру, если мы будем искать текст, состоящий из трех звездочек, то формулу ЕСЛИ СУММ можно записать так:
=СУММЕСЛИ(B2:B21;“~*~*~*”;E8:E28)
А если текст просто содержит в себе 3 звездочки, то можно наше выражение переписать так:
=СУММЕСЛИ(B2:B21;“*~*~*~**”;E8:E28)
Точная дата либо диапазон дат.
Если нам нужно найти сумму чисел, соответствующих определённой дате, то проще всего в качестве критерия указать саму эту дату.
Примечание. При этом не забывайте, что формат указанной вами даты должен соответствовать региональным настройкам вашей таблицы!
Обратите внимание, что мы также можем здесь вписать ее прямо в формулу, а можем использовать ссылку.
Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.
=СУММЕСЛИ(A2:A21;I1;E2:E21)
или же
=СУММЕСЛИ(A2:A21;СЕГОДНЯ();E2:E21)
Рассчитаем за вчерашний день.
=СУММЕСЛИ(A2:A21;СЕГОДНЯ()-1;E2:E21)
СЕГОДНЯ()-1 как раз и будет «вчера».
Складываем за даты, которые предшествовали 1 февраля.
=СУММЕСЛИ(A2:A21;»<«&»01.02.2020»;E2:E21)
После 1 февраля включительно:
=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21)
А если нас интересует временной интервал «от-до»?
Мы можем рассчитать итоги за определённый период времени. Для этого применим маленькую хитрость: разность функций СУММЕСЛИ. Предположим, нам нужна выручка с 1 по 4 февраля включительно. Из продаж после 1 февраля вычитаем все, что реализовано после 4 февраля.
=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21) — СУММЕСЛИ(A2:A21;»>=»&»04.02.2020″;E2:E21)
Сумма значений, соответствующих пустым либо непустым ячейкам
Случается, что в качестве условия суммирования нужно использовать все непустые клетки, в которых есть хотя бы одна буква, цифра или символ.
Рассмотрим ещё один вариант использования формулы СУММ ЕСЛИ в таблице Excel, где нам необходимо подсчитать заказы, в которых нет отметки о выполнении, а также сколько было вообще обработанных заказов.
Если критерий указать просто “*”, то мы учитываем для подсчета непустые ячейки, в которых имеется хотя бы одна буква или символ (кроме пустых).
=СУММЕСЛИ(F2:F21;»*»;E2:E21)
Точно такой же результат даёт использование вместо звездочки пары знаков «больше» и «меньше» — <>.
=СУММЕСЛИ(F2:F21;»<>»;E2:E21)
Теперь рассмотрим, как можно находить сумму, соответствующую пустым ячейкам.
Для того, чтобы найти пустые, в которых нет ни букв, ни цифр, в качестве критерия поставьте парные одинарные кавычки ‘’, если значение критерия указано в ячейке, а формула ссылается на неё.
Если же указать на отбор только пустых ячеек в самой формуле СУММ ЕСЛИ, то впишите двойные кавычки.
=СУММЕСЛИ(F2:F21;«»;E2:E21)
Сумма по нескольким условиям.
Функция СУММЕСЛИ может работать только с одним условием, как мы это делали ранее. Но очень часто случается, что нужно найти совокупность данных, удовлетворяющих сразу нескольким требованиям. Сделать это можно как при помощи некоторых хитростей, так и с использованием других функций. Рассмотрим все по порядку.
Вновь вернемся к нашему случаю с заказами. Рассмотрим два условия и посчитаем, сколько всего сделано заказов черного и молочного шоколада.
1. СУММЕСЛИ + СУММЕСЛИ
Все просто:
=СУММЕСЛИ($C$2:$C$21;»*»&H3&»*»;$E$2:$E$21)+СУММЕСЛИ($C$2:$C$21;»*»&H4&»*»;$E$2:$E$21)
Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).
Это самое простое решение, но не самое универсальное и далеко не единственное.
2. СУММ и СУММЕСЛИ с аргументами массива.
Вышеупомянутое решение очень простое и может выполнить работу быстро, когда критериев немного. Но если вы захотите работать с несколькими, то она станет просто огромной. В этом случае лучшим подходом является использование в качестве аргумента массива критериев. Давайте рассмотрим этот подход.
Вы можете начать с перечисления всех ваших условий, разделенных запятыми, а затем заключить итоговый список, разделенный точкой с запятой, в {фигурные скобки}, который технически называется массивом.
Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:
СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21)
Поскольку здесь использован массив критериев, то результатом вычислений также будет массив, состоящий из двух значений.
А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.
=СУММ(СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21))
Важно, что результаты вычислений в первом и втором случае совпадают.
3. СУММПРОИЗВ и СУММЕСЛИ.
А если вы предпочитаете перечислять критерии в какой-то специально отведенной для этого части таблицы? Можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в заданных массивах и возвращает сумму этих произведений.
Вот как это будет выглядеть:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;H3:H4;E2:E21))
в H3 и H4 мы запишем критерии отбора.
Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;{«*черный*»;»*молочный*»};E2:E21))
Результат, возвращаемый в обоих случаях, будет идентичен тому, что вы наблюдаете на скриншоте.
Важное замечание! Обратите внимание, что все перечисленные выше три способа производят расчет по логическому ИЛИ. То есть, нам нужны продажи шоколада, который будет или черным, или молочным.
Почему СУММЕСЛИ у меня не работает?
Этому может быть несколько причин. Иногда ваше выражение не возвращает того, что вы ожидаете, только потому, что тип данных в ячейке или в каком-либо аргументе не подходит для нее. Итак, вот что нужно проверить.
1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.
Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив {1,2,3}, Excel выдаст сообщение об ошибке.
Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)
Неверно : =СУММЕСЛИ({1,2,3}, «цвет», C1:C3)
2. Ошибка при суммировании значений из других листов или рабочих книг.
Как и любая другая функция Excel, СУММЕСЛИ может ссылаться на другие листы и рабочие книги, если они в данный момент открыты.
Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:
=СУММЕСЛИ([Книга1.xlsx]Лист1!$A$2:$A$9,»яблоки»,[Книга1.xlsx]Лист1!$F$2:$F$9)
Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.
3. Чтобы избежать проблем, убедитесь, что диапазоны данных и поиска имеют одинаковый размер.
Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.
Примеры расчета суммы:
Суммирование значений с учетом нескольких условий
Смотрите также одной строке, а или от результата аргументу появляется возможностьФункция СУММЕСЛИ анализирует каждое шагом 2. Т.е. по диапазону и Григорьева, то вСУММЕСЛИ (SUMIF) дни. В ячейкефункций вФункция «СУММЕСЛИМН» в статье «Суммирование ячеек данные. «>32», B5, «32», a11; D2: D11, автозавершение формул появитсяПримечание: платежи разделены прерывистой вычисления формулы. проверять условия по значение ячейки в
5, 7, 9… критерию. ячейке этого столбца из категории В11 будем писатьExcel «СУММЕСЛИ»Excel с датами в Excel поСтоимость имущества
«яблоки» или СЕГОДНЯ().«Южно» C2: C11, под формулы, вМы стараемся как линией, таким образомПоместите оператор сравнения в одному диапазону, а диапазоне B2:B12 иПредположим, нам нужно подсчитать будет значение 1,
Математические (Math&Trig) название нужного продукта.
. Эта функция выбирает
.
условию».Комиссионные
Важно: «Мясо») списке аргументов в можно оперативнее обеспечивать необходимая информация о двойные кавычки и суммировать другой.
проверяет соответствует ли
сумму заработных платРассмотрим простейший пример, который иначе — 0.. Выделяем пустую ячейку Сейчас мы написали и складывает данныеНужно сложить данныеПример 1.Данные Все текстовые условия иСуммирует продаж мяса в их правильном порядке. вас актуальными справочными
следующем платеже располагается соедините его символомНиже на рисунке представлена оно заданному условию за январь всех наглядно продемонстрирует, как Формула, которую надо для результата, жмем «сметана». Получилось так. из таблицы по из таблицы поПосчитаем сумму, если
1 000 000 ₽
условия с логическимиСтолбец C вЕсли посмотреть на изображение
материалами на вашем в первой строке амперсант (&) вместе таблица отчета продаж (указанному во втором продавцов-женщин. У нас использовать функцию СУММЕСЛИ ввести в этот кнопкуЭту функцию можно определенным условиям, которые нескольким условиям, в в ячейке наименования70 000 ₽
и математическими знаками
южном использования функции автозавершения языке. Эта страница после данной линии. со ссылкой на по клиентам за аргументе функции). Если есть два условия. и насколько удобной столбец очень простая:fx
использовать для заполнения мы пишем в т. ч. по товара написаны не2 500 000 ₽ необходимо заключать вобласть в столбце формул и списка переведена автоматически, поэтомуПока мне удалось ячейку или с месяц. Напротив, каждого значение меньше чем Сотрудник должен быть: она может оказаться=(A2=»Копейка»)*(B2=»Григорьев»)
в строке формул, бланка, выбрав данные формуле. датам. одним словом, а2 000 000 ₽ двойные кавычки ( A (результат — аргументов, то в ее текст может только вытянуть в формулой. клиента указана сумма,
0, тогда условиепродавцом; при решении определенныхЛогические равенства в скобках
Попробуйте попрактиковаться
находим функцию из большой таблицы.РазберемУдобно писать даты несколькими. Здесь нужно
140 000 ₽» 14 719). нашем примере диапазон_суммирования содержать неточности и отдельные ячейки (для=СУММЕСЛИ(A1:A10;»»&СЕГОДНЯ()) на которую совершил выполнено и данное
женщиной. задач. дают значения ИСТИНАСУММЕСЛИ Смотрите статью «Какпримеры функции «СУММЕСЛИ» в в отдельных ячейках. вставить в условие
|
3 000 000 ₽ |
). Если условием является |
= SUMIFS(D2:D11,A2:A11, |
— это диапазон |
|
грамматические ошибки. Для |
удобства построения условия) |
Значение содержит фрагмент строки |
покупки клиент на |
|
число учитывается в |
Значит, будем применять команду |
Имеем таблицу, в которой |
или ЛОЖЬ, что |
|
в списке: |
посчитать в Excel |
Excel». |
Так сможем посчитать |
|
функции специальные символы. |
210 000 ₽ |
число, использовать кавычки |
«Южно» C2: C11, |
|
D2:D11, представляющий собой |
нас важно, чтобы |
значения времени и |
Используйте операторы многозначных символов |
|
протяжении месяца. Некоторые |
общей итоговой сумме. |
СУММЕСЛИМН. |
указаны фамилии сотрудников, |
|
для Excel равносильно |
Жмем |
ячейки в определенных |
У нас такая |
|
данные любого периода, |
Таблица такая. |
4 000 000 ₽ |
не требуется. |
|
«Мясо») |
столбец с числами, |
эта статья была |
сумму каждого платежа |
|
и поместите их |
клиенты за месяц |
Числовые значения больше |
Прописываем аргументы. |
|
их пол и |
1 и 0. |
ОК |
|
|
строках». таблица. |
280 000 ₽ |
в двойные кавычки |
диапазон суммирования – ячейки зарплата, начисленная за
-
Таким образом, поскольку и вводим ее аргументы:
-
Какими способами можноПервый пример. этих отдельных ячейках. менеджер Васечкин продалФормула . Необязательный аргумент. Ячейки,
support.office.com
Функция СУММЕСЛИ
просуммировать; диапазон_условия1 — вас уделить паруТеперь надо понять:=СУММЕСЛИ(A1:A10;»*кг*»;B1:B10) покупок, поэтому их игнорируются функцией. Проигнорированы с зарплатой; январь-месяц. Если нам мы перемножаем этиДиапазон сделать самостоятельно любойСумма по условиюВ ячейке С11 яблок красных, зеленых,Описание значения из которыхДополнительные примеры можно найти это диапазон A2.A11, секунд и сообщить,1. Как данную
Во втором аргументе критериев наименование дублируется в также текстовые значениядиапазон условия 1 – нужно просто посчитать выражения, единица в- это те бланк в Excel,Excel. напишем дату начала других покупателю Иванову.Результат суммируются, если они
в статьеФункция СУММЕСЛИМН. представляющий собой столбец помогла ли она
формулу вставить в условий можно использовать
-
списке. Необходимо узнать и пустые ячейки. ячейки с указанием общее количество денег, конечном счете получится ячейки, которые мы смотрите в статьеНам нужно посчитать периода.В ячейку Н4=СУММЕСЛИ(A2:A5;»>160000″;B2:B5) отличаются от ячеек,Если вы хотите подсчитать
-
с данными, в вам, с помощью каждую необходимую строку разные функции и
Синтаксис
на какую общую
В приведенном примере сначала должности сотрудника; которые требуется выдать
-
только если оба проверяем на выполнение «Как сделать бланк на какую суммуВ ячейке С12 вставляем функцию «СУММЕСЛИМН».Сумма комиссионных за имущество указанных в качестве итоговое значение только котором находится кнопок внизу страницы. (желательно скриптом, привязанным формулы. Ниже на сумму купил товара проверяется значения ячейки
-
условия 1 – продавец; работникам, мы используем условия выполняются. ТеперьКритерия в Excel» здесь. продал продукты первый напишем дату окончания В условии функции стоимостью больше 1диапазона для одного диапазонаусловие1
Для удобства также к кнопке); как рисунке изображен список «Клиент3» в данном B2 и такдиапазон условия 2 – функцию СУММ, указав стоимости продаж осталось. В нашем случаеЧетвертый пример.
-
отдел за все периода, данные в напишем «*яблоки*». По 600 000 ₽.. Если аргумент с учетом значения— “Южный”. приводим ссылку на идентифицировать необходимую строку дат и присвоенных месяце. Здесь весьма как оно больше ячейки с указанием диапазоном все заработные умножить на значения — это диапазон
-
Сумма дни. В ячейке котором мы хотим этому условию Excel630 000 ₽диапазон_суммирования в другом диапазоне,По мере того, как оригинал (на английском — вопрос (либо им значений. Важно уместно использовать формулу чем 0 – пола сотрудника; платы. получившегося столбца и с фамилиями менеджеровExcel, если несколько условий. С9 устанавливаем функцию
Примечания
-
посчитать. будет искать все=СУММЕСЛИ(A2:A5; «>160000»)опущен, Excel суммирует используйте функцию СУММЕСЛИМН. вы вводите формулу,
-
языке) . по разделительной линии, отметить что сегодня с заполнением третьего будет проигнорировано. Далееусловие 2 – женскийНо как быть, если просуммировать отобранное в продаж.Если нужно посчитать «СУММЕСЛИ».В диалоговом окне яблоки. Диалоговое окноСумма по имуществу стоимостью ячейки, указанные вПримечание: в автозавершении формулыДопустим, вам нужно суммировать
|
либо по тексту |
на момент написания |
аргумента функции СУММЕСЛИ: |
|
проверяется ячейка B3. |
(ж). |
нам нужно быстро |
|
зеленой ячейке: |
Критерий |
сумму по двум |
|
В |
условия функции «СУММЕСЛИМН» |
функции заполнили так. |
|
больше 1 600 000 |
аргументе |
Мы стараемся как можно |
появятся остальные аргументы значения с более начала строки..); статьи дата –Как видно на всех В ней числовоеИтог: все продавцы-женщины в посчитать заработные платыЕсли вы раньше не
Примеры
Пример 1
- это то, и более столбцам,Excel функция «СУММЕСЛИ» по датам написалиФормула получится такая. ₽.диапазон оперативнее обеспечивать вас (здесь они не чем одного условия,2. Каким образом «03.11.2018». Чтобы суммировать выше приведенных примерах
|
значение меньше нуля, |
январе получили в |
только продавцов? В |
|
сталкивались с такой |
что мы ищем |
то можно в |
|
находится на закладке |
так. |
|
|
=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;A2:A8;»Иванов»;C2:C8;»*яблоки*») |
9 000 000 ₽ |
|
|
(те же ячейки, |
актуальными справочными материалами |
|
|
показаны); |
например сумму продаж |
разделить диапазоны документов |
|
числовые значения только |
во втором аргументе значит условие выполнено, сумме 51100 рублей. |
дело вступает использование |
|
замечательной возможностью Excel |
в предыдущем указанном формулу вставить несколько «Формулы» в разделе |
Получилась такая формула. |
|
Получилось, что всяких |
=СУММЕСЛИ(A2:A5;300000;B2:B5) к которым применяется на вашем языке. |
диапазон_условия2 |
|
товара в определенной |
по дебету и по сегодняшней дате указывается строка текста поэтому оно добавляется |
Функции СУММЕСЛИ и СУММЕСЛИМН |
Пример 2
функции СУММЕСЛИ. как формулы массива, диапазоне. Разрешается использовать функций «СУММЕСЛИ». Например, «Библиотека функций». Нажимаем=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;A2:A8;»Иванов»;D2:D8;»>=»&C11;D2:D8;» Здесь мы яблок Васечкин продалСумма комиссионных за имущество условие). Эта страница переведена— это диапазон области. Это хороший кредиту (по сути, используйте формулу: с условием, а
|
к общей сумме. |
хороши тем, что |
Прописываем аргументы. |
|
то советую почитать |
символы * (звездочка) |
в нашу таблицу |
|
на «Математические» и |
два раза указали |
Иванову на сумму |
|
стоимостью 3 000 000 |
В аргументе |
автоматически, поэтому ее |
|
C2:C11, представляющий собой |
обращения для использования |
|
|
это диапазон платежей |
Чтобы суммировать только значения |
не логическое выражение |
|
Данный процесс повторяется |
они автоматически подстраиваются |
Диапазоном в данном случае |
|
предварительно про них |
и ? (вопросительный |
из примера добавим |
|
выбираем функцию «СУММЕСЛИ». |
один и тот 7897 рублей. |
₽. |
|
условие |
текст может содержать столбец с данными, |
функции СУММЕСЛИМН в |
|
между двумя строками |
от сегодняшнего дня как в функции для каждой ячейки. под изменение условий. |
будет являться список |
|
много хорошего здесь. |
знак) как маски столбец В сПоявится диалоговое окно |
же диапазон дат, |
Пример 2.
support.office.com
Примеры функции «СУММЕСЛИМН» в Excel.
210 000 ₽можно использовать подстановочные неточности и грамматические в котором находится формуле. с текстом «реестр включительно и до ЕСЛИ. На практике В результате его Т.е. мы можем всех должностей сотрудников, Ну, а в или символы подстановки. новыми данными. Формулу «Аргументы функции». Заполняем
но указали разные
Нужно посчитать, сколько=СУММЕСЛИ(A2:A5;»>» &C2;B2:B5) знаки: вопросительный знак ошибки. Для насусловие2Просмотрите в этом примере, документов по дебету/кредиту»),
конца периода времени
использования функции СУММЕСЛИ выполнения суммированы значения изменить данные в потому что нам
нашем случае задача Звездочка подменяет собой в ячейке напишем его так. условия (от даты продано всех фруктовСумма комиссионных за имущество, (
важно, чтобы эта
— “Мясо”.
в котором у чтобы сумма первого воспользуйтесь оператором «больше этот второй логический
ячеек B3, B6,
ячейках, и суммы нужно будет определить решается одной формулой: любое количество любых такую. =СУММЕСЛИ(A2:A7;B10;C2:C7)+СУММЕСЛИ(A2:A7;B10;D2:D7)В строке «Диапазон» от той, что
на букву «Я». стоимость которого превышает? статья была вам
Если вы нажмете нас есть двумя диапазона была в или равно» (>=) аргумент с условием B7, B8 и будут изменяться вместе сумму заработных плат.=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)
символов, вопросительный знакЭтой формулой мы указываем диапазон первого в ячейке С11 В условиях функции значение в ячейке) и звездочку ( полезна. Просим васСУММЕСЛИМН
условиями: необходимо узнать одной итоговой ячейке, вместе с соответственной может содержать:
B10, а остальные с ними. Например,
Поэтому проставляем E2:E14.После ввода этой формулы — один любой считаем выборочные данные
столбца, с номерами
и больше, до «СУММЕСЛИМН» напишем так. C2.* уделить пару секунд
в автозавершении формулы, сумму продаж а второго - функцией =СЕГОДНЯ(). Формуласроки с текстовым значением; ячейки не учитываются при подсчете заработныхКритерий выбора в нашем необходимо нажать не
символ. Так, например, из столбцов C отделов, п. ч. даты, что в «=я*» Вся формула490 000 ₽). Вопросительный знак соответствует и сообщить, помогла откроется статья, справкой.
мяса в другой.
c операторам (>=):числовые значения; в итоговой сумме. плат оказалось, что случае – продавец. Enter, как обычно, чтобы найти все и D по мы хотим посчитать ячейке С12 и
будет такая. =СУММЕСЛИМН(D2:D8;C2:C8;»=я*»)
Скопируйте образец данных из одному любому символу, ли она вам,
Если вы хотите поэкспериментироватьвФайл с примером=»&СЕГОДНЯ();B2:B10)/B11′ class=’formula’>логические выражения;
Обратите внимание что ниже мы забыли учесть Заключаем слово в а продажи у менеджеров первому отделу.
данные только по меньше).Этой формулой посчитаются
приведенной ниже таблицы а звездочка — любой с помощью кнопок с функцией СУММЕСЛИМН,южном
прилагается.Во втором логическом аргументеформулы с функциями; результата суммирования отрицательных
одну сотрудницу, которая
кавычки и ставимCtrl + Shift + с фамилией изФункцией «СУММЕСЛИ» в первому отделу. МыПолучится так. все слова в и вставьте его последовательности символов. Если внизу страницы. Для то некоторые образцырайоне (из столбца
Raufbold
критериев условий функцииссылки на ячейки. чисел находится формула работает продавцом. Мы
вторым аргументом. Enter пяти букв, можноExcel
указали столбец A.
Поменяем в формуле фамилию столбце, которые начинаются в ячейку A1 требуется найти непосредственно удобства также приводим
данных и формулы,
A) (из столбца: А Текст по СУММЕСЛИ можно применять
Далее рассмотрим больше примеров
суммирования положительных чисел. можем добавить ещеДиапазон суммирования – это- тогда Excel использовать критерий
можно посчитать, только,
В строке «Критерий»
менеджера «Сергеева» на на букву «Я». нового листа Excel. вопросительный знак (или
ссылку на оригинал использующей функцию. C).
столбцам и затем
многозначные символы – составления условий для Единственное отличие между одну строчку через заработные платы, потому воспримет ее как????? отрицательные или, только, указываем, какой отдел «Васечкин». Проверяем формулу.Если нужно посчитать, что Чтобы отобразить результаты
звездочку), необходимо поставить (на английском языке).Вы можете работать сВот формулу можно использовать остальные расчеты? (?)и(*) для составления второго логического аргумента ними — это
правую кнопку мыши что нам нужно формулу массива и. А чтобы найти все положительные числа. Об из столбца А Получилось так. продал формул, выделите их перед ним знакФункция
excel-office.ru
Функция «СУММЕСЛИ» в Excel.
демонстрационными данными непосредственно для acomplish это:kalbasiatka относительных неточных запросов. функции СУММЕСЛИ более обратный оператор сравнения и команду ВСТАВИТЬ. узнать сумму зарплат сам добавит фигурные продажи менеджеров, у
этом способе, читайте нам нужно посчитать. Формула работает. Можно менять
именно и нажмите клавишу
«тильда» (
СУММЕСЛИ в этой книге
=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»): Ну, сделать условное Знак вопроса (?) детально. во втором аргументеУ нас появилась дополнительная всех продавцов. Поэтому
скобки. Вводить скобки которых фамилия начинается в статье «Сумма Здесь можно указать даты периода вВасечкин и F2, а затем —
~используется, если необходимо веб-приложения Excel Online.
Результатом является значение 14,719. суммирование ячеек, у – следует читатьВторой аргумент функции называется где указывается условие строчка. Сразу обращаем F2:F14. с клавиатуры не
на букву «П», отрицательных чисел в не только номер ячейках С11 иименно клавишу ВВОД. Кроме). просуммировать значения диапазона, Изменяйте значения иРассмотрим изучить все части которых время равно как любой символ, «Критерий». Данный логический для суммирования – внимание, что диапазонПолучилось 92900. Т.е. функция надо. Легко сообразить,
а заканчивается на Excel». отдела (например, 1), С12, т.д.Иванову, то добавим того, вы можетеФункция СУММЕСЛИ возвращает неправильные
соответствующие указанному условию. формулы или добавляйте
формулы. или меньше 17:00
а звездочка (*) аргумент используется и вместо строки «0» условий и суммирования автоматически проработала список что этот способ «В» — критерийЧтобы сложить и но и текст,Пример 5. эти условия в
настроить ширину столбцов
результаты, если она
Предположим, например, что свои собственные, чтобы
= СУММЕСЛИМН является формулой (надо будет количество – это строка в других подобных (больше чем ноль). автоматически расширился до должностей, выбрала из (как и предыдущий)П*В удалить дубли в символы, адрес отдельной
Можно в условиях функцию, как рассмотрели
в соответствии с используется для сопоставления
в столбце с увидеть, как мгновенно
арифметического. Он вычисляет
символов до 5 из любого количества
логических функциях: СУММЕСЛИМН, Теперь мы можем 15 строки. них только продавцов легко масштабируется на. Строчные и прописные таблице, тоже применяем ячейки с номером
функции «СУММЕСЛИМН» указать это выше. содержащимися в них строк длиннее 255 символов числами необходимо просуммировать изменятся результаты. числа, которые находятся увеличить) — дело
любых символов или СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ убедиться в том,Копируем данные сотрудника и и просуммировала их три, четыре и
буквы не различаются.
функцию «СУММЕСЛИ». Смотрите отдела, код, т.
и символы «>»Но, у нас данными. или применяется к только значения, превышающиеСкопируйте все ячейки из в этом случае нехитрое, так же пустая строка. Например, и СРЗНАЧЕСЛИМН. В что дебет с вставляем их в
зарплаты. т.д. условий безДиапазон_суммирования статью «Как сложить д. Какие символы
и « Получится в таблице, естьКатегория строке #ЗНАЧ!. 5. Для этого приведенной ниже таблицы в столбце D. как и итоговый нам необходимо просуммировать
каждом случаи аргумент кредитом сходится балансовое общий перечень. СуммыАналогично можно подсчитать зарплаты каких-либо ограничений.- это те и удалить дубли
можно использовать в такая формула. строка «Красные яблоки».ПродуктыАргумент можно использовать следующую и вставьте их Первым делом нужно расчет. Основная проблема
только защитные краски-лаки заполняется согласно одних сальдо будет равно в итоговых ячейках всех менеджеров, продавцов-кассировВ категории ячейки, значения которых в Excel». формуле, что они
=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;E2:E8;»>500″) Они по этойОбъем продаждиапазон_суммирования формулу: в ячейку A1
указать расположение номера: — сделать задачу, с кодом 3 и тех же нулю если сложить изменились. Функции среагировали и охранников. КогдаБазы данных (Database) мы хотим сложить,Ещё примеры этой означают, смотрите вЗдесь последний критерий
excel-office.ru
Выборочные вычисления по одному или нескольким критериям
Постановка задачи
формуле не посчитаются,Овощи
может не совпадать=СУММЕСЛИ(B2:B25;»>5″) нового листа Excel.=СУММЕСЛИМН(D2:D11;
Способ 1. Функция СУММЕСЛИ, когда одно условие
не вручную расставляя английские буквы в правил составления логических арифметически в ячейке на появление в табличка небольшая, кажется, можно найти функцию т.е. нашем случае - функции смотрите в статье «Символы в написали так – п.ч. первое словоПомидоры по размерам сЭто видео — часть Вы можете отрегулироватьДругими словами нужно ввести формулы, а именно начале наименования: условий. Другими словами, B16 формулой =B15+B14.
диапазоне еще одного что все можноБДСУММ (DSUM)
- стоимости заказов. статье «Функция «СУММЕСЛИ» формулах Excel» тут. «>500». в ячейке начинается23 000 ₽ аргументом учебного курса сложение ширину столбцов, чтобы
- формулу для суммирования автоматизировать процесс.Суммируются все значения ячеек для всех этих продавца-женщины. сосчитать и вручную,, которая тоже способнаЕсли условий больше одного в Excel, примеры»В строке «ДиапазонПример 6. не с буквыОвощидиапазон чисел в Excel. формулы лучше отображались. чисел в столбце,Raufbold в диапазоне B2:B16 функций второй аргументДругой пример, когда намАналогично можно не только но при работе решить нашу задачу. (например, нужно найти здесь. В этой суммирования» мы указываемЕсли посчитать в
- «Я».Сельдерей. При определении фактическихСоветы:Регион если они удовлетворяют
Способ 2. Функция СУММЕСЛИМН, когда условий много
: В файле ручками в соответствии со с критерием условий нужно отдельно суммировать добавлять, но и со списками, в Нюанс состоит в сумму всех заказов статье мы рассмотрели, диапазон столбца с непустых ячейках, тоЧтобы посчитать и55 000 ₽ ячеек, подлежащих суммированию, Продавец условиям. Что диапазон напишите, что и значениями в ячейках является логическим выражением цены на группы удалять какие-либо строки которых по несколько том, что для
Григорьева для «Копейки»), как использовать символы, данными, которые нужно ставим последний критерий» эти, «красные яблоки»,Фрукты в качестве начальнойПри необходимости условия можноЧто следует ввести ячеек — это где должно быть
диапазона A2:A16, в возвращающим результат ИСТИНА товаров стоимости до (например, при увольнении сотен позиций, целесообразно работы этой функции то функция для поиска конкретных
Способ 3. Столбец-индикатор
посчитать. У нас — «>0». Формула напишем такое условиеАпельсины используется верхняя левая применить к одномуПродажи первый и откуда это которых после третьего или ЛОЖЬ. Это 1000 и отдельно сотрудника), изменять значения
использовать СУММЕСЛИ.
необходимо создать наСУММЕСЛИ (SUMIF) данных. – это столбец получится такая. функции. «*я*»8 000 ₽ ячейка аргумента диапазону, а просуммироватьЮжныйаргумент брать. символа фрагмент строки значит, что выражение со стоимостью больше (заменить «январь» на
Способ 4. Волшебная формула массива
Если к стандартной записи листе специальный диапазонне поможет, т.к.Если нужно указать «Сумма».=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;E2:E8;»>0″)Формула будет такой.Маслодиапазон_суммирования соответствующие значения из
Орехов
в этой формулеVlad999 «-защита». должно содержать оператор 1000. В таком «февраль» и подставить команды СУММЕСЛИ в критериев — ячейки, содержащие не умеет проверять не одно условиеЗаполнили диалоговое окноПример 7. =СУММЕСЛИМН(D2:D8;C2:C8;»*я*»)4 000 ₽, а затем суммируются другого диапазона. Например,Напитки — первая часть: пришлось повозиться с
Способ 4. Функция баз данных БДСУММ
Таким образом удалось суммировать сравнения, например: больше случае одного оператора новые заработные платы) конце добавляются еще условия отбора — и больше одного критерия. суммирования, а несколько, так.Нужно посчитать продажиЕсли нужно, тоОвощи ячейки части диапазона, формула3571 данных, функция требует
вроде бы простой
planetaexcel.ru
Примеры использования функции СУММЕСЛИ в Excel с несколькими условиями
только определенную группу (>) меньше (), сравнения нам недостаточно и т.п. две буквы – указать затем этот Поэтому начиная с
то применяем функциюНажимаем «ОК». Получилась такая Васечкиным по всем добавляем условия вМорковь соответствующей по размерам
СУММЕСЛИ и ее синтаксис
=СУММЕСЛИ(B2:B5; «Иван»; C2:C5)Западный в качестве входных задачей суммирования по товаров в общем
- больше или равно (=1000) иначе мыПростые логические функции такие МН (СУММЕСЛИМН), значит,
- диапазон функции как версии Excel 2007 «СУММЕСЛИМН». Про эту формула. =СУММЕСЛИ(A2:A7;1;C2:C7)
- филиалам конкретного магазина. функцию СУММЕСЛИМН по42 000 ₽ аргументу
суммирует только теЕгоров данных. условию. Оказывается, что списке отчета по (>=), меньше или просуммируем сумму ровно как ЕСЛИ обычно
подразумевается функция с
Как работает функция СУММЕСЛИ в Excel?
аргумент: в набор функций функции читайте вМы рассмотрели этот Таблица такая. менеджерам и покупателям.Фрукты
диапазон значения из диапазонаМолочные продуктыПосле этого необходимо найти формулы в Excel складу. Данный фрагмент равно ( в 1000 – предназначены для работы несколькими условиями. Она=БДСУММ(A1:D26;D1;F1:G2) была добавлена функция
статье «Суммирование ячеек вариант установки формулыВ условиях функции напишемПример 3.Яблоки. Пример:
C2:C5, для которых
- 3338 данные, которые соответствуют в упор не наименования товара долженСоздание сложных критериев условий 2 раза, что с одним столбцом
- применяется в случае,Суммировать в программе ExcelСУММЕСЛИМН (SUMIFS) в Excel по в ячейку, чтобы
- так — «*Монетка*».Нам нужно посчитать12 000 ₽Диапазон соответствующие значения изВосточный
двух условий, поэтому воспринимают как значения встречаться в определенном может быть запутанным. приведет к ошибочным или одной ячейкой.
когда нужно задать умеет, наверное, каждый.- в ней условию» тут. понять принцип работы Формула получится такая. сумму продаж всехФормулаДиапазон суммирования диапазона B2:B5 равныПесоцкий
Функция СУММЕСЛИ в Excel с несколькими условиями
введите свой первый данные, вытянутые с месте – 3 Однако если придерживаться итоговым результатам: Excel также предлагает не один критерий. Но с усовершенствованной количество условий проверкиКакие ещё, есть
Синтаксис с использованием функции по нескольким критериям
функции «СУММЕСЛИ». Когда=СУММЕСЛИМН(D2:D8;B2:B8;»Васечкин»;A2:A8;»*Монетка*») фруктов, кроме яблок.Описание
- Фактические ячейки «Иван».Напитки условие, указав для помощью ПСТР. Как символа от начала нескольких простых правилЭто очень распространенная ошибка
- несколько других логическихАргументов у СУММЕСЛИМН может версией команды СУММ, увеличено аж до
- способы суммирования в поймем принцип составленияПоследним условием мы В условии функции
- РезультатA1:A5Если необходимо выполнить суммирование5122
- функции расположение данных это исправить (особенно строки. Нет необходимости описанных в ниже
пользователей Excel при функций служащих для быть сколько угодно, которая называется СУММЕСЛИ, 127! Функция находится Excel, не обязательно формулы с функцией
Пример использования
написали «*Монетка*». Слово напишем так. “<>яблоки”=СУММЕСЛИ(A2:A7;»Фрукты»;C2:C7)B1:B5 ячеек в соответствииСеверный
- (a2: a11), а
- в случае с
использовать сложные формулы приведенной таблице, не
работе с логическими
- агрегирования данных. Например, но минимум –
- существенно расширяются возможности в той же формулами, смотрите в
- «СУММЕСЛИ», можно формулу
- «Монетка» заключили вНо, в нашей таблицеОбъем продаж всех продуктов
- B1:B5 с несколькими условиями,
Песоцкий также какие условием временем), пока не
СУММЕСЛИ в Excel с динамическим условием
с функцией =ЛЕВСИМВ() будет возникать никаких функциями! функция СУММЕСЛИ для это 5. данной операции. категории статье «Суммирование в написать, сразу, в звездочки. Это значит, есть разные наименования категории «Фрукты».A1:A5 используйте функцию СУММЕСЛИМН.Молочные продукты является — наилучшим понятно. Файл с и т.д. Достаточно
проблем.Внимание! В первом выборочного суммирования диапазонаДиапазон суммирования. Если вПо названию команды можноМатематические
Excel». ячейке, не вызывая что Excel будет – яблоки зеленые,20 000 ₽B1:B3СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])6239
образом «Южный». Обратите комментариями прилагается. лишь воспользоваться операторамиТаблица правил составления критериев примере нулевые значения значений по условию. СУММЕСЛИ он был понять, что онаи работает похожим
exceltable.com
Формула суммы по условию критерия выборки значений в Excel
При написании формулы, окно функции. искать все слова красные яблоки. Чтобы=СУММЕСЛИ(A2:A7;»Овощи»;C2:C7)B1:B5Аргументы функцииЮжный внимание, запятые междуRaufbold многозначных символов чтобы условий:
Примеры формулы для суммы диапазонов с условием отбора в Excel
нам необязательно былоНиже на рисунке представлен в конце, то не просто считает образом, но имеет мы можем допуститьПолучились такие данные. «Монетка» с разными и их исключитьОбъем продаж всех продуктовA1:B4СУММЕСЛИМаринова отдельные аргументы:: естественно ПСТР - сформулировать простой иЧтобы создать условие учитывать, так как в таблице список здесь он стоит сумму, но еще больше аргументов: ошибку (по незнаниюВторой пример. номерами и буквами, из суммы всех категории «Овощи».
C1:D4описаны ниже.Сельхозпродукты= SUMIFS(D2:D11,A2:A11,»South», текстовая функция и лаконичный запрос кПримените правило на балансовое сальдо счетов вместе с на первом месте. и подчиняется каким-либоПри помощи полосы прокрутки или опечатка, т.д.).Пример функции «СУММЕСЛИ» в разные филиалы магазина фруктов, условие в120 000 ₽
C1:D4Диапазон8677Кавычки вокруг слова «Южный» ответ соответственно текст, базе данных сПример это никак не состоянием по каждому Он также означает логическим условиям. в правой части Какие бывают ошибки,Excel по дате. «Монетка». Смотрите статью функции напишем такое.=СУММЕСЛИ(B2:B7;»*ы»;C2:C7)A1:B4 . Обязательный аргумент. ДиапазонЮжный укажите, что это
а не значение. минимальными нагрузками наЗначение равно заданному числу повлияло бы, но счету в виде ячейки, которые необходимоФункция СУММЕСЛИ позволяет суммировать окна можно задать что они означают,Теперь посчитаем сумму «Подстановочные знаки в «<>*яблоки*»Объем продаж всех продуктов,C1:C2 ячеек, оцениваемых наЕгоров текстовые данные. для перевода в системные ресурсы. или ячейке с
во втором случаи
Пример логического выражения в формуле для суммы с условием
положительных или отрицательных просуммировать. ячейки, которые удовлетворяют и третью пару как их найти за определенные даты. Excel» тут.Символы «<>» означают названия которых заканчиваютсяC1:D4 соответствие условиям. ЯчейкиМясоНаконец, введите аргументы для значение нужно +0Raufbold
данным адресом. нужно составлять критерий чисел. Допустим намДиапазон условия 1 –
определенному критерию (заданному ( и исправить? Чтобы Можно в формулеЕсть ещё одна – не равно. на «ы» («Помидоры»Однако если аргументы в каждом диапазоне450 второго условия – или *1 или
: Здравствуйте!Не используйте знак равенства условия суммирования иначе, необходимо посчитать сумму ячейки, которые нужно условию). Аргументы командыДиапазон_условия3 ответить на эти
Синтаксис формулы суммирования по условию в Excel
указать эту дату. функция в Excel, О других символах, и «Апельсины»).диапазон должны содержать числа,Южный диапазон ячеек (C2: —(формула) .Есть у меня и двойных кавычек. чтобы не допустить всех отрицательных чисел
оценить на основании следующие:- вопросы, читайте статью А можно в которая считает по которые можно вставить43 000 ₽и имена, массивы илиЕгоров C11), содержащий словоVlad999 задача создать макрос,=СУММЕСЛИ(B1:B10;3) ошибочных просчетов. для расчета суммарного первого критерия.Диапазон – ячейки, которыеУсловие3
«Ошибки в формулах формуле указать адрес условию. Эта функция в формулу и=СУММЕСЛИ(A2:A7;»»;C2:C7)диапазон_суммирования ссылки на числа.Мясо «Мясо», плюс само: — в формуле который будет вЗначение равно текстовой строке.
- Второй аргумент функции СУММЕСЛИ,
- расхода по движению
- Условие 1 – определяет
- следует оценить на
- ), и четвертую, и
Excel». ячейки, в которой проще. Читайте о что они означают,Объем продаж всех продуктов,
Создание критериев условий для функции СУММЕСЛИ
функции СУММЕСЛИ содержат Пустые ячейки и7673 слово (заключено в для чисел - банковской выписке определятьНе используйте знак равенства, то есть условие, финансовых средств. Этот ячейки, которые функция основании критерия (заданного т.д. — приИмеем таблицу по продажам, будем писать разные ней в статье смотрите в статье категория для которых разное число ячеек, ячейки, содержащие текстовыеВосточный кавычки) таким образом, понятно, а вот остаток на 17:00. но используйте двойные которое должно быть
результат будет позже выделит из первого условия). необходимости. например, следующего вида: даты. В ячейку «Функция «СУММЕСЛИ» в «Символы в формулах
не указана. пересчет листа может
| значения, пропускаются. Выбранный | Песоцкий | Excel может соответствовать |
| как быть сДля этого надо кавычки по краям. | выполнено, записывается между сравниваться вместе с | диапазона условия. |
| Критерий – определяет, какие | Если же у васЗадача E10 устанавливаем функцию | Excel». |
| Excel».4 000 ₽ | занять больше времени, диапазон может содержать | Сельхозпродукты |
| его. Завершить формулу временем, я так | от остатка на=СУММЕСЛИ(B1:B10;»Клиент5″) | двойными кавычками. В |
| сумой положительных чиселДиапазон условия 2 – ячейки из диапазона пока еще старая | : просуммировать все заказы, «СУММЕСЛИ».В Excel естьФормула будет такая.К началу страницы чем ожидалось. даты в стандартном | 664 |
| с закрывающую скобку | и не нашел начало дня отнятьЗначение отличается от заданного | данном примере используется |
с целью верификации ячейки, которые следует будут выбраны (записывается версия Excel 2003, которые менеджер Григорьев реализовалВ ячейке B10 еще одна функция, =СУММЕСЛИМН(D2:D8;C2:C8;»<>*яблоки*»)РассмотримСкопируйте образец данных из формате Excel (см.Северный)kalbasiatka
все документы по числа. символ сравнения – и вывода балансового оценить на основании в кавычках). но задачу с для магазина «Копейка». пишем нужную дату.
которая сначала умножает
Суммирование по неточному совпадению в условии критерия отбора
Если нужно посчитатьпримеры функции «СУММЕСЛИМН» в следующей таблицы и примеры ниже).Егорови нажмите клавишу: время и есть дебету на 17:00Поместите оператор и число «меньше» ( сальдо. Узнаем одинаковые второго критерия.Диапазон суммирования – фактические несколькими условиями решитьЕсли бы в нашейДиалоговое окно функции ячейки в столбцах, не сумму вExcel
вставьте их вУсловиеСельхозпродукты ВВОД. Результат, снова число. единственное для и прибавить все в двойные кавычки.В синтаксисе функции СУММЕСЛИ
ли суммы доходовУсловие 2 – определяет ячейки, которые необходимо нужно, то придется задаче было только заполнили так. затем складывает результаты. выбранных ячейках, а. Как написать условия ячейку A1 нового . Обязательный аргумент. Условие1500 нажмите — 14,719. времени установить - документы по кредиту=СУММЕСЛИ(B1:B10;»>=50″) присутствует необязательный для и расходов – ячейки, которые функция просуммировать, если они
exceltable.com
сложное суммирование по условию
извращаться — см. одно условие (все
Нажимаем «ОК». Формула получилась Можно настроить функцию, количество ячеек по для различных способов листа Excel. Чтобы
в форме числа,Южный=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat») формат ячейки - (тоже на 17:00).Значение отличается от текстовой заполнения третий аргумент сойдется ли у
выделит из второго удовлетворяют критерию. следующие способы. заказы Петрова или
такая. =СУММЕСЛИ(D2:D7;B10;C2:C7) чтобы считала по определенному условию, то выбора данных для отобразить результаты формул, выражения, ссылки наМариноваПо мере ввода функции время (чч:мм) еслиВыписка всегда определенного
строки. – диапазон суммирования. нас дебит с диапазона условия.Получается, что у функцииДобавим к нашей таблице все заказы в
Третий пример.
условию. Смотрите об воспользуйтесь функцией «СЧЕТЕСЛИ». суммирования, в т. выделите их и ячейку, текста илиМясо СУММЕСЛИМН в Microsoft хотите отображение 12:34 формата, но поПоместите оператор и число
В предыдущем примере кредитом. Для суммированияИ так далее. В всего 3 аргумента. еще один столбец, «Копейку», например), тоСумма ячеек этой функции в Подробнее об этой ч. с датами. нажмите клавишу F2, функции, определяющее, какие6596
Excel, если выRaufbold
размеру не ограничена в двойные кавычки. условие проверялось по числовых значений по
зависимости от количества Но иногда последний который будет служить задача решалась быExcel с условием. статье «»СУММПРОИЗВ» в функции читайте в Как установить а затем — клавишу ячейки необходимо суммировать.Формула не помните аргументы: вариант (см.файл с примером).=СУММЕСЛИ(B1:B10;»<>выплата»)
тому же диапазону, условию в Excel критериев, число аргументов может быть исключен, своеобразным индикатором: если достаточно легко при
Посчитаем сумму продаж Excel». статье «Функция «СЧЕТЕСЛИ»функцию «СУММЕСЛИМН» в Excel ВВОД. При необходимости Например, условие можетОписание справки готов подKalbasiatka, все простоИнформация о времениЗначение отличается от ячейки который нужно было применяется логическая функция может увеличиваться в и тогда команда
заказ был в помощи встроенной функции сметаны по всемРассмотрим одну из в Excel»., какие можно указать измените ширину столбцов, быть представлено вРезультат
рукой. После ввода и гениально!!! Большое и сумме каждого по указанному адресу суммировать. Благодаря третьему =СУММЕСЛИ(): арифметической прогрессии с
будет работать только «Копейку» и от Excel отделам за все популярныхПример 4. условия, смотрите в
чтобы видеть все таком виде: 32,
’ = СУММЕСЛИМН (a2:= СУММЕСЛИМН ( спасибо
CyberForum.ru
платежа представлена в
Сумма в Excel — одна из самых частых операций. Однако зачастую недостаточно просто просуммировать все ячейки, а нужно сделать сумму ячеек, соответствующих определенному условию или нескольким условиям. В этой статье рассмотрим, как сделать суммирование ячеек в Excel по условию и по нескольким условиям.
- Функция СУММЕСЛИ в Excel для суммирования по одному условию
- Суммирование по нескольким условиям в Excel при помощи функции СУММЕСЛИМН
- Суммирование по текстовому условию
- Суммирование по числовому условию
- Суммирование по условию даты
- Почему СУММЕСЛИ или СУММЕСЛИМН не работает
- СУММЕСЛИ или СУММЕСЛИМН в Excel — что лучше использовать?
Чтобы сделать суммирование ячеек в Excel по одному условию, можно использовать функцию СУММЕСЛИ (SUMIF в англ. версии).
Синтаксис функции:
=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])
диапазон — область ячеек, в которых будет осуществляться поиск критерия суммирования
критерий — значение, по которому будет осуществляться поиск в диапазоне. Может быть текстом, числом, ссылкой на ячейку или шаблоном.
[диапазон_суммирования] — необязательный параметр, указывается в том случае, когда совпадение с критерием ищем в одном диапазоне,а суммируем значения из другого. Если этот параметр не указан, то будут суммироваться значения из первой области диапазон (если они могут быть просуммированы).
Рассмотрим функцию СУММЕСЛИ в Excel на примере.
У нас есть таблица с данными о сотрудниках.
Получим сумму заработной платы у всех сотрудников мужского пола.
Способ 1. С указанием критерия напрямую в формуле.
В ячейке, в которой нужно получить результат суммирования по условию, напишем формулу:
=СУММЕСЛИ(D3:D12;»муж»;G3:G12)
D3:D12 — диапазон, в котором будет происходить поиск совпадения с критерием. В нашем примере это столбец Пол.
«муж» — критерий поиска. Поскольку это текстовый критерий, он заключен в кавычки.
G3:G12 — диапазон, значения из которого будут просуммированы при наличии совпадений диапазона с критерием. В примере это столбец Заработная плата.
Способ 2. С указанием критерия ссылкой на ячейку
Этот способ наиболее распространен и универсален.
В ячейке слева от рабочей укажем критерий поиска (без кавычек!).
Формула будет выглядеть так:
=СУММЕСЛИ(D3:D12;I4;G3:G12)
где первый и третий аргументы — такие же, как в предыдущем способе.
I4 — это ссылка на ячейку, в которой содержится критерий суммирования значений.
Если формулу нужно “протянуть” (скопировать) в другие ячейки — не забывайте закреплять диапазоны и критерий абсолютными или относительными ссылками (знак $).
Суммирование по нескольким условиям в Excel при помощи функции СУММЕСЛИМН
Если критерием для суммирования больше одного, то на помощь придет функция СУММЕСЛИМН (SUMIFS в англ. версии).
Синтаксис функции:
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия 1; условие 1;…;диапазон_условия n; условие n)
У функции СУММЕСЛИМН такие же аргументы, как у СУММЕСЛИ, но немного другой их порядок — на первом месте указывает диапазон суммирования (область значений, которые должны быть просуммированы), а затем — диапазон условий и условие поиска.
Рассмотрим на примере: нужно просуммировать заработную плату всех женщин бухгалтеров.
Соответственно, условия суммирований будут:
Должность: Бухгалтер
Пол: жен
Формула суммирования по нескольким условиям в Excel будет такая:
=СУММЕСЛИМН(G3:G12;C3:C12;I4;D3:D12;J4)
где G3:G12 — диапазон суммирования
C3:C12 — первый диапазон условия, в котором будет искаться первое условие
I4 — ссылка на ячейку, содержащую первое условие поиска
D3:D12 — второй диапазон условия, в котором будет искаться второе условие
J4 — ссылка на ячейку, содержащую второе условие поиска
Как видите, Excel верно просуммировал заработную плату всех сотрудников с полом жен и должностью Бухгалтер.
Обратите внимание:
- Функцию СУММЕСЛИМН можно использовать только для одного условия поиска (не обязательно их должно быть несколько)
- для функции СУММЕСЛИМН действуют те же правила, как и для функции СУММЕСЛИ (регистр критерия поиска, абсолютные и относительные ссылки)
- условия поиска можно располагать в любом порядке. В примере мы указали сначала условие Должность, затем условие Пол — можно сделать наоборот.
Главное правило: диапазон условия и условие, которое идет следом за ним, должны соответствовать друг другу.
- условия поиска также можно указывать как в самой формуле, так и ссылкой на ячейку.
- критерии поиска в стандартном варианте должны находиться в разных столбцах. Если несколько критериев суммирования находятся в одном столбце, то нужно использовать формулу массива.
Суммирование по текстовому условию
Пример суммирования по текстовому условию мы рассмотрели в первом пункте этой статьи.
Текстовые значения суммировать сами по себе нельзя, но можно получить сумму соответствующих им чисел.
Рассмотрим простой пример использования функции СУММЕСЛИ в эксель таблице: просуммируем заработную плату всех кассиров в таблице из предыдущего примера.
Точно так же можно сделать сумму значений по условию, указав критерий прямо в формуле. Текстовый критерий обязательно нужно заключить в кавычки!
Обратите внимание:
- функции СУММЕСЛИ и СУММЕСЛИМН не чувствительны к регистру критерия. Например, сейчас критерий поиска Кассир
можно указать критерий с большой буквы или даже всеми большими буквами, например:
Как видите, формула работает и выдает тот же результат.
Но если добавить любой знак, например пробел или точку в конце — формула не будет работать, т.к. нет точного совпадения критерия со значениями диапазона для поиска.
- в качестве критерия всегда лучше использовать ссылку на ячейку, а не указывать его непосредственно в формуле. Так будет проще корректировать формулу, и меньше вероятности совершить ошибку при указании критерия. К тому же, в этом случае не нужно заключить в кавычки критерий поиска.
Поэтому в следующих примерах будем использовать только вариант со ссылкой на ячейку — лучше сразу приучить себя к такому способу.
СУММЕСЛИ или СУММЕСЛИМН по части текста
А вы знали, что можно сделать суммирование ячеек в Excel по одному или нескольким текстовым условиям, указав только часть текста? Подробно этот вариант рассмотрен в статье.
Например, получим сумму заработных плат сотрудниц по имени Елена. Критерием поиска в данном случае будет имя Елена — но ведь в нашей таблице нет столбца, в котором находится имя. Только ФИО. Однако, не нужно создавать дополнительных столбцов и разделять ФИО на компоненты.
Напишем следующую формулу:
=СУММЕСЛИ(B3:B12;»*елена*»;G3:G12)
Здесь критерием поиска будет «*елена*».
Используются два значка-оператора — кавычки и звездочка.
- звездочка * — заменяет любое количество символов. Т.е. перед и после слова елена может находиться любое количество любых символов.
- кавычки «» — обязательно ставятся для текстового аргумента поиска в функциях excel.
Вместо звездочки можно использовать знак вопроса ?. Отличие — символ * заменяем любое количество символов, а ? — только один символ.
Суммирование по числовому условию в Excel
Как правило, нужно определить сумму если больше чем, меньше чем или равно определенному числу.
Сумма чисел, если больше или меньше чем заданное число
Задача: получить сумму заработных плат, если величина заработной платы больше 50000 руб.
Формула будет такая:
=СУММЕСЛИ(G3:G12;»>50000″)
где G3:G12 — диапазон для поиска заданных значений и одновременно диапазон суммирования, поэтому третий критерий (не обязательный) в формуле не указывается.
«>50000» — критерий поиска.
Обратите внимание, что, несмотря на то, что критерий не текстовый, а числовой, используются кавычки.
Обязательно использовать кавычки, если в формуле применяются знаки больше > или меньше < ( а также >= и <=)
Если в качестве критерия используется знаки >, <, >=, <= и ссылка на ячейку, содержащую критерий (например >50000), то критерий поиска будет записан таким образом:
Знак сравнения должен быть заключен в кавычки, далее ставится амперсанд & и указывается ссылка на ячейку с критерием.
Сумма чисел, если равно заданному числу
В этом случае кавычки для критерия не используются.
Получим сумму заработных плат, равных 45000 руб.
Критерий поиска 45000 указывается без кавычек.
Также, если критерий указывается в ячейке, то формула будет как для текстового критерия:
Суммирование по условию даты
Чтобы сделать суммирование ячеек в excel по условию даты — равно определенной дате или больше или меньше определенной даты — то нужно указать эту дату в качестве критерия.
При этом дату также можно указывать непосредственно в формуле, а также ссылкой на ячейку, содержащую дату.
Для примера определим сумму заработных плат сотрудников, трудоустроенных после 1 января 2018 г.
Формула:
=СУММЕСЛИ(F3:F12;»>=»&I4;G3:G12)
Обратите внимание, как указан критерий — знак сравнения “больше или равно” заключен в кавычки и используется знак амперсанд &.
Если дату указывать напрямую в формуле, то ее нужно также заключить в кавычки:
Почему СУММЕСЛИ или СУММЕСЛИМН не работает
Формула СУММЕСЛИ иногда может выдавать ошибку #ЗНАЧ, если диапазон суммирования и диапазон условий имеют разный размер. Эта проблема решена в новых версиях Excel, но в ранних версиях она присутствует.
Также ошибка #ЗНАЧ может возникать, если формула ссылается на данные из другого файла, а этот файл в момент пересчета формулы закрыт. Необходимо просто открыть файл-источник, и ошибка исчезнет.
Если функция СУММЕСЛИ или СУММЕСЛИМН выдает значение 0 (при условии, что значение суммы никак не может быть равно 0), необходимо проверить критерий — не закрались ли в него лишние пробелы или знаки препинания.
СУММЕСЛИ или СУММЕСЛИМН в Excel — что лучше использовать?
Как вы могли заметить, эти функции похожи между собой.
В своей практике, я часто замечаю, что пользователи разграничивают действие этих функций: для суммирования по одному критерию используют строго функцию СУММЕСЛИ, по нескольким критериям — СУММЕСЛИМН.
Но обратите внимание:
Функцию СУММЕСЛИМН можно использовать для суммирования как по одному критерию, так и по нескольким критериям.
Другими словами, функция СУММЕСЛИМН универсальная. И функцию СУММЕСЛИ таким образом можно вообще не использовать, заменив ее СУММЕСЛИМН.
Почему СУММЕСЛИМН удобнее при суммировании по одному критерию?
Потому что условия задачи всегда могут измениться. Сейчас вы суммируете по одному критерию (например, по должности), а в следующий момент понадобилось добавить еще одно условие (пол). В случае с функцией СУММЕСЛИМН вы просто внесете дополнительное условие в ту же формулу, а функция СУММЕСЛИ здесь вовсе не подойдет (придется все равно переписывать на СУММЕСЛИМН).
Поэтому совет практика: использовать только функцию СУММЕСЛИМН.
Однако, окончательное решение в выборе функции всегда остается за вами.
В этой статье мы узнали, как сделать суммирование ячеек в excel по условию и познакомились с возможностью сделать суммирование по нескольким условиям.
Сообщество Excel Analytics | обучение Excel
Канал на Яндекс.Дзен
Вам может быть интересно: