Как посчитать суммпроизв в excel

Функция СУММПРОИЗВ() — Сложение и подсчет с множественными условиями в EXCEL

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

Существует несколько вариантов применения функции СУММПРОИЗВ() :

• нахождение суммы произведений элементов списка (массива);
• суммирование и подсчет значений, удовлетворяющих определенным критериям;
• замена формул массива (в некоторых случаях).

Нахождение суммы произведений элементов массивов

В этом разделе показан синтаксис функции СУММПРОИЗВ() и раскрыт ее потенциал для других применений.

Пусть имеется 2 диапазона чисел A3:A6 и B3:B6 , содержащие соответственно 2 массива чисел : и . Записав формулу =СУММПРОИЗВ(A3:A6;B3:B6) , получим 123. Результат получен поэлементным перемножением всех элементов двух массивов, а затем сложением полученных произведений. То есть были выполнены следующие арифметические действия: 4*7 + 8*6 + 6*7 + 1*5= 123

Таким образом, можно найти сумму произведений 3-х, 4-х и т.д. массивов.

Аргументы, которые являются массивами, должны иметь одинаковые размерности (в нашем случае это массивы по 4 элемента). В противном случае функция СУММПРОИЗВ() возвращает значение ошибки #ЗНАЧ!.

В формуле =СУММПРОИЗВ(A3:A6;B3:B6) функция СУММПРОИЗВ() трактует нечисловые элементы массивов как нулевые. Однако, как показано ниже, функцию можно использовать для подсчета текстовых значений.

Что произойдет если указать только 1 массив, т.е. =СУММПРОИЗВ(A3:A6) ? Тогда функция СУММПРОИЗВ() вернет сумму элементов, т.е. будет эквивалентна функции СУММ() : =СУММ(A3:A6) .

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

• =СУММПРОИЗВ(A3:A6*2) , получим сумму произведений =38 (каждый элемент массива из A3:A6 был умножен на 2, затем все произведения просуммированы);
• =СУММПРОИЗВ(A3:A6*B3:B6) , получим результат суммы произведений – 123 (все элементы массивов были попарно перемножены, а затем сложены, т.е. A3*B3+ A4*B4+ A5*B5+ A6*B6), т.е. эта запись эквивалента формула =СУММПРОИЗВ(A3:A6;B3:B6) ;
• =СУММПРОИЗВ(A3:A6+B3:B6) , получим сумму элементов из двух диапазонов;
• =СУММПРОИЗВ(A3:A6/B3:B6 ), получим сумму попарных отношений всех элементов, т.е. 4/7 + 8/6 + 6/7 + 1/5= 2,9619

Аналогичные вычисления можно выполнить и с функцией СУММ() , только для этого нужно ее ввести как формулу массива , т.е. после ввода функции в ячейку вместо ENTER нажать CTRL+SHIFT+ENTER : =СУММ(A3:A6/B3:B6)

Прелесть функции СУММПРОИЗВ() в том, что после ввода функции в ячейку можно просто нажать ENTER , что снимает некий психологический барьер перед использованием формул массива .

Оказывается, что в качестве аргумента этой функции можно указать не только произведение массивов ( A3:A6*B3:B6 ), но и использовать другие функции и даже применить к массивам операции сравнения, т.е. использовать ее для сложения чисел, удовлетворяющих определенным условиям.

Суммирование и подсчет значений удовлетворяющих определенным критериям

Попробуем подсчитать число значений больших 2 в диапазоне A3:A6 , содержащий значения 4, 8, 6, 1.

Если мы запишем формулу =СУММПРОИЗВ(A3:A6>2) , то получим результат 0. Выделив в Строке формул A3:A6>2 и нажав клавишу F9 , получим массив , который говорит, что мы движемся в правильном направлении: в диапазоне A3:A6 больше 2 только первые 3 значения. Хотя значению ИСТИНА соответствует 1, а ЛОЖЬ – 0, мы не получим 3, т.к. для перевода значений ИСТИНА/ЛОЖЬ в числовую форму требуется применить к ним арифметическую операцию. Для этого можно, например, применить операцию двойного отрицания (—), что позволит привести массив в числовую форму .

