Функция СУММЕСЛИ excel с примерами

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

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

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

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

Как применять СУММЕСЛИ – Примеры.

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

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

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

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

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

СУММЕСЛИ с текстовыми аспектами

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция СУММЕСЛИ при условии неравенства

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

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

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

Функция СУММЕСЛИ при условии соответствия тексту

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

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

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

Функция СУММЕСЛИМН с несколькими критериями

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

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

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

Мы рады, что смогли посодействовать Для вас в решении препядствия.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

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

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

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

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

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

Данной функцией можно пользоваться при обработке огромных чисел, которые просто необходимо сложить, не отделяя ячейки по каким-то аспектам. Практически, как «+» в калькуляторе. Для выполнения обыденного сложения нужно:

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

Принципиально! Знайте, что программка проигнорирует логическое либо текстовое значения.

При суммировании чисел с одним условием применяйте функцию «СУММЕСЛИ»

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

Примечание! Можно вводить также математическое выражение.

На заметку! Эту функцию также можно ввести вручную, используя базисную запись: «=СУММЕСЛИ(x), где х – спектр, аспект и спектр суммирования, которые перечисляются через «;». К примеру, «=СУММЕСЛИ(А1:А2;«Условие»;В1:В2)».

Вводим вручную данные, в избранную ячейку, жмем «Enter»

Но, если необходимо отобрать информацию по нескольким различным аспектам?

Если вы желаете выяснить, как написать формулу в Excel, также обучиться азам работы в Excel, вы сможете прочесть статью о этом на нашем портале.

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

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

Принципиально! Направьте внимание – в отличие от «СУММЕСЛИ», в данном окне поначалу задается спектр суммирования, а позже уже условия. Также можно ввести до 127 критерий.

Примечание! Наиболее подробную аннотацию вы сможете отыскать в данной нам статье чуток выше.

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

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

Видео — Суммирование по условию в Excel, функция «СУММЕСЛИМН»

Как суммировать лишь положительные либо отрицательные числа в Excel (обычная формула)

Когда вы работаете с числами в Excel, для вас нередко требуется условное добавление данных. Один из всераспространенных примеров этого — когда для вас необходимо суммировать положительные числа в Excel (либо суммировать лишь отрицательные числа).

И это просто создать при помощи интегрированных в Excel формул.

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

Суммировать лишь положительные числа

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

Ниже приведена формула, которая сделает это:

Приведенная выше формула СУММЕСЛИ (SUMIF) воспринимает три аргумента:

• 1-ый аргумент — это спектр критериев. Тут мы проверим аспекты и добавим лишь те значения, которые соответствуют аспектам. В нашем примере это B2: B8.
• 2-ой аргумент — это сами аспекты. Так как мы желаем суммировать лишь положительные числа, это «> 0». Помните, что когда вы используете оператор, он должен быть заключен в двойные кавычки.
• 3-ий аргумент имеет фактический спектр, который вы желаете суммировать.

Направьте внимание, что при помощи формулы SUMIF спектр критериев и спектр ячеек, которые вы желаете суммировать, могут быть различными. В нашем примере спектр критериев и спектр суммы схожи (B2: B8).

Так как в этом случае у меня есть лишь один аспект, я употреблял формулу SUMIF. Если у вас есть несколько критериев для проверки, вы сможете применять эту формулу SUMIFS в Excel.

СУММИРОВАТЬ все положительные числа больше 100

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

К примеру, в приведенном ниже наборе данных я желаю добавить лишь те ячейки, значение которых больше 100.

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

Ниже приведена формула, которая сделает это:

Как видите, все, что я сделал, это скорректировал 2-ой аргумент, который является аргументом аспекта.

Сложить все числа с положительным значением иной ячейки

В приведенных выше примерах мы употребляли формулу SUMIF, в которой «спектр критериев» и «спектр сумм» совпадают.

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

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

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

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

Направьте внимание, что это подобная формула, которую мы употребляли выше (с одним конфигурацией). Заместо того, чтоб иметь однообразные аспекты и спектр суммы, в данной нам формуле все по-другому.

Совет от специалиста : когда вы указали спектр критериев, для вас не надо указывать весь спектр сумм. К примеру, последующая формула будет работать нормально — = SUMIF (C2: C6, ”> 0 ″, B2). Так как C2: C6 уже указан, для вас не надо указывать B2: B6. Подойдет лишь 1-ая ячейка. Формула автоматом воспримет этот же спектр размеров, начиная с B2.

Суммировать лишь отрицательные числа

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

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

Ниже приведена формула для этого:

В приведенной выше формуле в качестве аспекта (2-ой аргумент) употребляется «<0», который складывает лишь отрицательные числа.

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

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