Функция СУММЕСЛИ в 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 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.
Спасибо за внимание.
Спасибо!
Здравствуйте, а если критерий число (код закупки), то как быть? Пробовала делать формат текстовый и общий – не считает.
Вложение .xlsx
Очень прошу объяснить мне работу данной функции. Когда из диапазона мне необходимо просуммировать ячейки с “01-04”, функция считает что ячейки с номером “04-19” идентичны с номерами “01-04”. Почему так происходить?(надеюсь я смог объяснить/раскрыть суть проблемы)
Вложение -1.xlsx
Функции СУММЕСЛИ все что похоже на число преобразовывает в число и собственно смотрите результат в файле
А как остановить это преобразование не меняя формулу? В настройках нету? Мне нужно чтобы формула считала 01-04 отдельно, 04-19 отдельно из общего массива. Раньше работалось нормально. С установлением новой версии эксел (с 2010 года на 2016 год) формула неправильно считает.
Добрый день!
Подскажите пожалуйста, возможно ли суммировать по начальному слову. В таблице много позиций
Трубка 54654
Трубка 98787
Трубка 54466
Трубопровод 45646
Трубопровод 57466
Соответственно он должен просуммировать только по слову Трубка или Трубопровод, так как все модели труб в формулу не занесёшь.
Можно. Делаете такую таблицу
Трубка*
Трубопровод*
Потом прописываете формулу
=СУММЕСЛИ($A$1:$A$10;G1;$B$1:$B$10)
где G1 – это трубка*
Вложение
Здравствуйте. очень сильно нужна ваша помощь, я запуталась.
задание: Посчитать оборот по каждому бренду по данным листа 2 используя одну формулу
Даны 2 таблицы
в первой: столбцы бренд и оборот во второй: столбцы бренд, наименование товара и сумма.
Какую можно использовать формулу для подсчета оборота по каждому бренду? пыталась использовать суммесли, но сумма подсчитывалась только для одного бренда, на остальные не действовала.
Огромное спасибо.
Не видно листа 2, плюс лучше файл, самому писать записывать денные не очень хочется.
Здравствуйте. во вложении
Вложение -для-аналитика-ОМ-1.xlsx
2
Вложение
Здравствуйте, у меня возник вопрос, задача полагала в том чтобы посчитать количество конкретного проданого товара, но в итоге сумма всех груп товаров отличается от суммы всех товаров отдельно. Или формула не суммирует некоторые ячейки, или же я сделала ошибку. Замена формата в числовой или текстовый не помогла, но может быть я что-то делаю неправильно. Подскажите пожалуйста.
По формуле вроде все правильно. Но на скриншоте не видно ни столбец E ни столбец I. Скорее всего ошибка в написании товаров. Может есть товары с лишним пробелом, нечитаемым символом. Прогоните товары через формулу =СЖПРОБЕЛЫ(ПЕЧСИМВ())
Вложение
Спасибо, но я использую не названия товаров, а артикли – числа. Они прописаны текстовым форматом, но измена формата на числовой не меняет результат. Я попыталась использовать формулу, что Вы посоветовали, но ничего не изменилось.
Пример товара с неправильным подсчетом какой?
Посчитал по “Жувальні цукерки” – формула все правильно считает
Пример. Ячеек больше чем сумма которую вывела формула.
Пример не пришел
Вложение
Пример
Все правильно у вас. Вы просто делаете поиск по всему документу
а там цифры 50346 встречаются и в других ячейках. Например тут: https://sirexcel.ru/wp-content/uploads/2018/02/1.png
Да, но ведь сума груп то отличается – меньше, чем сума общая, а в условии не весь документ, а 1 столбец. И все же почему-то формула не суммирует по групах правильно, я никак не могу понять, почему такая разница.
Я не понимаю, что вы имеете ввиду. Вот если выделить столбец и найти все значения то находит 65, при этом одно значение у вас указано в кол-ве 2, то есть в сумме получается 66, так же как посчитала СУММУЕСЛИ – правильно.
https://sirexcel.ru/wp-content/uploads/2018/02/2.png
Если имеете ввиду другое, то напишите, конкретно где неправильно считает. Как должно быть и как сейчас (какие цифры должны быть у считает сейчас)
Странно, должно работать. А кусок файла можете прислать?
Я посчитала суму всех единиц всех товаров – это 14414,2, потом использовала формулу СУММЕСЛИ, чтобы посчитать сумму единиц каждого товара, после просуммировала уже эти значения: количество сока+ количество попкорна+…+количество х, и число отличается от предыдущей суммы = 14057,2, хотя не должно. Но проверить абсолютно все позиции с помощью поиска будет долго и сложно, кроме того я могу просто не заметить среди сотен “1” другое число. Поэтому я пытаюсь найти причину по которой некоторые ячейки, как я подозреваю, не суммируются формулой. Или же проблема наоборот с подсчетом обычной формулы СУММ, которую я использовала для суммирования всех товаров сначала. В любом случае, эти суммы не должны отличатся – сумма всех единиц всех товаров и сумма груп этих же товаров.
Вложение -товар1.xlsx
Теперь понял как вы считаете. Добавил столбец O, там идет проверка наличия артикула в нижней таблицы. Там где Н/д таких товаров нет в нижней таблицы. Они лишние поэтому сумма верхней таблицы больше.
Даже не знаю, как Вас благодарить. Формула ВПР для меня отличная находка. Вы ну оооочень помогли. Спасибо большое.
Вложение -товар.xlsx
Буду безгранично благодарна за подсказку)
Добрый день. Подскажите ка правильно прописать формулу. У меня есть 650 км. мне нужно посчитать стоимость км если известно, что до 500 км. по 2 грн. а если свыше 500 км по 1 грн. Спасибо
=ЕСЛИ(A1<500;A1*1;500*1+(A1-500)*2)
А можно посчитать сумму каких-нибудь знаков ? Например знака + в определенном столбце ?
Можно конечно. + должен быть написан в виде текста, если это просто формат отображения, то лучше считать положительные числа. и в данном случае использовать надо счётесли а не суммесли
подскажите пожалуйста, с помощью какой функции можно посчитать сумму ряда от 1 до n-1?
∑А от 1 до (n-1)
Спасибо за статью!
Добрый день, у меня есть ежедневные данные, мне нужно их просуммировать, чтобы получить квартальные, пробовала с помощью суммесли, не получается, можно ли критерий условия задать тоже промежутком? В приложенном файле в ячейке Е2 примерно нужный мне результат. Заранее спасибо за помощь.
Сделайте дополнительные столбец с определением квартала.
Есть очень простая формула, которая поможет определить порядковый номер квартала для любой заданной даты из списка:
=ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3)
Далее суммируете с помощью суммесли по данному столбцу
А еще для этих целей рекомендую освоить сводные таблицы
https://sirexcel.ru/video-uroki/svodnye-tablicy-v-excel-podgotovka-dannyx-postroenie-i-nastrojka-video/
Вложение -1.xlsx
Добрый день! Никак не получается синхронизировать две таблицы. Если я вношу изменения в лист №1, то автоматически должны меняются данные в листе №2. Помогите, пожалуйста, найти формулу, которую необходимо вставить в таблицу из листа №2.
Вложение -1-1.xlsx
Сделал на примере кол-ва. Второй столбец по аналогии
Добрый день. Помогите пожалуйста. Есть столбик в котором занесены случайные числа по дням за месяц. Первый день сумма наибольше, потом меньше меньше и седьмой день найменьше, потом опять тоже самое. Нужно написать формулу чтобы находило 4 найбольших значения и суммировало каждое это число со следующими 6. При этом, таблица может начинаться с любого числа, не обьязательно с найбольшего.
Лучше показать пример, потому как много нюансов может быть, а гадать нет времени. 4 наибольших числа можно найти как НАИБОЛЬШИЙ(A1:A6;1), НАИБОЛЬШИЙ(A1:A6;2)…НАИБОЛЬШИЙ(A1:A6;4)
Добрый день! Прошу помочь в следующем:
В таблице 1 в диапазоне А1:А9 необходимо выбрать максимальное число и отнять его от числа в другой из другого диапазона. Подскажите, пожалуйста, это возможно?
=B1-МАКС(А1:А9)
где B1 – это другое число из другого диапазона
Спасибо большое!) Работает!
Ребят, помогите со строчкой.
Задача:
Нужно посчитать количество событий, если они происходят раньше определенной даты. Дата каждого из событий указана в отдельной переменной. Например: Клиент1; Дата платежа; Дата письма1; Дата письма2; Дата письма3 и т.д.
Нужно посчитать количество писем, если их дата меньше чем дата платежа.
Вот тут можно посмотреть видео урок на эту тему
https://sirexcel.ru/priemi-excel/kak-poschitat-kolichestvo-yacheekznachenij-v-excel/
Вложение primer.xlsx
Требуется сделать отчет, например, за текущий месяц. Таблица Data может иметь много строк и поэтому не хочется из даты получать отдельно месяц через отдельную колонку (так кстати работает). Хочется получать номер месяца не посредственно из числа, сравнивать с текущим и если условия выполняются – считать, суммировать
Вложение primer-1.xlsx
Можно сделать три условия. По имени, больше 1 числа месяца и меньше последнего числа месяца. Пример решения во вложении
Вложение
не работает функция подсчета по условию за месяц
На мой взгляд у вас что-то не так написано в формуле. Можете прислать пример файла?
Добрый день !!! поогите пожалуйста, критерий содержит более 15 числовых знаков н.р: 39210202101081012160 и 39210202101081011160 . фунция суммирует в один диапазон.
что-то не так делаете то что больше 15 знаков проверку не должно менять
Вложение -формул.xls
Добрый день, прошу помощи. Во вложении таблица, это реестр платежей по Распред. центрам, в последнем листе таблица по плану сбора денег с этих центров на месяц по недельно. Соответственно необходима какая то “сложная” формула которая будет распознавать и даты и дни недели.
При чем эта таблица будет вестись весь 2016 и 2017 год, то есть даты и отгрузок и фактические оплаты будут просто добавляться на листы ниже.
На листе каждого центра можно добавить дополнительный столбец с номером недели, который будет подвязан к дате.
На лисет Планы по сбору ДС так же нужно определять номер недели по дате. Можно сделать дополнительную скрытую строку, где вычислять номер недели, чтобы сохранить формат недели как сейчас.
Номер недели определяется так
=НОМНЕДЕЛИ(A1;11)вместо A1 – ссылка на дату.
После этого сможете по номеру недели суммировать
Вложение
Добрый день!
Подскажите пожалуйста, как при помощи формулы СУММЕСЛИ сложить значения, стоящие не через несколько строк, а через несколько столбцов друг от друга?
Например мне нужно в таблице на изображении сложить кол-во звонков у данного конкретного менеджера за каждый день в течение месяца… вручную очень долго. Ввожу формулу -выдает значение 0
Заранее спасибо
наверное вы делаете суммесли по условии фио
Вам же надо проверять строку 59 по условию “звонков”
вот так будет выглядеть для первого человека
=СУММЕСЛИ($A$59:$AB$59;"звонков";A60:AB60)Если нужно привязаться и к фио, то используйте суммеслимн (статья есть на сайте)
подскажите пожалуйста, как просуммировать значения из одной колонки в разных ячейках внизу, всех значений >=10; >=25; .=3
А что не получается?
=СУММЕСЛИ(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
Добрый день! Проблема в том, что условие вы указываете ссылку на ячейку, но в кавычках, поэтом E6 Excel не понимает, что это ссылка.
Нужно так указать
"<="&E6Хм, впринципе понятно, но:
Я пытался вычислить еще и если критерий например “Sony” и далее в дипазоне суммирования я писал “выражение вычисления процента ЯЧЕЙКА/100 * 35 (%)”
не выдает значение -(
=СУММЕСЛИ(H:H;”самсунг”;O2/100*35)
Добрый день! У вас несколько условий, поэтому нужно использовать СУММЕСЛИМН
https://sirexcel.ru/priemi-excel/funkcii/summeslimn/
Как мне подсчитать за каждого человека в отдельности? Какую формулу нужно ввести. У меня что то не получается, выдает ошибку.
Вообще не понятно про что вы говорите
Вложение _АПРЕЛЬ1.xlsx
У меня почему- то не работает данная функция. Помогите разобраться. (Функция в файле применяется в столбце G
Проблема в том, что у вас цифры на листе 01 указаны текстом. Это случилось так как у вас все числа не целые, а дробные и указана точка в качестве разделителя. В настройках же Excel у вас стоят разделители запятые, поэтому Excel не понимает, что это цифры. Возможно поможет вот эта статья: https://sirexcel.ru/priemi-excel/kak-pomenyat-tochki-na-zapyatye-v-excel/
Добрый день!
Можно ли использовать в функции СУММУСЛИ исключающее условие, т.е. нужно просуммировать все цифры в столбце, кроме некоторых.
Пример: в первом столбце номера счетов-фактур, некоторые из них авансовые и имеют в номере букву “А”. Во втором столбце суммы этих счетов-фактур. Нужно просуммировать все, кроме авансовых. Подскажите, пожалуйста, как написать формулу.
Заранее спасибо.
Сделайте дополнительный столбец, который будет проверять счета-фактуры и будет определять какие из них авансовые и какие нет. То есть в этом столбце будет два значения. Например, “А”, “нет”.
Далее используйте функцию СУММЕСЛИМН
https://sirexcel.ru/priemi-excel/funkcii/summeslimn/
А можно как-то без дополнительного столбца? Прописать в формуле как-нибудь условия “все кроме “*А”.
Именно так и можно, но только функцией СУММЕСЛИМН, просто одно из условий будет “*А”, если “А” указано в конце счета фактуры
Как сделать: например если А1=1, то А1=А3
так не сделать. Это циклическая ссылка. Можно сделать в соседней ячейке. Если A1=1, то в B1 прописать значение A3
Вложение .xlsx
Добрый день!
Как добавить в диапазон суммирования и диапазон, листы с 1 по 31. В формулу сумм если…какие должны быть скобки ? Формула в ИТОГО
Вопрос как у Анатолия от 11,08
ЗАРАНЕЕ СПАСИБО!
Добрый день! Не хочется тратить время на поиск взаимосвязей. Напишите, на примере, какие ячейки вам необходимо заполнить на листе “Итого”. Думаю так будет быстрее мне понять, что вам требуется
Спасиб о!!!!!!!!!
Здравствуйте. У меня почему то результат после 27 строки не считается, расчет по следующим критериям идет с нулями https://yadi.sk/i/X3t3sUsHmAJr5
Вложение 1.xlsx
Подскажите,пожалуйста,как посчитать сумму чисел,кратных трем, в столбце. Заранее Благодарен!
отдельный столбец ОСТАТ(A1;3), где A – это столбец с числами. Так где получится 0 – это числа кратные 3, далее суммесли 0
Вложение -учеба.xlsx
Помогите решить задачу в эксель, первые 2 таблички я сделал, а третью не могу понять, нужно посчитать сколько воспитателей, Сколько человек имеют высшее образование? хотя бы пример формулы, попробовал выбирать критерии, все равно 0
• Сколько человек имеют 2-ю квалификационную категорию?
• Сколько человек без категории?
Вложение -учеба-1.xlsx
Ошибка в том, что тут надо использовать не суммесли, а счётесли. Сумму – это когда нужно суммировать числа, а вам нужно посчитать количество текстовых значений, поэтому СЧЁТЕСЛИ
Подскажите, пожалуйста, а если Критерием служит только одно слово из ячейки, а остальная часть ячейки может различаться, как задать такую формулу?
Заранее большое спасибо!
Можно в качестве критерия использовать маски со * и ?. Например “яблоки*” или “*седан” или “файл ??????.doc “
С помощью текстовых функций нужно в отдельном столбце вывести эту одинаковую часть и считать по этому столбцу
А как быть, если критерием является двойное неравенство, например, от 2 до 10?
Используйте функции СУММЕСЛИМН
https://sirexcel.ru/priemi-excel/funkcii/summeslimn/
Вложение .xlsx
Добрый день!
Какой формулой можно воспользоваться для получения следующих результатов:
Есть две таблицы. В одном из столбцов каждой таблицы есть ячейки с одинаковым текстовым содержимым. Необходимо, чтобы ексель по ячейке из одной таблицы находил эту же ячейку в другой таблице и выводил данные остальных столбцов ТОЛЬКО ПО ЭТОЙ СТРОКЕ.
Спасибо, если подскажете формулу
Добрый день! Используйте формулу ВПР. Но она работает только в том случае, если текстовое содержимое в обеих таблицах абсолютно идентично
Добрый вечер! Может кто нибудь подскажет? Буду очень благодарен.
В книге есть 31 лист с числами месяца, так и называется от 1го до31 и лис Итог. На каждом листе есть одинаковые таблица (первая смена, номер бригады, изделие №1 – шт, изделие №2 – шт, изделие №3 – шт; вторая смена, номер бригады, изделие №1 – шт, изделие №2 – шт, изделие №3 – шт). Номера бригад на листах периодически меняются, так как график работы разный. На листе Итог есть таблица, в которой занесена выработка по бригадам.
Так вот вопрос – как будет выглядеть формула в итоговой таблице. Чтобы Возле бригады №1 – нашлись и проссумировались данные о выработке со всех листов. Так же и с другими бригадами.
Спасибо.
Вы пришлите пример, тут не сложно будет сделать, покажу на паре дней, остальное сделаете сами
Если строки постоянно меняются, то наверное лучше использовать формулу СУММЕСЛИ, правда формула будет состоять из 31 функции СУММЕСЛИ, но зато будет универсальной и не будет зависеть от изменения положения номера бригады
как сделать чтобы суммировались только положительные значения?
В формуле СУММЕСЛИ в аргументе “критерий” нужно указать “>0”
с кавычками
Вложение -работа.xls
не получается разобраться с формулами. помогите пожалуйста.
Для этого нужно задать вопрос
добрый день! а как прописать такую же формулу, если “диапазон” и “диапазон_суммирования” находятся в другом файле? просто при закрытии файла с этими данными в своде пропадают значения
Добрый день! Могу предложить следующее решение. Создаете новый лист и его заполняете ссылками на нужный лист другого файла, можно только нужных столбцов. Этот лист можете скрыть, чтобы не мешал.
Далее когда нужно обновить данные нажимаете в вкладке “Данные” – “Изменить связи” и напротив нужного листа нажимаете “Обновить”.
Либо суммесли используете на том листе, где исходные данные, а потом уже эти итоговые данные подтягиваете с помощью ВПР, но данные так же нужно будет обновлять как я указал раньше, если только файлы не открыты одновременно.
Можно и макросом, но ради этого не стоит заморачиваться.
Если есть вопросы – задавайте
А если ячейки выделены цветом как их проссумировать?
Добрый день, что суммировать по цвету ячейки, используйте вот эту статью:https://sirexcel.ru/priemi-excel/kak-v-excel-poschitat-kolichestvo-yacheek-po-cvetu-yachejki-ili-cvetu-teksta/
Спасибо большое! Вы помогли мне избежать ручной обработки 45000 строк в таблице.
Пожалуйста, спасибо за комментарий