Итак, задача подсчета значений больше 2 решается следующим образом: =СУММПРОИЗВ(—(A3:A6>2))

Вместо двойного отрицания можно использовать другие формулы: =СУММПРОИЗВ(1*(A3:A6>2)) или =СУММПРОИЗВ(0+(A3:A6>2)) или даже так =СУММПРОИЗВ((A3:A6>2)^1) .

Запись >2 является критерием, причем можно указать любые операции сравнения ( =; =).

Критерии можно указывать в форме ссылки: =СУММПРОИЗВ(—(A3:A6>G8)) – ячейка G 8 должна содержать число 2.

Критерии можно применять и к текстовым значениям, например, =СУММПРОИЗВ(—(B3:B6=»яблоки»)) – вернет количество ячеек, содержащие слово яблоки (подробнее, например, в статье Подсчет значений с множественными критериями (Часть 1. Условие И)) .

Функцию СУММПРОИЗВ() можно использовать для отбора значений по нескольким критериям (с множественными условиями). Как известно, 2 критерия могут образовывать разные условия:

• Условие ИЛИ . Например, подсчитать ячеек содержащих значение яблоки ИЛИ груши =СУММПРОИЗВ((B3:B6=»яблоки»)+ (B3:B6=»груши») ) ;
• Условие И . Например, подсчитать количество значений больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 )
• Условие И . Например, найти сумму Чисел больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 A3:A6 ) )

В файле примера приведены решения подобных задач.

СУММПРОИЗВ() – как формула массива

В ряде случаев (когда нужно подсчитать или сложить значения, удовлетворяющие определенным критериям) можно заменить использование формул массива функцией СУММПРОИЗВ() , например:

• =СУММПРОИЗВ(—ЕПУСТО(D2:D23)) подсчет пустых ячеек в диапазоне;
• =СУММПРОИЗВ(НАИБОЛЬШИЙ(A:A;)) сумма 3-х наибольших значений ;
• =СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) сумма значений, которые больше среднего .

Совет : Дополнительную информацию об этой функции можно подчерпнуть здесь (английский язык).

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

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

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

Синтаксис СУММПРОИЗВ

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

Простейший пример использования функции

Чтобы стало понятно, как и что считает команда, рассмотрим простой пример. Имеем таблицу с указанными длинами и ширинами прямоугольников. Нам нужно сосчитать сумму площадей всех прямоугольников. Если не пользоваться данной функцией, придется произвести промежуточные действия и сосчитать площадь каждого прямоугольника, а только потом сумму. Как мы и сделали.

Обратите внимание, что нам не понадобился массив с промежуточными итогами. В аргументах функции мы использовали только массивы с длиной и шириной, а функция их автоматически перемножила и просуммировала, выдав тот же результат = 70.

СУММПРОИЗВ с условием

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

Рассмотрим пример. Имеем таблицу затрат небольшой компании за один расчетный месяц. Необходимо сосчитать общую сумму потраченных средств за январь и февраль по всем статьям расходов.

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

• (А:А=»Январь») – первое условие;
• (Е:Е=»канцелярия») – второе условие;
• D:D – массив, из которого выводится итоговая сумма.

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

Сравнение в СУММПРОИЗВ

Одним из условий при использовании команды СУММПРОИЗВ может быть сравнение. Рассмотрим сразу на примере. Предположим, что нам нужно сосчитать не просто все расходы по канцелярии за январь, а только те, которые составляли меньше 1000 рублей (назовем их «мелкие расходы»). Прописываем функцию с теми же аргументами, но дополнительно проставляем оператор сравнения. В данном случае он выглядит как D:D ↑

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

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

В этом примере это список продуктов, в одном из которых перечисляется стоимость каждого товара, а в другом массиве — количество приобретаемых товаров.

1. Чтобы создать формулу, введите =СУММПРОИВЕД(B3:B6;C3:C6) и нажмите ввод.

Каждая ячейка в столбце B умножается на соответствующую ячейку в той же строке столбца C, и результаты добавляются. Общая сумма для продуктов составляет 21,60 долларов США.

