СУММПРОИЗВ в Excel: несколько условий и практические примеры
СУММПРОИЗВ умеет не только складывать произведения количества и цены. Логические выражения превращают её в компактный расчёт по нескольким условиям. Разберём механику на понятных таблицах, научимся задавать И и ИЛИ и найдём причины неверного результата.
Что делает СУММПРОИЗВ
Функция перемножает соответствующие элементы массивов, а затем складывает полученные произведения. Для количества в B2:B5 и цены в C2:C5 базовый расчёт выглядит так:
=СУММПРОИЗВ(B2:B5;C2:C5)Если строки содержат 2 × 500, 3 × 700, 1 × 1 200 и 4 × 250, результат равен 5 300. Сначала считаются четыре произведения, затем они складываются.
Согласно официальной справке Microsoft по СУММПРОИЗВ, массивы должны иметь одинаковые размеры, иначе Excel возвращает ошибку #ЗНАЧ!. Нечисловые элементы в обычных аргументах массива рассматриваются как нули.
Таблица для примеров с условиями
| Строка | A: город | B: категория | C: сумма |
|---|---|---|---|
| 2 | Москва | Курсы | 12 500 |
| 3 | Москва | Книги | 4 800 |
| 4 | Казань | Курсы | 9 700 |
| 5 | Москва | Курсы | 8 300 |
| 6 | Казань | Книги | 3 100 |
| 7 | Санкт-Петербург | Курсы | 6 000 |
В F2 запишем «Москва», в F3 — «Казань», а в G2 — «Курсы». Критерии лучше хранить в ячейках, а не встраивать в десятки формул: так их проще менять и проверять.
Несколько обязательных условий: логика И
Нужно сложить только продажи курсов в Москве. Сравнение каждого значения A2:A7 с F2 создаёт массив ИСТИНА/ЛОЖЬ. Умножение преобразует его в единицы и нули. То же происходит с категорией, поэтому в итог попадёт строка, где оба условия истинны:
=СУММПРОИЗВ((A2:A7=F2)*(B2:B7=G2)*C2:C7)Подходящие строки — 2 и 5. Результат: 12 500 + 8 300 = 20 800.
| Город совпал | Категория совпала | Произведение условий | Строка входит |
|---|---|---|---|
| 1 | 1 | 1 | Да |
| 1 | 0 | 0 | Нет |
| 0 | 1 | 0 | Нет |
Добавить третье обязательное условие можно ещё одним множителем, например *(D2:D7>=ДАТА(2026;1;1)), но диапазон D2:D7 должен содержать реальные даты Excel и совпадать по размеру с остальными.
Альтернативные условия: логика ИЛИ без двойного счёта
Теперь нужно сложить продажи из Москвы или Казани. Сложение двух логических массивов даёт 1, если сработало одно условие, и может дать 2, если условия пересекаются. Чтобы каждая строка учитывалась не более одного раза, проверим, что сумма условий больше нуля:
=СУММПРОИЗВ(((A2:A7=F2)+(A2:A7=F3)>0)*C2:C7)В расчёт войдут строки 2–6, а строка Санкт-Петербурга не войдёт. Результат равен 38 400.
Формула «город Москва ИЛИ сумма больше 10 000» пересекается на строке 2: там истинны оба условия. Если просто умножить сумму условий на C2:C7, строка 2 будет посчитана дважды. Проверка >0 превращает любое положительное число обратно в единицу.
Общая логика сочетания условий И и ИЛИ описана в справке Microsoft. В СУММПРОИЗВ она применяется к массивам строк, а не к одному логическому значению.
Числа как текст, пустые ячейки и ошибки
Формула может быть логически правильной, но давать неожиданный итог из-за качества исходной таблицы.
| Проблема | Что происходит | Что сделать |
|---|---|---|
| Число сохранено как текст | Значение может быть принято за ноль или вести себя иначе в арифметическом выражении | Преобразовать столбец в числа и проверить итог |
| Пустая ячейка | В числовом массиве обычно даёт нулевой вклад | Уточнить, означает ли пустота ноль или пропуск данных |
| #Н/Д, #ЗНАЧ! и другая ошибка | Ошибка может распространиться на весь результат | Найти источник, исправить либо обрабатывать ошибку осознанно |
| Лишний пробел в тексте | Критерий «Москва» не совпадает с «Москва » | Очистить текст и проверить уникальные значения |
| Диапазоны разной длины | Excel возвращает #ЗНАЧ! | Выровнять первую и последнюю строки всех массивов |
Не подменяйте очистку данных сложной формулой. Если отчёт обновляется регулярно, сначала приведите столбцы к стабильным типам, а затем считайте.
Когда выбрать другую функцию
| Задача | Понятный инструмент |
|---|---|
| Сумма по нескольким обычным критериям | СУММЕСЛИМН — формула легче читается коллегами |
| Количество строк по условиям | СЧЁТЕСЛИМН |
| Произведение двух массивов или сложная массивная логика | СУММПРОИЗВ |
| Условие трудно объяснить одной строкой | Вспомогательный столбец с прозрачной проверкой |
| Регулярная обработка больших выгрузок | Структурированная таблица, сводный отчёт или Power Query |
Начинающим полезно сначала разобраться в основных формулах Excel, а затем выбирать СУММПРОИЗВ там, где её массивная логика действительно сокращает расчёт.
Как проверить сложную формулу
- проверьте каждый критерий отдельно, посчитав подходящие строки фильтром;
- замените суммируемый диапазон на единицы и убедитесь, что число строк верно;
- сравните итог с ручной суммой на небольшом наборе;
- добавьте строку, которая подходит под оба условия ИЛИ, и проверьте отсутствие дубля;
- добавьте текстовое число, пустоту и ошибку в копии таблицы;
- не используйте целые столбцы без необходимости: большие массивы замедляют пересчёт.
Краткий чек-лист
- все массивы начинаются и заканчиваются на одинаковых строках;
- числовой диапазон действительно содержит числа;
- умножение означает И, а сложение — ИЛИ;
- пересекающиеся условия ИЛИ приведены к проверке больше нуля;
- ошибки и пробелы исходных данных устранены;
- для простой задачи не выбрана неоправданно сложная формула;
- результат проверен на небольшом контрольном примере.
СУММПРОИЗВ становится понятной, если видеть за формулой массив единиц и нулей. Сначала сформулируйте условие словами, затем постройте маску и только после этого умножайте её на значения.
Перейдите от одной формулы к уверенной работе с данными и отчётами
Курс «Excel + «Google Таблицы» с нуля до PRO» развивает практические навыки от структуры таблиц и формул до подготовки данных, отчётов и автоматизации. По итогам обучения выдаётся Удостоверение о повышении квалификации.
Вопросы и ответы
- Почему СУММПРОИЗВ возвращает ошибку #ЗНАЧ!?
Частая причина — массивы разного размера. Также проверьте ошибки в исходных ячейках и прямые арифметические операции с текстом вместо чисел.
- Как задать несколько условий И в СУММПРОИЗВ?
Запишите каждое сравнение в скобках и перемножьте их. Единица останется только в строках, где истинны все условия, после чего маску умножают на суммируемый диапазон.
- Как сделать условие ИЛИ и не посчитать строку дважды?
Сложите логические условия, затем сравните сумму с нулём: выражение больше нуля даст одну единицу даже тогда, когда в строке совпали сразу два критерия.
- Почему числа в текстовом формате не попали в сумму?
СУММПРОИЗВ не обязана воспринимать текстовую запись числа как числовое значение. Приведите весь столбец к числовому типу и проверьте разделители и скрытые пробелы.
- Что лучше: СУММПРОИЗВ или СУММЕСЛИМН?
Для обычной суммы по нескольким критериям СУММЕСЛИМН чаще понятнее. СУММПРОИЗВ удобна для суммы произведений и массивной логики, которую трудно выразить стандартными критериями.