Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel

В сегодняшней статье мы рассмотрим одну из самых популярных и мощных функций Excel – СУММПРОИЗВ. Изначально функция может показаться вам не такой полезной, как многие описывают. Но стоит начать работать со списками или массивами данных, польза СУММПРОИЗВ становится очевидной.

СУММПРОИЗВ – синтаксис формулы

Синтаксис формулы Excel суммы произведений довольно прост. Функция берет в качестве аргументов один или более массивов данных и возвращает сумму произведений соответствующих значений.

синтаксис формулы суммпроизв

Синтаксис функции выглядит следующим образом: =СУММПРОИЗВ(список1; список2 …)

То есть, если у вас есть массив данных {2;3;4} в одной колонке и {5;10;20} – в другой, и вы воспользовались функцией СУММПРОИЗВ, вы получите результат 120 (потому что 2*5+3*10+4*20=120).

пример функции суммпроизв

СУММПРОИЗВ и массивы

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

Вопрос довольно просто решаем с одним условием, но что если количество условий возрастает и нам необходимо узнать сумму продаж конкретного агента в конкретном регионе. У нас есть три пути:

На самом деле у нас есть еще один, скрытый путь, воспользоваться СУММПРОИЗВ.

Использование СУММПРОИЗВ в формулах массива

Если предположить, что данные находятся в диапазоне A2:C21, с именами агентов в колонке A, регионами – в колонке B и продажами – в колонке C, то формула СУММПРОИЗВ будет выглядеть следующим образом:

=СУММПРОИЗВ(- -(A2:A21="Агент Смит");- -(B2:B21="Запад");C2:C21)

Давайте разберемся, как работает формула:

  • Часть формулы (- -(A2:A21="Агент Смит" ищет Агента Смита в диапазоне A2:A21 и возвращает массив с единицами и нулями (единица,  если ячейка содержит Агента Смита, и ноль – если нет).
  • Часть — -(B2:B21="Запад") делает тоже самое, только возвращает единицу, если ячейка содержит Запад.
  • C2:C21 – просто возвращает массив с продажами

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

работа функции суммпроизв

Послесловие

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


11 комментариев

  1. А вот вопросик такой, второй вариант который вы описали повторяет формулу СУММЕСЛИМН, так вот не сталкивались ли вы какая будет работать быстрее? и где можно подробнее почитать про использование «- -» никогда раньше с этим не сталкивался?

    • Сергей, однозначно, формула СУММЕСЛИМН будет работать быстрее, так как встроенные формулы шустрее, чем пользовательские. Данный пример будет полезен читателям, которые используют старые версии Excel (до 2007), в которых данная функция не реализована.
      По поводу второго вопроса на англоязычных ресурсах об этом много написано, в любом поисковике введите «double dash excel», думаю, вы найдете ответ на свой вопрос.

  2. Сергей, однозначно, формула СУММЕСЛИМН будет работать быстрее, так как встроенные формулы шустрее, чем пользовательские
    СУММПРОИЗВ — это пользовательская функция?

  3. Добрый день.
    Одно из преимуществ функции СУММПРОИЗ в отличии от СУММЕСЛИ — это работа с закрытым файлом, т.е. СУММПРОИЗ рассчитывает результат, используя данные из другой рабочей книги, даже если она закрыта.
    Все было хорошо, пока длина диапазона данных (список1; список2 …) не стала переменной. Решила стандартное написание диапазона заменить на название колонки таблицы, например, (- -(Табл1[Агент]=»Агент Смит»)….), где Табл1 — таблица в другом файле. Пока этот другой файл открыт, все работает. Но при закрытом файле любой Update приводит СУММПРОИЗ к ошибке (Ref). Или я что-то делаю неправильно?

    • Добрый день, Лилия
      К сожалению, это слабое место Excel. Он не способен вытягивать динамические диапазоны с закрытых книг. Есть предложение производить все вычисления на стороне закрытой книги, а в книге, где это будет использоваться, помещать только ссылку на ячейку закрытой книги

  4. Допустим у нас в компании сидят «грамотеи» и пишут слова с ошибками: Агент Смит, Агент Смид, Запад, Запат и т.д. Вопрос:
    Как используются подстановочные знаки «*», «?» в функции «СУММПРОИЗВ»?
    Когда пишу =СУММПРОИЗВ(- -(A2:A21=»Агент*»);- -(B2:B21=»Запа?»);C2:C21), результат=0
    Заранее спасибо!

  5. Доброго времени суток.
    При помощи функции суммпроизводства пытаюсь посчитать, столбце с датами. Пишу в условии следующее. =Сегодня()-4
    Ошубку не выдает но не считает результат.
    А если пишу <=Сегодня()-4 то считает правильно по условию проводит расчет. Как правильно записать условие подскажите пожалуйста?

  6. Здравствуйте! Подскажите, как задать условие — если значение в ячейке $H$2=1, то ссылаться на столбец MATYEAR и возвращать 1, если MAT 17, если $H$2 равно не один, тогда ссылаться на соседний столбец. Писала условие через ЕСЛИ, но почему-то не работает.

    =ЕСЛИОШИБКА(СУММПРОИЗВ(—(Вкус=$B10);—(Форма=C$9);—(ЕСЛИ($H$2=1;MATYEAR=»MAT 17″;ЕСЛИ($H$2=2;Season=»summer»;ЕСЛИ($H$2=3;Season=»winter»;ЛОЖЬ))));—(MARKET=(ЕСЛИ($B$2=1;$A$2;ЕСЛИ($B$2=2;$A$3;ЕСЛИ($B$2=3;$A$4;0)))));ЕСЛИ($F$2=1;Value__in_1000_RUR;ЕСЛИ($F$2=2;Volume__in_1000_KG;ЕСЛИ($F$2=3;Units__in_1000_PACKS;ЛОЖЬ))));»»)

  7. Добрый день!

    Вопрос по второму примеру. Можно ли узнать через формулу, какие значения были использованы в расчете? Допустим ответ вышел 200 через данные 1*1*198+1*1*2=200
    Вопрос как можно по формуле узнать значения какие значения были просуммированы?

Добавить комментарий для Rustem Отменить ответ

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