Суммирование, если значение совпадает с одним из нескольких заданных значений в Excel
Обычно значения легко суммировать по заданному критерию с помощью функции СУММЕСЛИ (SUMIF). Однако иногда возникает необходимость суммировать значения на основе списка элементов. Например, в моём диапазоне данных товары указаны в столбце A, а соответствующие суммы продаж — в столбце B. Мне нужно получить общую сумму продаж для товаров, перечисленных в диапазоне D4:D6, как показано на скриншоте ниже. Как быстро и просто решить эту задачу в Excel?
Суммирование, если значение совпадает с одним из нескольких заданных значений, с помощью функций СУММПРОИЗВ и СУММЕСЛИ
Если нужно просуммировать значения, для которых соответствующие ячейки совпадают с одним из нескольких заданных условий, используйте формулу на основе функций СУММПРОИЗВ (SUMPRODUCT) и СУММЕСЛИ (SUMIF). Общий синтаксис выглядит следующим образом:
- range: диапазон ячеек для сопоставления с диапазоном критериев;
- criteria_range: диапазон, содержащий значения, используемые в качестве критериев;
- sum_range: диапазон значений, по которым необходимо вычислить сумму.
Чтобы решить вышеуказанную задачу, примените приведённую ниже формулу, а затем нажмите клавишу Enter, чтобы получить результат:
Объяснение формулы:
=SUMPRODUCT(SUMIF(A2:A12,D2:D4,B2:B12))
- SUMIF(A2:A12,D2:D4,B2:B12): функция СУММЕСЛИ (SUMIF) возвращает три суммы — по одной для каждого элемента из диапазона критериев (D2:D4), учитывая соответствующие значения из диапазона суммирования B2:B12. В результате вы получите массив: {677;797;706}.
- SUMPRODUCT(SUMIF(A2:A12,D2:D4,B2:B12))=SUMPRODUCT({677,797,706}): функция СУММПРОИЗВ (SUMPRODUCT) суммирует все элементы массива и возвращает итоговый результат — 2180.
Используемая относительная функция:
- SUMPRODUCT:
- Функция СУММПРОИЗВ (SUMPRODUCT) позволяет умножать два или более столбцов или массивов, а затем суммировать полученные произведения.
- СУММЕСЛИ (SUMIF):
- Функция СУММЕСЛИ (SUMIF) позволяет суммировать ячейки по одному заданному критерию.
Другие статьи:
- Суммирование, если значение начинается или заканчивается определённым текстом или символами
- Чтобы суммировать значения, если соответствующие ячейки начинаются или заканчиваются определённым текстом, используйте функцию СУММЕСЛИ (SUMIF) с подстановочным знаком (*) — и эта статья подробно расскажет, как правильно применять такую формулу.
- Суммирование, если ячейки содержат определённый текст в другом столбце
- В этом руководстве показано, как суммировать значения, если ячейки в другом столбце содержат определённый или частичный текст. Рассмотрим приведённый ниже диапазон данных: чтобы получить общую сумму товаров, содержащих текст «T-Shirt», в Excel можно использовать как функцию СУММЕСЛИ (SUMIF), так и функцию СУММПРОИЗВ (SUMPRODUCT).
- Суммирование наименьших или последних N значений в Excel
- В Excel легко просуммировать диапазон ячеек с помощью функции СУММ (SUM). Однако иногда возникает необходимость сложить наименьшие или последние 3, 5 или n чисел в диапазоне данных — как показано на скриншоте ниже. В этом случае на помощь придут функции СУММПРОИЗВ (SUMPRODUCT) и НАИМЕНЬШИЙ (SMALL), которые вместе эффективно решат эту задачу в Excel.
Лучшие инструменты для повышения продуктивности в Office
Kutools для Excel — Помогает Вам выделяться из толпы
Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…
Office Tab — Включает вкладочное чтение и редактирование в Microsoft Office (включая Excel)
- Переключайтесь между десятками открытых документов всего за секунду!
- Сокращает сотни кликов мышью каждый день — забудьте о «мышечной руке» раз и навсегда!
- Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
- Добавляет эффективность вкладок в Office (включая Excel) — как в Chrome, Edge и Firefox.