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

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

 

Поделиться:
97 Комментариев
  • Мила says:

    Спасибо!

  • Тина says:

    Здравствуйте, а если критерий число (код закупки), то как быть? Пробовала делать формат текстовый и общий — не считает.

  • Айбек says:

    Вложение  .xlsx

    Очень прошу объяснить мне работу данной функции. Когда из диапазона мне необходимо просуммировать ячейки с «01-04», функция считает что ячейки с номером «04-19» идентичны с номерами «01-04». Почему так происходить?(надеюсь я смог объяснить/раскрыть суть проблемы)

    • excel says:

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

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

      • Айбек says:

        А как остановить это преобразование не меняя формулу? В настройках нету? Мне нужно чтобы формула считала 01-04 отдельно, 04-19 отдельно из общего массива. Раньше работалось нормально. С установлением новой версии эксел (с 2010 года на 2016 год) формула неправильно считает.

  • Денис says:

    Добрый день!
    Подскажите пожалуйста, возможно ли суммировать по начальному слову. В таблице много позиций
    Трубка 54654
    Трубка 98787
    Трубка 54466
    Трубопровод 45646
    Трубопровод 57466
    Соответственно он должен просуммировать только по слову Трубка или Трубопровод, так как все модели труб в формулу не занесёшь.

    • excel says:

      Можно. Делаете такую таблицу
      Трубка*
      Трубопровод*
      Потом прописываете формулу
      =СУММЕСЛИ($A$1:$A$10;G1;$B$1:$B$10)
      где G1 — это трубка*

  • Катерина says:

    Вложение

    Здравствуйте. очень сильно нужна ваша помощь, я запуталась.
    задание: Посчитать оборот по каждому бренду по данным листа 2 используя одну формулу
    Даны 2 таблицы
    в первой: столбцы бренд и оборот во второй: столбцы бренд, наименование товара и сумма.
    Какую можно использовать формулу для подсчета оборота по каждому бренду? пыталась использовать суммесли, но сумма подсчитывалась только для одного бренда, на остальные не действовала.
    Огромное спасибо.

    • excel says:

      Не видно листа 2, плюс лучше файл, самому писать записывать денные не очень хочется.

  • Людмила says:

    Вложение

    Здравствуйте, у меня возник вопрос, задача полагала в том чтобы посчитать количество конкретного проданого товара, но в итоге сумма всех груп товаров отличается от суммы всех товаров отдельно. Или формула не суммирует некоторые ячейки, или же я сделала ошибку. Замена формата в числовой или текстовый не помогла, но может быть я что-то делаю неправильно. Подскажите пожалуйста.

    • excel says:

      По формуле вроде все правильно. Но на скриншоте не видно ни столбец E ни столбец I. Скорее всего ошибка в написании товаров. Может есть товары с лишним пробелом, нечитаемым символом. Прогоните товары через формулу =СЖПРОБЕЛЫ(ПЕЧСИМВ())

      • Людмила says:

        Вложение

        Спасибо, но я использую не названия товаров, а артикли — числа. Они прописаны текстовым форматом, но измена формата на числовой не меняет результат. Я попыталась использовать формулу, что Вы посоветовали, но ничего не изменилось.

        • excel says:

          Пример товара с неправильным подсчетом какой?
          Посчитал по «Жувальні цукерки» — формула все правильно считает

          • Людмила says:

            Пример. Ячеек больше чем сумма которую вывела формула.

            • excel says:

              Пример не пришел

              • Людмила says:

                Вложение

                Пример

                • excel says:

                  Все правильно у вас. Вы просто делаете поиск по всему документу

                  а там цифры 50346 встречаются и в других ячейках. Например тут: https://sirexcel.ru/wp-content/uploads/2018/02/1.png

                  • Людмила says:

                    Да, но ведь сума груп то отличается — меньше, чем сума общая, а в условии не весь документ, а 1 столбец. И все же почему-то формула не суммирует по групах правильно, я никак не могу понять, почему такая разница.

                    • excel says:

                      Я не понимаю, что вы имеете ввиду. Вот если выделить столбец и найти все значения то находит 65, при этом одно значение у вас указано в кол-ве 2, то есть в сумме получается 66, так же как посчитала СУММУЕСЛИ — правильно.
                      https://sirexcel.ru/wp-content/uploads/2018/02/2.png
                      Если имеете ввиду другое, то напишите, конкретно где неправильно считает. Как должно быть и как сейчас (какие цифры должны быть у считает сейчас)

        • excel says:

          Странно, должно работать. А кусок файла можете прислать?

          • Людмила says:

            Я посчитала суму всех единиц всех товаров — это 14414,2, потом использовала формулу СУММЕСЛИ, чтобы посчитать сумму единиц каждого товара, после просуммировала уже эти значения: количество сока+ количество попкорна+…+количество х, и число отличается от предыдущей суммы = 14057,2, хотя не должно. Но проверить абсолютно все позиции с помощью поиска будет долго и сложно, кроме того я могу просто не заметить среди сотен «1» другое число. Поэтому я пытаюсь найти причину по которой некоторые ячейки, как я подозреваю, не суммируются формулой. Или же проблема наоборот с подсчетом обычной формулы СУММ, которую я использовала для суммирования всех товаров сначала. В любом случае, эти суммы не должны отличатся — сумма всех единиц всех товаров и сумма груп этих же товаров.

            • excel says:

              Вложение  -товар1.xlsx

              Теперь понял как вы считаете. Добавил столбец O, там идет проверка наличия артикула в нижней таблицы. Там где Н/д таких товаров нет в нижней таблицы. Они лишние поэтому сумма верхней таблицы больше.

              • Людмила says:

                Даже не знаю, как Вас благодарить. Формула ВПР для меня отличная находка. Вы ну оооочень помогли. Спасибо большое.

          • Людмила says:

            Вложение  -товар.xlsx

            Буду безгранично благодарна за подсказку)

  • Татьяна says:

    Добрый день. Подскажите ка правильно прописать формулу. У меня есть 650 км. мне нужно посчитать стоимость км если известно, что до 500 км. по 2 грн. а если свыше 500 км по 1 грн. Спасибо

    • excel says:

      =ЕСЛИ(A1<500;A1*1;500*1+(A1-500)*2)

  • Антон says:

    А можно посчитать сумму каких-нибудь знаков ? Например знака + в определенном столбце ?

    • excel says:

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

  • ЕВгений says:

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

  • Иван says:

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

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

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

    • excel says:

      Сделайте дополнительные столбец с определением квартала.
      Есть очень простая формула, которая поможет определить порядковый номер квартала для любой заданной даты из списка:
      =ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3)
      Далее суммируете с помощью суммесли по данному столбцу
      А еще для этих целей рекомендую освоить сводные таблицы
      https://sirexcel.ru/video-uroki/svodnye-tablicy-v-excel-podgotovka-dannyx-postroenie-i-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 не понимает, что это цифры. Возможно поможет вот эта статья: https://sirexcel.ru/priemi-excel/kak-pomenyat-tochki-na-zapyatye-v-excel/

  • Татьяна says:

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

    • excel says:

      Сделайте дополнительный столбец, который будет проверять счета-фактуры и будет определять какие из них авансовые и какие нет. То есть в этом столбце будет два значения. Например, «А», «нет».
      Далее используйте функцию СУММЕСЛИМН
      https://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 строки не считается, расчет по следующим критериям идет с нулями https://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 не будет опубликован. Все поля обязательны для заполнения.

×
Рекомендуем посмотреть