Общая формула
=SUMPRODUCT(--(range1=criteria),range2)
Резюме
Для суммирования или подытога столбцов на основе критериев в соседних столбцах можно использовать формулу, основанную на функции СУММПРОИЗВ. В показанном примере формула J5 имеет следующий вид:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Объяснение
По сути, эта формула использует СУММПРОИЗВ для умножения и суммирования произведений двух массивов: array1 и array2 . Первый массив, array1 , настроен как «фильтр», позволяющий разрешать только значения, соответствующие критериям.
Array1 использует диапазон, который начинается с первого столбца, содержащего значения, которые должны соответствовать критериям. Эти «значения критериев» находятся в столбце слева от «значений данных» и непосредственно рядом с ними.
Критерии применяются как простой тест, который создает массив значений ИСТИНА и ЛОЖЬ:
--($B5:$H5=J$4)
Этот бит формулы «проверяет» каждое значение в первом массиве с использованием предоставленных критериев, а затем использует двойное отрицание (-) для приведения результирующих значений ИСТИНА и ЛОЖЬ к единицам и нулям. Результат выглядит так:
(1,0,0,0,1,0,1)
Обратите внимание, что единицы соответствуют столбцам 1,5 и 7, которые соответствуют критерию «А».
Для array2 внутри SUMPRODUCT мы используем диапазон, который «сдвинут» на один столбец вправо. Этот диапазон начинается с первого столбца, содержащего значения для суммирования, и заканчивается последним столбцом, содержащим значения для суммирования.
Итак, в формуле примера в J5 после заполнения массивов мы имеем:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Поскольку SUMPRODUCT специально запрограммирован на игнорирование ошибок, возникающих в результате умножения текстовых значений, окончательный массив выглядит так:
(1,0,0,0,1,0,1)
Единственные значения, которые «выживают» при умножении, - это те, которые соответствуют единицам внутри array1 . Вы можете думать о логике в array1, «фильтрующей» значения в array2 .
