Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako izračunati "sumproduct if", vraćajući zbroj proizvoda niza ili raspona na temelju kriterija.
SUMPRODUCT funkcija
Funkcija SUMPRODUCT koristi se za množenje nizova brojeva, zbrajanje rezultirajućeg niza.
Da bismo stvorili “Sumproduct If”, upotrijebit ćemo funkciju SUMPRODUCT zajedno s funkcijom IF u formuli niza.
POVRŠAK AKO
Kombinirajući SUMPRODUCT i IF u formuli niza, možemo u biti stvoriti funkciju "SUMPRODUCT IF" koja radi slično kao što radi ugrađena funkcija SUMIF. Prođimo kroz primjer.
Imamo popis prodaje koje su menadžeri postigli u različitim regijama s odgovarajućim stopama provizije:
Pretpostavimo da se od nas traži da izračunamo iznos provizije za svakog menadžera na sljedeći način:
Da bismo to postigli, možemo ugnijezditi IF funkciju sa menadžer kako naši kriteriji unutar SUMPRODUCT -a funkcioniraju ovako:
= SUMPRODUCT (IF (=,*))
= SUMPRODUCT (AKO ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Kada koristite Excel 2022 i starije, formulu morate unijeti pritiskom na CTRL + SHIFT + ENTER da biste zaokružili zagrade oko formule (pogledajte gornju sliku).
Kako funkcionira formula?
Formula funkcionira ocjenjujući svaku ćeliju u našem rasponu kriterija kao TRUE ili FALSE.
Izračunavanje ukupne provizije za Oliviju:
= SUMPRODUCT (AKO ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
= SUMPRODUCT (IF ({TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
Zatim funkcija IF zamjenjuje svaku vrijednost s FALSE ako njezin uvjet nije ispunjen.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Sada funkcija SUMPRODUCT preskače vrijednosti FALSE i zbraja preostale vrijednosti (2.077,40).
SUMPRODUCT IF s više kriterija
Da biste koristili SUMPRODUCT IF s više kriterija (slično kao što radi ugrađena funkcija SUMIFS), jednostavno ugnijezdite više IF funkcija u funkciju SUMPRODUCT na sljedeći način:
= SUMPRODUCT (IF (=, IF (=, *))
(CTRL + SHIFT + ENTER)
= SUMPRODUCT (IF ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)))
(CTRL + SHIFT + ENTER)
Drugi pristup SUMPRODUCTU IF
Često u Excelu postoji više načina za postizanje željenih rezultata. Drugačiji način izračuna "sumproizvoda ako" uključuje kriterije unutar funkcija SUMPRODUCT kao niz koji koristi dvostruki unary ovako:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Ova metoda koristi dvostruki unary (-) za pretvaranje TRUE FALSE niza u nule i jedinice. SUMPRODUCT zatim množi pretvorene nizove kriterija zajedno:
= PODIZVOD ({1; 1; 0; 0; 0; 1; 0; 0; 0}, {1; 0; 1; 0; 1; 0; 0; 0; 0}, {928,62; 668,22; 919,695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})
Savjeti i trikovi:
- Kad god je to moguće, uvijek zaključajte (F4) svoje raspone i unose formule kako biste omogućili automatsko popunjavanje.
- Ako koristite Excel 2022 ili noviji, formulu možete unijeti bez Ctrl + Shift + Enter.
POVRŠAK AKO u Google tablicama
Funkcija SUMPRODUCT IF radi potpuno isto u Google tablicama kao i u Excelu:
