Функция СУММЕСЛИМН в Excel с примером использования в формуле
Функция СУММЕСЛИМН появилась начиная с Excel 2007 и выше. Само название функции говорит о том, что данная функция позволяет суммировать значения если совпадает множество значений.
Давайте сразу же рассмотрим использование формулы СУММЕСЛИМН на примере. Допустим у нас есть таблица с данными о сотрудниках, которые обзванивали клиентов с разных городов в разные дни и подключали им различные услуги.
У нас есть список сотрудников, выбирая город нам необходимо посчитать сумму подключенных услуг по сотрудникам и видам услуг. То есть нам необходимо заполнить вот такую таблицу (то что выделено желтым).
Конечно, в данном примере я бы использовал сводные таблицы (очень рекомендую посмотреть видеоурок), ну а мы будет решать данную задачу с помощью функции СУММЕСЛИМН, но прежде чем начать напомню, что по условию задачи, город нам необходимо выбирать из раскрывающегося списка и в данном уроке мы не будем рассматривать как его сделать.
Для наглядности я перенес данную таблицу на один лист с исходными данными.
Синтаксис функции СУММЕСЛИМН:
СУММЕСЛИМН(диапазон_суммирования;диапазон_условий1;условия1;[диапазон_условий2;условия2];…)
диапазон_суммирования — В нашем случае нам необходимо просуммировать количество подключенных услуг, поэтому это столбец Количество и диапазон Е2:E646
Далее указываются условия по которым необходимо просуммировать услуги. У нас три условия:
- должна совпадать фамилия сотрудника;
- должна совпадать услуга;
- должен совпадать город.
диапазон_условий1 — первое условие у нас сотрудники и диапазон условий это столбец с именами ФИО сотрудников A2:A646
условия1 — это сам сотрудник, так как мы начинаем прописывать формулу напротив сотрудника Апанасенко Е.П то и условия1 у нас будет ссылка на его ячейку G3
Вот что у нас должно получиться. Можно уже закрыть скобку и тогда формула посчитать общее количество услуг по данному сотруднику без разбивки по городу и вида услуг. Именно поэтому следующие условия в синтаксисе функции СУММЕСЛИМН указаны в квадратных скобках — что значит, что они не обязательны.
Продолжим, следующая условие это услуга
диапазон_условий2 — это столбец с услугами D2:D646
условия2 — это ссылка на услугу 1, то есть H2
Вот как должна выглядеть наша формула на текущий момент:
=СУММЕСЛИМН(E2:E646;A2:A646;G3;D2:D646;H2
Добавляем третье условие по городам
диапазон_условий3 — диапазон условий по городам это столбец «Город клиента» и диапазон B2:B646
условия3 — это ссылка на город в раскрывающемся списке G1
Все три условия учтены и формула наша выглядит следующим образом, скобку можно закрыть.
=СУММЕСЛИМН(E2:E646;A2:A646;G3;D2:D646;H2;B2:B646;G1)
Для первой ячейке мы посчитали значения, но теперь нам необходимо протянуть формулу на остальные ячейки. Для этого нам необходимо закрепить некоторые диапазоны.
Во-первых все диапазоны условий у нас не двигаются и постоянны поэтому закрепим их с помощью знака доллара (выделить данный диапазон в формуле и нажать клавишу F4):
A2:A646 → $A$2:$A$646
D2:D646 → $D$2:$D$646
B2:B646 → $B$2:$B$646
Диапазон суммирования у нас так же постоянный E2:E646 → $E$2:$E$646
Так же условия3 по городу G1 y нас всегда находится только в ячейке G1 и не должен смещаться при протягивании, поэтому так же закрепляем данную ячейку
G1 → $G$1
Услуги (условия2) при протягивании вправо должны меняться по столбцам, а вот строка при протягивании вниз не должна меняться, поэтому закрепляем только строку
H2 → H$2
Ссылка на фамилии наоборот должна меняться при протягивании вниз, но не должна меняться при протягивании формулы вправо, поэтому закрепляем только столбец
G3 → $G3
Итоговая формула будет выглядеть следующим образом
=СУММЕСЛИМН($E$2:$E$646;$A$2:$A$646;$G3;$D$2:$D$646;H$2;$B$2:$B$646;$G$1)
ее можно протянуть право и вниз, заполнив все ячейки таблицы. При выборе города из раскрывающегося списка данные будут пересчитываться.
Пример файла: Скачать
Вложение -лист.xlsx
Добрый день, есть эксель для подсчета отсутствий, отгулов и отпусков сотрудников. Не могу изменить формулу суммеслимн, чтобы в отпуске считала и выходные дни.У нас 7 дневная рабочая неделя, а формула изначально так поставлена-не считает выходные дни.
Вложение TIME-MANAGEMENT.xlsx
Здравствуйте. С функцией СУММЕСЛИМН у меня выходит #ЗНАЧ!
Я хочу задать следующее условие: Из столбца «Месяц» листа ТМ взять соответствующий месяц, из следующего столбца взять значение 0, 1, или 2, и суммировать данные из столбца «Разница». Результат вывести на другой лист на против соответствующего месяца (строка) и значения 0, 1 или 2 (столбец. Файл прикреплен к комментарию. Буду очень благодарен.
Готово https://sirexcel.ru/wp-content/uploads/2018/02/TIME-MANAGEMENT.xlsx
Здравствуйте! У меня такой вопрос — можно ли соедениеть формулу (суммесли) и (левсимв)?
Спасибо!
Можно. Просто с помощью ЛЕВСИМВ у вас получится число в формате текста. Его нужно положить прогнать через функцию ЗНАЧЕН и текст станет числом
Добрый день!
У меня таблица где есть дата, и сумма. Мне нужно суммировать данные с таблицы с условием что дата данной суммы не позднее 30 августа 2015 года. подскажите плиз как правильно прописать формулу
Добрый день!
Столкнулся с проблемой, никак не могу решить, помогите кто может посоветуйте…
Суть проблемы: нужны возможности формулы суммеслимн, только для работы с текстом, нужно переносить/выбирать текстовые данные с одного листа на другой согласно дате, номеру порядковому и Имени??? ВПР отлично это делает только при одном условии, как я могу это сделать когда условий много???
Заранее благодарю!
Нужно сделать дополнительный столбец слева от таблицы. Сцепить эти три типа данных
=A1&B1&C1
В итоге в этом столбце получится что-то в этом роде:
428575Иван
42857 — это дата в числовом формате
5 — это порядковый номер
Иван — Имя
И далее уже делаете ВПР по этому столбцу с тремя значениями
Вложение .xlsx
Добрый день! Подскажите пожалуйста, как прописать формулу, чтоб в зависимости от диапазона чисел суммировалось количество ячеек с совпадающим критерием
Вложение -2.xlsx
Например можно так. Смотрите пример
Вложение -топливо-пассажиры-груз.xls
подскажите как сделать, чтобы при переносе значений из ячеек одной таблице в другую шло сложение по нарастающей, но если в ячейке стоит ноль, то и при переносе был ноль
Вложение -топливо-пассажиры-груз-1.xls
То что, у вас таблицы одни вертикальные, а другие горизонтальные не очень удобно.
Я вы сделал вспомогательный на листе «Налет ЛС» и уже оттуда подтягивал бы в график. (см. вложение)
Вложение report.xlsx
Добрый день! Подскажите, пожалуйста, как подсчитать сумму продаж за определенный период по определенному артикулу в шутках и деньгах? В исходниках есть продажи по артикулам за каждый день (пример во вложении)
Спасибо заранее!
для суммы используйте СУММЕСЛИ
https://sirexcel.ru/priemi-excel/funkciya-summesli-v-excel-s-primerami/
для кол-ва используйте СЧЁТЕСЛИ
https://sirexcel.ru/priemi-excel/kak-poschitat-kolichestvo-yacheekznachenij-v-excel/
Есть у меня проблема, а где и кому задать вопрос, не знаю.
Есть два листа Excel. На первом — табель, в котором, в столбце В список сотрудников. На втором — расчёт зарплаты, где в столбце В аналогичный список. При изменении списка на первом листе, при удалении или добавлении ФИО, автоматически изменяется список на втором листе (ячейки связаны) Если сделать сортировку (от А до Я) на первом листе , то автоматически сортируется и список на втором листе. Вопрос! Как сделать так, чтобы диапазон ячеек правее столбца В на втором листе был привязан к соответствующей ячейке в столбце В и перемещался в соответствии с автоматической сортировкой. Пример: В ячейке В3 указано «Иванов», в ячейке С3 — «79»; в ячейке D3 — «0,75» и т.д. При сортировке на первом листе «Иванов» оказывается на 7 строке, то же самое происходит и на втором листе, а данные в ячейках на втором листе остаются на 3 строке. Мне нужно, чтобы они перемещались вместе с «Ивановым». Если кто знает как это сделать, помогите!
Вам поможет ВПР
Во второй таблице данные значений по расчету зарплаты должны быть не числа а формулы привязанные к ФИО. Если значения по ЗП берутся из первой таблицы, то используйте ВПР.
Если вы это рассчитываете по-другому или вставляете только значения, то сделайте отдельную таблицу (служебную, ее можно будет скрыть) куда необходимо снова ставить ФИО и данные по ЗП и из этой таблицы подтягивать значения ЗП с помощью все той же функции ВПР
Как создать фильтр городов ? Как вы создали фильтр на диапазоне G1 ?
Вот тут написано
https://sirexcel.ru/osvaivaem-excel/kak-sdelat-vypadayushhij-spisok-v-excel/
Вложение
А как быть, если в таблице есть ещё и заголовки и критерии меняются в зависимости от заголоков. В данном примере нужно вычленить личные звонки/смс и прочее из всех (личные+рабочие). Если заголовок «звонки на велком» или «звонки на мтс», то учитываются только те, что сделаны в выходной день (к примеру, 03.02.15) и до 8.00 или после 19.00. Причём, если оба условия выполняются, как к примеру в 5й строчке (и выходной — 03.02.15 и после 19.00), учитывать только один раз. Если заголовок «СМС» или «роуминг», то всё относить в личное. Т.е. решением должно быть: 100 (оба критерия выполняются) + 10 (выходной) + 20 (после 19.00) + 200 (смс всегда личные) + 2000 (роуминг всегда личные). Это возможно сделать с помощью формулы?
В таком виде трудновато будет сделать, тем более если кол-во дней все время меняется.
Я бы добавил вспомогательные столбец слева и присвоил бы каждой строчке тип услуги. В ячейке A3 написать формулу с ЕСЛИ, если B3=»», то =B4, иначе =В3
то есть мы привязываемся к пустой строке, которая отделяет типы звонков.
Далее можно сделать еще один вспомогательный столбец — где вернуть день недели (выходной)
После этого думаю уже можно посчитать
А можно как-нибудь автоматически «добавить каждой строчке тип услуги»? Писать вручную — не вариант: таблица более 5000 строк. Можно как-нибудь скопировать столбец и вставить в пустой столбец, но так, чтобы там были только надписи «звонки на велком», «звонки на мтс», «смс» и т.д. Т.е. вставился только текст, без цифр.
Либо как я писал выше сделать доп. столбец и привязаться к пустой строке и таким образом вернуть тип услуги напротив каждой даты
Либо если вам нужно «Можно как-нибудь скопировать столбец и вставить в пустой столбец, но так, чтобы там были только надписи», то сделать доп столбце и сделать проверку каждой строки с помощью функции ЕТЕКСТ
Идея в следующем. Делаем проверку соседней ячейки. Если это текст, то возвращаем эту ячейку с текстом, в другом случае — возвращаем пустую строчку. Протягиваем и получаем нужный вам вариант
Вложение -.xls
Здравствуйте.
Нужно написать формулу для переноса данных в зависимости от Ф.И.О., даты и марки автомобиля в табель учета рабочего времени. Сам пробовал, не получилось.
Заранее благодарен.
Вложение -1.xls
Сделал дополнительный лист, где все собрал в одну таблицу, потом построил сводную таблицу и уже оттуда подтянул данные.
Остальных людей подтягивайте по аналогии.
При обновлении данных, нужно обязательно обновить сводную таблицу, правой кнопкой по таблицы и выбрать «Обновить».
Можно и без сводной таблицы сделать такую же таблицу и подтянуть с помощью Индекс и Поискпоз.
Сейчас понимаю, что можно вообще без этого обойтись, после объединения делаем доп. столбец, объединяем дату и фамилию, а далее через ВПР подтягиваем данные по этому столбцу. Искомое значение будет ФИО&Дата