Splošna formula
=SUMPRODUCT(--(range1=criteria),range2)
Povzetek
Za seštevanje ali vmesne seštevke stolpcev na podlagi meril v sosednjih stolpcih lahko uporabite formulo, ki temelji na funkciji SUMPRODUCT. V prikazanem primeru je formula v J5:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Pojasnilo
V jedru, ta formula uporablja SUMPRODUCT pomnožiti nato sešteje produktov dveh polj: array1 in array2 . Prvo polje array1 je nastavljeno tako, da deluje kot "filter", ki omogoča le vrednosti, ki izpolnjujejo merila.
Array1 uporablja obseg, ki se začne v prvem stolpcu in vsebuje vrednosti, ki morajo izpolnjevati merila. Te "vrednosti meril" se nahajajo v stolpcu levo od "vrednosti podatkov" in tik ob njih.
Merila se uporabljajo kot preprost test, ki ustvari nabor TRUE in FALSE vrednosti:
--($B5:$H5=J$4)
Ta bit formule "preskusi" vsako vrednost v prvem polju z uporabo priloženih kriterijev, nato pa z dvojnim negativom (-) prisili nastale vrednosti TRUE in FALSE na 1 in 0. Rezultat je videti takole:
(1,0,0,0,1,0,1)
Upoštevajte, da enoti ustrezata stolpcem 1,5 in 7, ki izpolnjujeta merila "A".
Za array2 znotraj SUMPRODUCT uporabljamo obseg, ki ga za en stolpec "premakne" v desno. Ta obseg se začne s prvim stolpcem, ki vsebuje vrednosti, ki se seštejejo, in konča z zadnjim stolpcem, ki vsebuje vrednosti, ki se seštejejo.
Torej, v primeru formule v J5 imamo po zapolnitvi nizov:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Ker je SUMPRODUCT posebej programiran tako, da prezre napake, ki nastanejo pri množenju besedilnih vrednosti, je končna matrika videti takole:
(1,0,0,0,1,0,1)
Edine vrednosti, ki "preživijo" množenje, so tiste, ki ustrezajo 1s znotraj array1 . Si lahko zamislite logike v array1 "filtriranje" vrednosti v array2 .
