Функция СУММЕСЛИ в 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 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

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

 

Поделиться:
Отправить
67 Комментариев
  • ЕВгений says:

    подскажите пожалуйста, с помощью какой функции можно посчитать сумму ряда от 1 до n-1?

    ∑А от 1 до (n-1)

  • Иван says:

    Спасибо за статью! 

  • Анастасия says:

    Добрый день, у меня есть ежедневные данные, мне нужно их просуммировать, чтобы получить квартальные, пробовала с помощью суммесли, не получается, можно ли критерий условия задать тоже промежутком? В приложенном файле в ячейке Е2 примерно нужный мне результат. Заранее спасибо за помощь.

    • excel says:

      Сделайте дополнительные столбец с определением квартала.

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

      =ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3)

      Далее суммируете с помощью суммесли по данному столбцу

      А еще для этих целей рекомендую освоить сводные таблицы

      sirexcel.ru/video-uroki/s...nastrojka-video/

  • Юлия says:

    Вложение  -1.xlsx

    Добрый день! Никак не получается синхронизировать две таблицы. Если я вношу изменения в лист №1, то автоматически должны меняются данные в листе №2. Помогите, пожалуйста, найти формулу, которую необходимо вставить в таблицу из листа №2.

    • excel says:

      Вложение  -1-1.xlsx

      Сделал на примере кол-ва. Второй столбец по аналогии

  • Дмитрий says:

    Добрый день. Помогите пожалуйста. Есть столбик в котором занесены случайные числа по дням за месяц. Первый день сумма наибольше, потом меньше меньше и седьмой день найменьше, потом опять тоже самое. Нужно написать формулу чтобы находило 4 найбольших значения и суммировало каждое это число со следующими 6. При этом, таблица может начинаться с любого числа, не обьязательно с найбольшего.

    • excel says:

      Лучше показать пример, потому как много нюансов может быть, а гадать нет времени. 4 наибольших числа можно найти как НАИБОЛЬШИЙ(A1:A6;1), НАИБОЛЬШИЙ(A1:A6;2)...НАИБОЛЬШИЙ(A1:A6;4)

  • Вячеслав says:

    Добрый день! Прошу помочь в следующем:

    В таблице 1 в диапазоне А1:А9 необходимо выбрать максимальное число и отнять его от числа в другой из другого диапазона. Подскажите, пожалуйста, это возможно?

    • excel says:

      =B1-МАКС(А1:А9)

      где B1 — это другое число из другого диапазона

      • Вячеслав says:

        Спасибо большое!) Работает!

  • Anton says:

    Ребят, помогите со строчкой.

    Задача:

    Нужно посчитать количество событий, если они происходят раньше определенной даты. Дата каждого из событий указана в отдельной переменной. Например: Клиент1; Дата платежа; Дата письма1; Дата письма2; Дата письма3 и т.д.

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

  • Алексей says:

    Вложение  primer.xlsx

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

    • excel says:

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

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

  • Алексей says:

    Вложение

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

    • excel says:

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

  • Жанна says:

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

    • excel says:

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

  • Ирина says:

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

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

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

    • excel says:

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

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

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

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

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

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

  • Полина says:

    Вложение

    Добрый день!

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

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

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

    • excel says:

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

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

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

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

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

  • Влад says:

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

    • excel says:

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

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

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

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

  • Александр says:

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

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

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

    J

    • excel says:

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

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

      "<="&E6

  • валерий says:

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

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

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

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

  • Павел says:

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

    • excel says:

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

  • Стас says:

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

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

    • excel says:

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

  • Татьяна says:

    Добрый день!

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

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

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

    • excel says:

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

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

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

      • Татьяна says:

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

        • excel says:

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

  • Григорий says:

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

    • excel says:

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

  • Олег says:

    Вложение  .xlsx

    Добрый день!

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

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

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

    • excel says:

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

  • Алексей says:

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

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

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

  • Максим says:

    Вложение  1.xlsx

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

    • excel says:

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

  • Дмитрий says:

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

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

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

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

    • excel says:

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

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

  • Екатерина says:

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

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

    • Олег says:

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

    • excel says:

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

  • Кадрия says:

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

  • Ульяна says:

    Вложение  .xlsx

    Добрый день!

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

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

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

    • Маргарита says:

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

  • Анатолий says:

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

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

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

    Спасибо.

    • excel says:

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

    • excel says:

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

  • михаил says:

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

    • excel says:

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

      с кавычками 

  • оксана says:

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

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

    • excel says:

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

  • сергей says:

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

    • excel says:

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

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

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

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

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

  • Dima says:

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

  • Сергей says:

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

    • excel says:

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

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

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