1. Чтобы создать более длинную формулу, которая дает такой же результат, введите =B3*C3+B4*C4+B5*C5+B6*C6 и нажмите ввод.

После нажатия ввода результат будет таким же: 21,60 долларов США. Ячейка B3 умножается на C3, а ее результат добавляется к результату ячейки B4, умноженной на ячейку C4 и так далее. Представьте, что если у вас тысячи (или даже сотни) строк, то насколько проще было бы использовать суммпроидуц.

• Длина столбцов или строк (массивов) должна быть равной.
• Если текст, а не число, находится в ячейке, он будет обработан как нуль.

Пример

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

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

СУММПРОИЗВ (функция СУММПРОИЗВ)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Функция СУММПРОИВ ВОЗВРАЩАЕТ сумму продуктов соответствующих диапазонов или массивов. По умолчанию операция умножения, но возможна с добавлением, вычитанием и делением.

В этом примере мы используем СУММПРОИВ для возврата общего объема продаж для данного элемента и его размера:

SumPRODUCT соответствует всем экземплярам элемента Y/Size M и суммирует их, поэтому в данном примере «21 плюс 41» равен 62.

Синтаксис

Чтобы использовать операцию по умолчанию (умножение):

=СУММПРОИВ(массив1;[массив2];[массив3];. )

Аргументы функции СУММПРОИЗВ описаны ниже.

Первый массив, компоненты которого нужно перемножить, а затем сложить результаты.

[массив2], [массив3].

От 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.

Выполнение других арифметических операций

Используйте функцию СУММПРОИВ, как обычно, но вместо запятых, разделяющих аргументы массива, используйте нужные арифметические операторы (*, /, +, -). После выполнения всех операций результаты суммются обычным образом.

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

Примечания

• Аргументы, которые являются массивами, должны иметь одинаковые размерности. В противном случае функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. Например, =СУММПРОИВ(C2:C10;D2:D5) возвращает ошибку, так как диапазоны не одного размера.
• В функции СУММПРОИВТ ненумерические записи массива обрабатывают их так, как если бы они были нулями.
• Для лучшей производительности не следует использовать суммпроив с полными ссылками на столбцы. Рассмотрим функцию =СУММПРОИВ(A:A;B:B), чтобы умножить 1 048 576 ячеек в столбце A на 1 048 576 ячеек в столбце B перед их добавлением.

Пример 1

Чтобы создать формулу на примере выше, введите =СУММПРОИВ(C2:C5;D2:D5) и нажмитеввод . Каждая ячейка в столбце C умножается на соответствующую ячейку в той же строке столбца D, и результаты сбавляются. Общая сумма продуктов составляет 78,97 долларов США.

Чтобы ввести более длинную формулу, которая дает такой же результат, введите =C2*D2+C3*D3+C4*D4+C5*D5 и нажмите ввод . После нажатия ввод результат будет таким же: 78,97 долларов США. Ячейка C2 умножается на D2, а ее результат добавляется к результату ячейки C3, умноженной на ячейку D3 и так далее.

Пример 2

В следующем примере sumPRODUCT возвращает суммарные чистую сумму продаж по агенту продаж, у которых есть как общие продажи, так и расходы по агенту. В этом случае мы используем таблицу Excel, в которой используются структурированные ссылки вместо стандартных Excel диапазонов. Здесь вы увидите, что диапазоны «Продажи», «Расходы» и «Агент» имеют ссылку по имени.

Формула: =СУММПРОИМ(((Таблица1[Продажи])+(Таблица1[Расходы]))*(Таблица1[Агент]=B8)) и возвращает сумму всех продаж и расходов агента, указанных в ячейке B8.

Пример 3

В этом примере мы хотим получить общую сумму по конкретному товару, проданного в конкретном регионе. В данном случае, сколько вишней было продается в восточном регионе?

Вот формула: =СУММПРОИВ((B2:B9=B12)*(C2:C9=C12)*D2:D9). Сначала оно умножает количество вхождений восточного на количество совпадающих вишней. Наконец, она суммирует значения соответствующих строк в столбце Продажи. Чтобы узнать, Excel вычисляет формулу, выйдите из ячейки формулы, а затем перейдите в > Вычислить формулу >.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.