Generička formula
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Sažetak
Da biste zbrojili vrijednosti u podudarnim stupcima i redovima, možete koristiti funkciju SUMPRODUCT. U prikazanom primjeru formula u J6 je:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
gdje su podaci (C5: G14), dani (B5: B14) i kodovi (C4: G4) imenovani rasponi.
Obrazloženje
Funkcija SUMPRODUCT može izvorno obrađivati nizove, bez potrebe za unosom upravljačkog pomaka.
U ovom slučaju množimo sve vrijednosti u navedenom rasponu podataka s dva izraza koji filtriraju vrijednosti koje nisu od interesa. Prvi izraz primjenjuje filtar zasnovan na kodovima:
(codes=J4)
Budući da J4 sadrži "A002", izraz stvara niz TRUE FALSE vrijednosti poput ove:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Drugi filtri izraza na dan:
(days=J5)
Budući da J4 sadrži "Wed", izraz stvara niz TRUE FALSE vrijednosti poput ove:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
U Excelu se vrijednosti TRUE FALSE automatski prisiljavaju na vrijednosti 1 i 0 bilo kojom matematičkom operacijom, pa operacija množenja prisiljava gornje nizove na jedinice i nule i stvara 2D niz s istim dimenzijama kao i izvorni podaci. Postupak se može vizualizirati kako je prikazano u nastavku:
Konačno, SUMPRODUCT vraća zbroj svih elemenata u konačnom nizu, 9.
Broji umjesto zbroja
Ako želite brojati odgovarajuće vrijednosti umjesto zbrajanja, možete skratiti formulu na:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Imajte na umu da će ovaj broj sadržavati prazne ćelije.
Bilješke
- Iako primjer prikazuje samo jedan podudarni stupac, ova će formula ispravno zbrojiti više podudarnih stupaca.
- Ako trebate samo podudarati stupce (ne i retke), možete koristiti ovakvu formulu.
- Da biste podudarali samo retke, možete koristiti funkciju COUNTIFS.
