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

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

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

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

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

Функция Суммеслимн - исходные данные таблицы

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

Для наглядности я перенес данную таблицу на один лист с исходными данными.

Функция Суммеслимн - исходные данные с таблицей

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

СУММЕСЛИМН(диапазон_суммирования;диапазон_условий1;условия1;[диапазон_условий2;условия2];…)
диапазон_суммирования — В нашем случае нам необходимо просуммировать количество подключенных услуг, поэтому это столбец Количество и диапазон Е2:E646

Далее указываются условия по которым необходимо просуммировать услуги. У нас три условия:

  1. должна совпадать фамилия сотрудника;
  2. должна совпадать услуга;
  3. должен совпадать город.

диапазон_условий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)

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

Пример файла: Скачать

 

Поделиться:
24 Комментария
  • Sabina says:

    Вложение  -лист.xlsx

    Добрый день, есть эксель для подсчета отсутствий, отгулов и отпусков сотрудников. Не могу изменить формулу суммеслимн, чтобы в отпуске считала и выходные дни.У нас 7 дневная рабочая неделя, а формула изначально так поставлена-не считает выходные дни.

  • Равшан says:

    Вложение  TIME-MANAGEMENT.xlsx

    Здравствуйте. С функцией СУММЕСЛИМН у меня выходит #ЗНАЧ!
    Я хочу задать следующее условие: Из столбца “Месяц” листа ТМ взять соответствующий месяц, из следующего столбца взять значение 0, 1, или 2, и суммировать данные из столбца “Разница”. Результат вывести на другой лист на против соответствующего месяца (строка) и значения 0, 1 или 2 (столбец. Файл прикреплен к комментарию. Буду очень благодарен.

  • Сергей says:

    Здравствуйте! У меня такой вопрос – можно ли соедениеть формулу (суммесли) и (левсимв)?
    Спасибо!

    • excel says:

      Можно. Просто с помощью ЛЕВСИМВ у вас получится число в формате текста. Его нужно положить прогнать через функцию ЗНАЧЕН и текст станет числом

  • Алибек says:

    Добрый день!
    У меня таблица где есть дата, и сумма. Мне нужно суммировать данные с таблицы с условием что дата данной суммы не позднее 30 августа 2015 года. подскажите плиз как правильно прописать формулу

  • Эрик says:

    Добрый день!
    Столкнулся с проблемой, никак не могу решить, помогите кто может посоветуйте…
    Суть проблемы: нужны возможности формулы суммеслимн, только для работы с текстом, нужно переносить/выбирать текстовые данные с одного листа на другой согласно дате, номеру порядковому и Имени??? ВПР отлично это делает только при одном условии, как я могу это сделать когда условий много???
    Заранее благодарю!

    • excel says:

      Нужно сделать дополнительный столбец слева от таблицы. Сцепить эти три типа данных
      =A1&B1&C1
      В итоге в этом столбце получится что-то в этом роде:
      428575Иван
      42857 – это дата в числовом формате
      5 – это порядковый номер
      Иван – Имя
      И далее уже делаете ВПР по этому столбцу с тремя значениями

  • Илья says:

    Вложение  .xlsx

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

    • excel says:

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

      Например можно так. Смотрите пример

  • Андрей says:

    подскажите как сделать, чтобы при переносе значений из ячеек одной таблице в другую шло сложение по нарастающей, но если в ячейке стоит ноль, то и при переносе был ноль

    • excel says:

      То что, у вас таблицы одни вертикальные, а другие горизонтальные не очень удобно.
      Я вы сделал вспомогательный на листе “Налет ЛС” и уже оттуда подтягивал бы в график. (см. вложение)

  • Мария says:

    Вложение  report.xlsx

    Добрый день! Подскажите, пожалуйста, как подсчитать сумму продаж за определенный период по определенному артикулу в шутках и деньгах? В исходниках есть продажи по артикулам за каждый день (пример во вложении)
    Спасибо заранее!

  • Сергей says:

    Есть у меня проблема, а где и кому задать вопрос, не знаю.
    Есть два листа Excel. На первом – табель, в котором, в столбце В список сотрудников. На втором – расчёт зарплаты, где в столбце В аналогичный список. При изменении списка на первом листе, при удалении или добавлении ФИО, автоматически изменяется список на втором листе (ячейки связаны) Если сделать сортировку (от А до Я) на первом листе , то автоматически сортируется и список на втором листе. Вопрос! Как сделать так, чтобы диапазон ячеек правее столбца В на втором листе был привязан к соответствующей ячейке в столбце В и перемещался в соответствии с автоматической сортировкой. Пример: В ячейке В3 указано “Иванов”, в ячейке С3 – “79”; в ячейке D3 – “0,75” и т.д. При сортировке на первом листе “Иванов” оказывается на 7 строке, то же самое происходит и на втором листе, а данные в ячейках на втором листе остаются на 3 строке. Мне нужно, чтобы они перемещались вместе с “Ивановым”. Если кто знает как это сделать, помогите!

    • excel says:

      Вам поможет ВПР
      Во второй таблице данные значений по расчету зарплаты должны быть не числа а формулы привязанные к ФИО. Если значения по ЗП берутся из первой таблицы, то используйте ВПР.
      Если вы это рассчитываете по-другому или вставляете только значения, то сделайте отдельную таблицу (служебную, ее можно будет скрыть) куда необходимо снова ставить ФИО и данные по ЗП и из этой таблицы подтягивать значения ЗП с помощью все той же функции ВПР

  • Алла says:

    Как создать фильтр городов ? Как вы создали фильтр на диапазоне G1 ?

  • Надежда says:

    Вложение

    А как быть, если в таблице есть ещё и заголовки и критерии меняются в зависимости от заголоков. В данном примере нужно вычленить личные звонки/смс и прочее из всех (личные+рабочие). Если заголовок “звонки на велком” или “звонки на мтс”, то учитываются только те, что сделаны в выходной день (к примеру, 03.02.15) и до 8.00 или после 19.00. Причём, если оба условия выполняются, как к примеру в 5й строчке (и выходной – 03.02.15 и после 19.00), учитывать только один раз. Если заголовок “СМС” или “роуминг”, то всё относить в личное. Т.е. решением должно быть: 100 (оба критерия выполняются) + 10 (выходной) + 20 (после 19.00) + 200 (смс всегда личные) + 2000 (роуминг всегда личные). Это возможно сделать с помощью формулы?

    • excel says:

      В таком виде трудновато будет сделать, тем более если кол-во дней все время меняется.
      Я бы добавил вспомогательные столбец слева и присвоил бы каждой строчке тип услуги. В ячейке A3 написать формулу с ЕСЛИ, если B3=””, то =B4, иначе =В3
      то есть мы привязываемся к пустой строке, которая отделяет типы звонков.

      Далее можно сделать еще один вспомогательный столбец – где вернуть день недели (выходной)
      После этого думаю уже можно посчитать

      • Надежда says:

        А можно как-нибудь автоматически “добавить каждой строчке тип услуги”? Писать вручную – не вариант: таблица более 5000 строк. Можно как-нибудь скопировать столбец и вставить в пустой столбец, но так, чтобы там были только надписи “звонки на велком”, “звонки на мтс”, “смс” и т.д. Т.е. вставился только текст, без цифр.

        • excel says:

          Либо как я писал выше сделать доп. столбец и привязаться к пустой строке и таким образом вернуть тип услуги напротив каждой даты
          Либо если вам нужно “Можно как-нибудь скопировать столбец и вставить в пустой столбец, но так, чтобы там были только надписи”, то сделать доп столбце и сделать проверку каждой строки с помощью функции ЕТЕКСТ
          Идея в следующем. Делаем проверку соседней ячейки. Если это текст, то возвращаем эту ячейку с текстом, в другом случае – возвращаем пустую строчку. Протягиваем и получаем нужный вам вариант

  • Владимир says:

    Вложение  -.xls

    Здравствуйте.
    Нужно написать формулу для переноса данных в зависимости от Ф.И.О., даты и марки автомобиля в табель учета рабочего времени. Сам пробовал, не получилось.
    Заранее благодарен.

    • excel says:

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

      Сделал дополнительный лист, где все собрал в одну таблицу, потом построил сводную таблицу и уже оттуда подтянул данные.
      Остальных людей подтягивайте по аналогии.
      При обновлении данных, нужно обязательно обновить сводную таблицу, правой кнопкой по таблицы и выбрать “Обновить”.

      Можно и без сводной таблицы сделать такую же таблицу и подтянуть с помощью Индекс и Поискпоз.
      Сейчас понимаю, что можно вообще без этого обойтись, после объединения делаем доп. столбец, объединяем дату и фамилию, а далее через ВПР подтягиваем данные по этому столбцу. Искомое значение будет ФИО&Дата

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

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

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