Všeobecný vzorec
=SUMPRODUCT(--(range1=criteria),range2)
Zhrnutie
Na sčítanie alebo medzisúčet stĺpcov na základe kritérií v susedných stĺpcoch môžete použiť vzorec založený na funkcii SUMPRODUCT. V zobrazenom príklade je vzorec v J5:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Vysvetlenie
Tento vzorec v jadre používa SUMPRODUCT na vynásobenie súčtov súčinov dvoch polí: array1 a array2 . Prvé pole, pole1 je nastavené tak, aby fungovalo ako „filter“, ktorý umožňuje iba hodnoty, ktoré spĺňajú kritériá.
Pole 1 používa rozsah začínajúci sa na prvom stĺpci, ktorý obsahuje hodnoty, ktoré musia vyhovovať kritériám. Tieto „hodnoty kritérií“ sú umiestnené v stĺpci naľavo od „údajových hodnôt“ a bezprostredne k nim.
Kritériá sa používajú ako jednoduchý test, ktorý vytvorí pole hodnôt TRUE a FALSE:
--($B5:$H5=J$4)
Tento bit vzorca „testuje“ každú hodnotu v prvom poli pomocou dodaných kritérií, potom použije dvojitý zápor (-) na vynútenie výsledných hodnôt TRUE a FALSE na čísla 1 a 0. Výsledok vyzerá takto:
(1,0,0,0,1,0,1)
Všimnite si, že 1 zodpovedá stĺpcom 1,5 a 7, ktoré vyhovujú kritériám „A“.
Pre pole2 vo vnútri SUMPRODUCT používame rozsah, ktorý je „posunutý“ o jeden stĺpec doprava. Tento rozsah sa začína prvým stĺpcom, ktorý obsahuje súhrnné hodnoty, a končí posledným stĺpcom, ktorý obsahuje hodnoty, ktoré sa majú sčítať.
Takže v vzorovom vzorci v J5 po vyplnení polí máme:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Pretože SUMPRODUCT je naprogramovaný špeciálne na ignorovanie chýb, ktoré sú výsledkom násobenia textových hodnôt, konečné pole vyzerá takto:
(1,0,0,0,1,0,1)
Jediné hodnoty, ktoré „prežijú“ násobenie, sú tie, ktoré zodpovedajú 1s vo vnútri poľa1 . Môžete myslieť na logiku v poli1, ktorá „filtruje“ hodnoty v poli2 .
