Обща формула
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Обобщение
За да сумирате стойности в съвпадащи колони и редове, можете да използвате функцията SUMPRODUCT. В показания пример формулата в J6 е:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
където данните (C5: G14), дните (B5: B14) и кодовете (C4: G4) са наречени диапазони.
Обяснение
Функцията SUMPRODUCT може да обработва масиви от самото начало, без да изисква въвеждане на смяна на контрола.
В този случай умножаваме всички стойности в посочените данни за диапазон по два израза, които филтрират стойности, които не представляват интерес. Първият израз прилага филтър, базиран на кодове:
(codes=J4)
Тъй като J4 съдържа "A002", изразът създава масив от TRUE FALSE стойности по следния начин:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Вторият израз филтрира за деня:
(days=J5)
Тъй като J4 съдържа „Ср.“, Изразът създава масив от TRUE FALSE стойности по следния начин:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
В Excel стойностите TRUE FALSE автоматично се принуждават към стойности 1 и 0 от всяка математическа операция, така че операцията за умножение принуждава масивите по-горе към единици и нули и създава 2D масив със същите размери като оригиналните данни. Процесът може да бъде визуализиран, както е показано по-долу:
И накрая, SUMPRODUCT връща сумата на всички елементи в крайния масив, 9.
Бройте вместо сума
Ако искате да преброите съвпадащи стойности вместо да сумирате, можете да съкратите формулата до:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Обърнете внимание, че този брой ще включва празни клетки.
Бележки
- Въпреки че примерът показва само една съвпадаща колона, тази формула правилно ще сумира множество съвпадащи колони.
- Ако трябва да съвпадате само с колони (а не с редове), можете да използвате формула като тази.
- За да съвпадате само с редове, можете да използвате функцията COUNTIFS.
