Formula generică
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
rezumat
Pentru a însuma valori în coloane și rânduri potrivite, puteți utiliza funcția SUMPRODUCT. În exemplul prezentat, formula din J6 este:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
unde datele (C5: G14), zilele (B5: B14) și codurile (C4: G4) sunt denumite intervale.
Explicaţie
Funcția SUMPRODUCT poate gestiona matrici în mod nativ, fără a necesita introducerea schimbării de control.
În acest caz, înmulțim toate valorile din datele intervalului denumit cu două expresii care filtrează valorile care nu sunt de interes. Prima expresie aplică un filtru bazat pe coduri:
(codes=J4)
Deoarece J4 conține „A002”, expresia creează o matrice de valori ADEVĂRATE FALSE ca aceasta:
(FALSE,TRUE,FALSE,FALSE,FALSE)
A doua expresie se filtrează în ziua:
(days=J5)
Deoarece J4 conține „Miercuri”, expresia creează o matrice de valori ADEVĂRATE FALSE ca aceasta:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
În Excel, valorile FALSE ADEVĂRATE sunt constrânse automat la valori 1 și 0 de orice operație matematică, astfel încât operația de multiplicare constrânge matricile de mai sus la unii și zerouri și creează o matrice 2D cu aceleași dimensiuni ca și datele originale. Procesul poate fi vizualizat după cum se arată mai jos:
În cele din urmă, SUMPRODUCT returnează suma tuturor elementelor din matricea finală, 9.
Numărați în loc de suma
Dacă doriți să numărați valorile potrivite în loc să însumați, puteți scurta formula la:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Rețineți că acest număr va include celule goale.
Note
- Deși exemplul arată o singură coloană potrivită, această formulă va însuma corect mai multe coloane potrivite.
- Dacă trebuie doar să potriviți coloane (nu rânduri), puteți utiliza o formulă de genul acesta.
- Pentru a potrivi doar rânduri, puteți utiliza funcția COUNTIFS.
