Обща формула
=SUMPRODUCT(--(range1=criteria),range2)
Обобщение
За да сумирате или междинни колони въз основа на критерии в съседни колони, можете да използвате формула, базирана на функцията SUMPRODUCT. В показания пример формулата в J5 е:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Обяснение
В основата си тази формула използва SUMPRODUCT за умножаване, след което сумиране на продукти от два масива: array1 и array2 . Първият масив, array1 е настроен да действа като "филтър", за да позволи само стойности, които отговарят на критериите.
Array1 използва диапазон, който започва от първата колона, която съдържа стойности, които трябва да преминат критерии. Тези "стойности на критериите" се намират в колона вляво и непосредствено до "стойностите на данните".
Критериите се прилагат като прост тест, който създава масив от TRUE и FALSE стойности:
--($B5:$H5=J$4)
Този бит от формулата "тества" всяка стойност в първия масив, използвайки предоставените критерии, след което използва двойно отрицателно (-), за да принуди получените стойности TRUE и FALSE към 1 и 0. Резултатът изглежда така:
(1,0,0,0,1,0,1)
Имайте предвид, че 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)
Единствените стойности, които „преживяват“ умножението, са тези, които съответстват на 1s вътре в array1 . Можете да мислите за логиката в array1 "филтриране" на стойностите в array2 .
