Všeobecný vzorec
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Zhrnutie
Ak chcete sčítať hodnoty v zhodných stĺpcoch a riadkoch, môžete použiť funkciu SUMPRODUCT. V zobrazenom príklade je vzorec v J6:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
kde údaje (C5: G14), dni (B5: B14) a kódy (C4: G4) sú pomenované rozsahmi.
Vysvetlenie
Funkcia SUMPRODUCT dokáže natívne spracovať polia bez toho, aby vyžadovala zadanie ovládacieho posuvu.
V takom prípade vynásobíme všetky hodnoty v pomenovaných údajoch rozsahu dvoma výrazmi, ktoré odfiltrujú hodnoty, ktoré nás nezaujímajú. Prvý výraz použije filter založený na kódoch:
(codes=J4)
Pretože J4 obsahuje „A002“, výraz vytvára pole TRUE FALSE hodnôt, ako je táto:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Druhý výraz filtruje v deň:
(days=J5)
Pretože J4 obsahuje „Wed“, výraz vytvára pole TRUE FALSE hodnôt, ako je táto:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
V programe Excel sú TRUE FALSE hodnoty automaticky vynútené na hodnoty 1 a 0 ľubovoľnou matematickou operáciou, takže operácia násobenia vynúti vyššie uvedené polia na jednotky a nuly a vytvorí 2D pole s rovnakými rozmermi ako pôvodné údaje. Proces je možné vizualizovať, ako je uvedené nižšie:
Nakoniec program SUMPRODUCT vráti súčet všetkých prvkov v konečnom poli 9.
Počítajte namiesto súčtu
Ak chcete namiesto sčítania počítať zodpovedajúce hodnoty, môžete vzorec skrátiť na:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Upozorňujeme, že tento počet bude obsahovať prázdne bunky.
Poznámky
- Aj keď príklad zobrazuje iba jeden zhodný stĺpec, tento vzorec správne sčíta viac zhodných stĺpcov.
- Ak potrebujete zosúladiť iba stĺpce (nie riadky), môžete použiť nasledujúci vzorec.
- Ak chcete priradiť iba riadky, môžete použiť funkciu COUNTIFS.
