Formula generică
=SUMPRODUCT(--(range1=criteria),range2)
rezumat
Pentru a suma sau subtotalul coloanelor pe baza criteriilor din coloanele adiacente, puteți utiliza o formulă bazată pe funcția SUMPRODUCT. În exemplul prezentat, formula din J5 este:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Explicaţie
La bază, această formulă folosește SUMPRODUCT pentru a multiplica apoi suma produselor a două matrice: matrice1 și matrice2 . Prima matrice, matricea1 este configurată să acționeze ca un „filtru” pentru a permite numai valorile care îndeplinesc criteriile.
Array1 folosește un interval care începe în prima coloană care conține valori care trebuie să treacă criteriile. Aceste „valori ale criteriilor” stau într-o coloană din stânga și imediat adiacente „valorilor datelor”.
Criteriul este aplicat ca un test simplu care creează o serie de valori ADEVĂRATE și FALSE:
--($B5:$H5=J$4)
Acest bit din formulă „testează” fiecare valoare din prima matrice utilizând criteriile furnizate, apoi folosește un dublu negativ (-) pentru a constrânge valorile ADEVĂRATE și FALSE rezultate la 1 și 0. Rezultatul arată astfel:
(1,0,0,0,1,0,1)
Rețineți că 1 corespunde coloanelor 1,5 și 7, care îndeplinesc criteriile „A”.
Pentru array2 din interiorul SUMPRODUCT, folosim un interval care este „deplasat” cu o coloană spre dreapta. Acest interval începe cu prima coloană care conține valori de sumat și se termină cu ultima coloană care conține valori de sumat.
Deci, în formula de exemplu din J5, după ce matricile au fost populate, avem:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Deoarece SUMPRODUCT este programat special pentru a ignora erorile care rezultă din multiplicarea valorilor textului, matricea finală arată astfel:
(1,0,0,0,1,0,1)
Singurele valori care „supraviețuiesc” înmulțirii sunt cele care corespund cu 1s din matrice1 . Vă puteți gândi la logica din array1 „filtrând” valorile din array2 .
