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

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

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

Пример функции СУММЕСЛИ - Исходные данные

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

Синтаксис функции СУММЕСЛИ

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

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

диапазон – это диапазон сравнения, то есть тот массив, в котором будут сравниваться с критериями. В нашем пример это A5:A504, чтобы при протягивании формулы наш диапазон не сдвигался, сделаем его абсолютным, поставив знак $. Получаем – $A$5:$A$504

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

диапазон_суммирования – это тот диапазон, который необходимо просуммировать, при совпадении критериев. В нашем примере это диапазон: B5:B504, который мы также для удобства превратим в абсолютный $B$5:$B$504

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

Для решения задачи с данным примером в ячейку F5 впишем следующую формулу:

=СУММЕСЛИ($A$5:$A$504;E5;$B$5:$B$504)

Логика работы функции СУММЕСЛИ следующая: в диапазоне $A$5:$A$504 ищется критерий E5 (Санкт-Петербург), если Санкт-Петербург находится, то суммируется кол-во заказов из этой строчки то есть из диапазона $B$5:$B$504

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

Пример функции СУММЕСЛИ - формула

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

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

Скачать пример

 

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

Спасибо за внимание.

 

Поделиться:
54 Комментария
  • Алексей:

    вложение  primer.xlsx

    Требуется сделать отчет, например, за текущий месяц. Таблица Data может иметь много строк и поэтому не хочется из даты получать отдельно месяц через отдельную колонку (так кстати работает). Хочется получать номер месяца не посредственно из числа, сравнивать с текущим и если условия выполняются — считать, суммировать

    • excel:

      вложение  primer-1.xlsx

      Можно сделать три условия. По имени, больше 1 числа месяца и меньше последнего числа месяца. Пример решения во вложении

  • Алексей:

    вложение primer

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

    • excel:

      На мой взгляд у вас что-то не так написано в формуле. Можете прислать пример файла?

  • Жанна:

    Добрый день !!! поогите пожалуйста, критерий содержит более 15 числовых знаков н.р: 39210202101081012160 и 39210202101081011160 . фунция суммирует в один диапазон.

    • excel:

      что-то не так делаете то что больше 15 знаков проверку не должно менять

  • Ирина:

    вложение  -формул.xls

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

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

    • excel:

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

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

      Номер недели определяется так

      =НОМНЕДЕЛИ(A1;11)

      вместо A1 — ссылка на дату.

      После этого сможете по номеру недели суммировать

  • Полина:

    вложение tyUcbU8dPVo

    Добрый день!

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

    Например мне нужно в таблице на изображении сложить кол-во звонков у данного конкретного менеджера за каждый день в течение месяца... вручную очень долго. Ввожу формулу -выдает значение 0

    Заранее спасибо

    • excel:

      наверное вы делаете суммесли по условии фио

      Вам же надо проверять строку 59 по условию «звонков»

      вот так будет выглядеть для первого человека

      =СУММЕСЛИ($A$59:$AB$59;"звонков";A60:AB60)

      Если нужно привязаться и к фио, то используйте суммеслимн (статья есть на сайте)

  • Влад:

    подскажите пожалуйста, как просуммировать значения из одной колонки в разных ячейках внизу, всех значений >=10; >=25; .=3

    • excel:

      А что не получается?

      =СУММЕСЛИ(A1:A15;">=10")

      =СУММЕСЛИ(A1:A15;">=25")

      =СУММЕСЛИ(A1:A15;3)

  • Александр:

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

    Такой пример ошибку не выдает, но и вычислений не производит:

    =СУММЕСЛИМН(Лист1!$D$2:$D$91182;Лист1!$B$2:$B$91182;'Лист2 (2)'!B6;Лист1!$C$2:$C$91182; "<=E6") 

    J

    • excel:

      Добрый день! Проблема в том, что условие вы указываете ссылку на ячейку, но в кавычках, поэтом E6 Excel не понимает, что это ссылка.

      Нужно так указать 

      "<="&E6

  • валерий:

    Хм, впринципе понятно, но:

    Я пытался вычислить еще и если критерий например «Sony» и далее в дипазоне суммирования я писал «выражение вычисления процента ЯЧЕЙКА/100 * 35 (%)»

    не выдает значение -(

    =СУММЕСЛИ(H:H; «самсунг»;O2/100*35)

  • Павел:

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

    • excel:

      Вообще не понятно про что вы говорите

  • Стас:

    вложение  _АПРЕЛЬ1.xlsx

    У меня почему- то не работает данная функция. Помогите разобраться. (Функция в файле применяется в столбце G

    • excel:

      Проблема в том, что у вас цифры на листе 01 указаны текстом. Это случилось так как у вас все числа не целые, а дробные и указана точка в качестве разделителя. В настройках же Excel у вас стоят разделители запятые, поэтому Excel не понимает, что это цифры. Возможно поможет вот эта статья: sirexcel.ru/priemi-excel/...apyatye-v-excel/

  • Татьяна:

    Добрый день!

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

    Пример: в первом столбце номера счетов-фактур, некоторые из них авансовые и имеют в номере букву «А». Во втором столбце суммы этих счетов-фактур. Нужно просуммировать все, кроме авансовых. Подскажите, пожалуйста, как написать формулу.

    Заранее спасибо.

    • excel:

      Сделайте дополнительный столбец, который будет проверять счета-фактуры и будет определять какие из них авансовые и какие нет. То есть в этом столбце будет два значения. Например, «А», «нет».

      Далее используйте функцию СУММЕСЛИМН

      sirexcel.ru/priemi-excel/funkcii/summeslimn/

      • Татьяна:

        А можно как-то без дополнительного столбца? Прописать в формуле как-нибудь условия "все кроме «*А».

        • excel:

          Именно так и можно, но только функцией СУММЕСЛИМН, просто одно из условий будет «*А», если «А» указано в конце счета фактуры

  • Григорий:

    Как сделать: например если А1=1, то А1=А3

    • excel:

      так не сделать. Это циклическая ссылка. Можно сделать в соседней ячейке. Если A1=1, то в B1 прописать значение A3

  • Олег:

    вложение  .xlsx

    Добрый день!

    Как добавить в диапазон суммирования и диапазон, листы с 1 по 31. В формулу сумм если...какие должны быть скобки ? Формула в ИТОГО

    Вопрос как у Анатолия от 11,08

    ЗАРАНЕЕ СПАСИБО!

    • excel:

      Добрый день! Не хочется тратить время на поиск взаимосвязей. Напишите, на примере, какие ячейки вам необходимо заполнить на листе «Итого». Думаю так будет быстрее мне понять, что вам требуется

  • Алексей:

    Спасиб о!!!!!!!!!

  • Константин:

    Здравствуйте. У меня почему то результат после 27 строки не считается, расчет по следующим критериям идет с нулями yadi.sk/i/X3t3sUsHmAJr5

  • Максим:

    вложение  1.xlsx

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

    • excel:

      отдельный столбец ОСТАТ(A1;3), где A — это столбец с числами. Так где получится 0 — это числа кратные 3, далее суммесли 0

  • Дмитрий:

    вложение  -учеба.xlsx

    Помогите решить задачу в эксель, первые 2 таблички я сделал, а третью не могу понять, нужно посчитать сколько воспитателей, Сколько человек имеют высшее образование? хотя бы пример формулы, попробовал выбирать критерии, все равно 0

    • Сколько человек имеют 2-ю квалификационную категорию?

    • Сколько человек без категории?

    • excel:

      вложение  -учеба-1.xlsx

      Ошибка в том, что тут надо использовать не суммесли, а счётесли. Сумму — это когда нужно суммировать числа, а вам нужно посчитать количество текстовых значений, поэтому СЧЁТЕСЛИ

  • Екатерина:

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

    Заранее большое спасибо!

    • Олег:

      Можно в качестве критерия использовать маски со * и ?. Например «яблоки*» или «*седан» или "файл ??????.doc "

    • excel:

      С помощью текстовых функций нужно в отдельном столбце вывести эту одинаковую часть и считать по этому столбцу

  • Кадрия:

    А как быть, если критерием является двойное неравенство, например, от 2 до 10?

  • Ульяна:

    вложение  .xlsx

    Добрый день!

    Какой формулой можно воспользоваться для получения следующих результатов:

    Есть две таблицы. В одном из столбцов каждой таблицы есть ячейки с одинаковым текстовым содержимым. Необходимо, чтобы ексель по ячейке из одной таблицы находил эту же ячейку в другой таблице и выводил данные остальных столбцов ТОЛЬКО ПО ЭТОЙ СТРОКЕ.

    Спасибо, если подскажете формулу

    • Маргарита:

      Добрый день! Используйте формулу ВПР. Но она работает только в том случае, если текстовое содержимое в обеих таблицах абсолютно идентично

  • Анатолий:

    Добрый вечер! Может кто нибудь подскажет? Буду очень благодарен.

    В книге есть 31 лист с числами месяца, так и называется от 1го до31 и лис Итог. На каждом листе есть одинаковые таблица (первая смена, номер бригады, изделие №1 — шт, изделие №2 — шт, изделие №3 — шт; вторая смена, номер бригады, изделие №1 — шт, изделие №2 — шт, изделие №3 — шт). Номера бригад на листах периодически меняются, так как график работы разный. На листе Итог есть таблица, в которой занесена выработка по бригадам.

    Так вот вопрос — как будет выглядеть формула в итоговой таблице. Чтобы Возле бригады №1 — нашлись и проссумировались данные о выработке со всех листов. Так же и с другими бригадами.

    Спасибо.

    • excel:

      Вы пришлите пример, тут не сложно будет сделать, покажу на паре дней, остальное сделаете сами

    • excel:

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

  • михаил:

    как сделать чтобы суммировались только положительные значения?

    • excel:

      В формуле СУММЕСЛИ в аргументе «критерий» нужно указать «>0»

      с кавычками 

  • оксана:

    вложение  -работа.xls

    не получается разобраться с формулами. помогите пожалуйста.

    • excel:

      Для этого нужно задать вопрос

  • сергей:

    добрый день! а как прописать такую же формулу, если «диапазон» и «диапазон_суммирования» находятся в другом файле? просто при закрытии файла с этими данными в своде пропадают значения

    • excel:

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

      Далее когда нужно обновить данные нажимаете в вкладке «Данные» — «Изменить связи» и напротив нужного листа нажимаете «Обновить».

      Либо суммесли используете на том листе, где исходные данные, а потом уже эти итоговые данные подтягиваете с помощью ВПР, но данные так же нужно будет обновлять как я указал раньше, если только файлы не открыты одновременно.

      Можно и макросом, но ради этого не стоит заморачиваться.

      Если есть вопросы — задавайте

  • Dima:

    А если ячейки выделены цветом как их проссумировать?

  • Сергей:

    Спасибо большое! Вы помогли мне избежать ручной обработки 45000 строк в таблице.

    • excel:

      Пожалуйста, спасибо за комментарий

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.