Üldine valem
=SUMPRODUCT(--(range1=criteria),range2)
Kokkuvõte
Külgnevate veergude kriteeriumide alusel veergude summeerimiseks või vahesummatamiseks võite kasutada funktsiooni SUMPRODUCT põhinevat valemit. Näidatud näites on valem J5-s:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Selgitus
Keskmes see valem kasutab SUMPRODUCT korrutada siis Kokkuvõttes toodete kahe massiivi: massiiv1 ja massiiv2 . Esimene massiiv array1 on loodud toimima "filtrina", et lubada ainult kriteeriumidele vastavaid väärtusi.
Massiiv1 kasutab vahemikku, mis algab esimesest veerust ja sisaldab väärtusi, mis peavad läbima kriteeriumid. Need "kriteeriumiväärtused" asuvad veerus "andmeväärtustest" vasakul ja vahetult nende kõrval.
Kriteeriume rakendatakse lihtsa testina, mis loob massiivi TRUE ja FALSE väärtustest:
--($B5:$H5=J$4)
See valemibitt "testib" kõiki massiivi kõiki väärtusi, kasutades selleks esitatud kriteeriume, seejärel kasutab topeltnegatiivset (-), et sundida saadud TÕENE ja VALE väärtus 1-le ja 0-le. Tulemus näeb välja selline:
(1,0,0,0,1,0,1)
Pange tähele, et 1-d vastavad veergudele 1,5 ja 7, mis vastavad "A" kriteeriumidele.
Sest massiiv2 sees SUMPRODUCT, me kasutame vahemik, mis on "nihutatud" üks veerg paremale. See vahemik algab sellest, et esimene veerg sisaldab summeeritavaid väärtusi ja lõpeb viimase veeruga, mis sisaldab summeeritavaid väärtusi.
Niisiis on J5 näite valemis pärast massiivide täitmist:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Kuna SUMPRODUCT on programmeeritud spetsiaalselt tekstiväärtuste korrutamisel tekkivate vigade eiramiseks, näeb viimane massiiv välja selline:
(1,0,0,0,1,0,1)
Ainus väärtused "ellu jääda" korrutamine on need, mis vastavad 1s sees massiiv1 . Võite mõelda massiivi 1 loogikale, mis "filtreerib" massiivi2 väärtusi .
