Üldine valem
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Kokkuvõte
Veerudes ja ridades olevate väärtuste summeerimiseks võite kasutada funktsiooni SUMPRODUCT. Näidatud näites on valem J6-s:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
kus andmeid (C5: G14), päevi (B5: B14) ja koode (C4: G4) nimetatakse vahemikeks.
Selgitus
Funktsioon SUMPRODUCT saab massiive käsitleda loomulikult, ilma et oleks vaja juhtnupu sisestamist.
Sel juhul korrutame kõik vahemiku andmete kõik väärtused kahe avaldisega, mis filtreerivad välja huvipakkuvad väärtused. Esimene avaldis rakendab filtritel põhinevat koodi:
(codes=J4)
Kuna J4 sisaldab "A002", loob avaldis massiivi TRUE FALSE väärtusi järgmiselt:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Teine avaldis filtreeritakse päeval:
(days=J5)
Kuna J4 sisaldab "Wed", loob avaldis massiivi TRUE FALSE väärtusi järgmiselt:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Excelis sunditakse mis tahes matemaatikaoperatsioonide abil TRUE FALSE väärtused automaatselt väärtusele 1 ja 0, nii et korrutamistoiming sunnib ülaltoodud massiive üksuste ja nullidena ning loob algdatutega samade mõõtmetega 2D massiivi. Protsessi saab visualiseerida, nagu allpool näidatud:
Lõpuks tagastab SUMPRODUCT viimase massiivi kõigi elementide summa 9.
Summa asemel loe
Kui soovite summeerimise asemel vastavaid väärtusi lugeda, saate valemit lühendada järgmiselt:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Pange tähele, et see arv sisaldab tühje lahtrid.
Märkused
- Kuigi näide näitab ainult ühte sobitatud veergu, võtab see valem mitu sobitatud veergu õigesti kokku.
- Kui peate vastama ainult veergudele (mitte ridadele), võite kasutada sellist valemit.
- Ainult ridade sobitamiseks võite kasutada funktsiooni COUNTIFS.
