Splošna formula
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Povzetek
Za seštevanje vrednosti v ujemajočih se stolpcih in vrsticah lahko uporabite funkcijo SUMPRODUCT. V prikazanem primeru je formula v J6:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
kjer so podatki (C5: G14), dnevi (B5: B14) in kode (C4: G4) imenovani obsegi.
Pojasnilo
Funkcija SUMPRODUCT lahko izvirno obdeluje nize, ne da bi bilo treba vnesti nadzorni premik.
V tem primeru množimo vse vrednosti v imenovanih podatkih obsega z dvema izrazoma, ki filtrirata vrednosti, ki ne zanimajo. Prvi izraz uporabi filter, ki temelji na kodah:
(codes=J4)
Ker J4 vsebuje "A002", izraz ustvari niz TRUE FALSE vrednosti, kot je ta:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Drugi izraz filtrira na dan:
(days=J5)
Ker J4 vsebuje "Wed", izraz ustvari niz TRUE FALSE vrednosti, kot je ta:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
V Excelu vrednosti TRUE FALSE samodejno prisilijo na vrednosti 1 in 0 s katero koli matematično operacijo, zato operacija množenja prisili gornje nize na enote in ničle in ustvari 2D matriko z enakimi dimenzijami kot prvotni podatki. Postopek je mogoče vizualizirati, kot je prikazano spodaj:
Končno, SUMPRODUCT vrne vsoto vseh elementov v končnem polju, 9.
Šteje namesto vsote
Če želite namesto seštevanja šteti ujemajoče se vrednosti, lahko formulo skrajšate na:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Upoštevajte, da bo to število vključevalo prazne celice.
Opombe
- Čeprav primer prikazuje samo en ujemajoč se stolpec, bo ta formula pravilno seštela več ujemajočih se stolpcev.
- Če se morate ujemati samo s stolpci (ne z vrsticami), lahko uporabite takšno formulo.
- Če se želite ujemati samo z vrsticami, lahko uporabite funkcijo COUNTIFS.
