СУММПРОИЗВ

Функция СУММПРОИЗВ в Excel перемножает соответствующие элементы диапазонов и складывает произведения: =СУММПРОИЗВ(B2:B10;C2:C10) — это сумма «количество × цена» по всем строкам без вспомогательного столбца. Главное её применение — подсчёт сумм и количества с условиями прямо внутри формулы: =СУММПРОИЗВ((A2:A10="Москва")*(C2:C10>100)*D2:D10). Условия перемножаются (это И) или складываются (это ИЛИ), а формулу не нужно вводить через Ctrl+Shift+Enter. На английском функция называется SUMPRODUCT.

Что делает функция СУММПРОИЗВ

Эта функция использует как минимум два диапазона или массива, перемножая и суммируя их элементы.

Значения в первом диапазоне умножаются на соответствующие по порядку значения во втором и так далее.

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

Наиболее часто функция используется как аналог функции СУММЕСЛИ с множественными условиями.

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

СУММПРОИЗВ: формат результата и исходных данных

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

Диапазоны могут иметь несколько столбцов и строк одновременно. Обычно используются вертикальные диапазоны шириной 1 столбец.

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

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

Средневзвешенное значение и подсчёт с учётом регистра

СУММПРОИЗВ — стандартный способ посчитать средневзвешенную величину: сумма произведений значений на веса, делённая на сумму весов. А в связке с СОВПАД она считает ячейки с учётом регистра, чего не умеет СЧЁТЕСЛИ.

Примеры формул с функцией СУММПРОИЗВ

Рассмотрим подробно сложный пример с 4 условиями.

Задача маркетолога - посчитать сумму по четырем условиям:

1. Дата начала недели - 13 апреля или позднее
2. В названии кампании присутствует слово Brand
3. При этом кампания заканчивается на BY (Белоруссия)
4. Или заканчивается на UA (Украина)

Строки, удовлетворяющие всем четырем условиям, выделены зеленым цветом. Как составить формулу, которая учла бы только их?

Формулу можно наблюдать на скриншоте в ячейке H9.

Фактически она является суммой произведения двух диапазонов - массива чисел D3:D34 и массива нулей и единиц. Рассмотрим ее поближе:

Массив нулей и единиц рождается произведением трех формул массива, третья из которых получается сложением двух других формул массива и применением функции ЗНАК.

В каждой строке указано одно из четырех условий, на основе которых эти формулы создаются:

(ЗНАЧЕН(ЛЕВСИМВ($A$3:$A$34;8))>=$H$2)

это выражение создает массив булевых значений ИСТИНА и ЛОЖЬ. Функция ЛЕВСИМВ($A$3:$A$34;8) извлекает из диапазона дат в столбце A первые 8 символов, обозначающие дату, а функция ЗНАЧЕН преобразует их в числовой формат, иначе сравнение с ячейкой H2 не будет происходить корректно, т.к. функция ЛЕВСИМВ - текстовая, и возвращает строку.

НЕ(ЕОШ(ПОИСК($H$4;$B$3:$B$34)))

- это выражение также создает массив булевых значений. Функция ПОИСК ищет внутри каждой ячейки столбца слово "BRAND", если оно не найдено, возвращается ошибка, а если найдено - позиция его первого символа в строке. Функцией ЕОШ мы превращаем ошибки в единицы, а их отсутствия - в нули. И далее функцией отрицания НЕ получаем нужный нам массив.

Функций "Заканчивается на" в Excel нет, но можно делать простую проверку - если последние символы в строке совпадают со словом, что это, как не вхождение? Эту операцию и производит выражение

(ПРАВСИМВ($B$3:$B$34;ДЛСТР($J$2))=$J$2)

Тут нам помогают функции ДЛСТР и ПРАВСИМВ. Проверяя, совпадают ли последние 2 символа с ячейкой J2, мы также возвращаем массив булевых значений.

Четвертое условие аналогично третьему, как и его выражение:

(ПРАВСИМВ($B$3:$B$34;ДЛСТР($J$4))=$J$4)

Условная логика ИЛИ воспроизводится методом сложения двух булевых массивов. При этом они становятся числами. ИСТИНА+ЛОЖЬ (и наоборот) дают 1, ЛОЖЬ+ЛОЖЬ дают 0, ИСТИНА+ИСТИНА дают 2. Чтобы не исказить данные, мы используем функцию ЗНАК - она возвращает для положительных чисел 1, а для 0 - 0. Таким образом, мы получаем массив нулей и единиц на основе двух булевых массивов.

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

У наблюдательного читателя может возникнуть вопрос, почему мы не пользуемся функцией ИЛИ для объединения последних двух булевых массивов. Ответ: к сожалению, она не сочетает массивы попарно (как и функция И), а рассматривает каждую ячейку массива как равноправный элемент.

Именно поэтому используются арифметические функции.

• вместо И - умножение
• а вместо ИЛИ - сложение + функция ЗНАК.

Другие примеры с СУММПРОИЗВ:

Найти повторяющиеся значения в Excel

Почему СУММПРОИЗВ возвращает ошибку или 0

• #ЗНАЧ!. Диапазоны разного размера или в одном из них есть ошибка (#Н/Д, #ДЕЛ/0!).
• Результат 0. Условие сравнивается с числом, сохранённым как текст, или условия ИСТИНА/ЛОЖЬ переданы через «;» без умножения — используйте * или --.
• Формула тормозит. СУММПРОИЗВ по целым столбцам (A:A) пересчитывает миллион строк — ограничьте диапазон.
• ИЛИ работает неправильно. Функции И/ИЛИ не работают с массивами построчно — используйте умножение и сложение.
• Текст в диапазоне умножения через * даёт #ЗНАЧ!. Через «;» текст считается нулём, через * — ошибкой.

Функция СУММПРОИЗВ — частые вопросы

Что делает СУММПРОИЗВ в Excel?

Перемножает элементы массивов попарно и суммирует результат: =СУММПРОИЗВ(B2:B10;C2:C10).

Как СУММПРОИЗВ называется на английском?

SUMPRODUCT.

Как сделать СУММПРОИЗВ с условием?

Перемножьте условия и диапазон суммирования: =СУММПРОИЗВ((A2:A10="Москва")*C2:C10).

Чем СУММПРОИЗВ лучше СУММЕСЛИМН?

СУММПРОИЗВ принимает любые выражения в условиях — части текста, ИЛИ, вычисления над датами, учёт регистра. СУММЕСЛИМН быстрее, но работает только с готовыми значениями ячеек.

Нужно ли нажимать Ctrl+Shift+Enter?

Нет, СУММПРОИЗВ обрабатывает массивы сама в любой версии Excel.

Итоги: функция СУММПРОИЗВ в Excel

Надеюсь, на этой странице вы нашли то, что искали. СУММПРОИЗВ — универсальный инструмент для расчётов с условиями: умножение — это И, сложение — ИЛИ.