Generieke formule
=SUMPRODUCT(--(range1=criteria),range2)
Samenvatting
Als u kolommen wilt optellen of subtotalen op basis van criteria in aangrenzende kolommen, kunt u een formule gebruiken die is gebaseerd op de functie SOMPRODUCT. In het getoonde voorbeeld is de formule in J5:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Uitleg
De kern, deze formule gebruikt SOMPRODUCT te vermenigvuldigen dan som producten van twee arrays: array1 en matrix2 . De eerste array, array1, is ingesteld om te werken als een "filter" om alleen waarden toe te staan die voldoen aan criteria.
Array1 gebruikt een bereik dat begint op de eerste kolom die waarden bevat die aan criteria moeten voldoen. Deze "criteriumwaarden" bevinden zich in een kolom links van en direct naast de "gegevenswaarden".
Het criterium wordt toegepast als een eenvoudige test die een reeks TRUE en FALSE waarden creëert:
--($B5:$H5=J$4)
Dit bit van de formule "test" elke waarde in de eerste array met behulp van de opgegeven criteria, en gebruikt vervolgens een dubbele negatieve (-) om de resulterende WAAR en ONWAAR waarden te dwingen tot enen en nullen. Het resultaat ziet er als volgt uit:
(1,0,0,0,1,0,1)
Merk op dat enen corresponderen met kolommen 1,5 en 7, die voldoen aan de criteria van "A".
Voor array2 binnen SUMPRODUCT gebruiken we een bereik dat één kolom naar rechts is "verschoven". Dit bereik begint met de eerste kolom die waarden bevat om op te tellen en eindigt met de laatste kolom die waarden bevat om op te tellen.
Dus in de voorbeeldformule in J5, nadat de arrays zijn gevuld, hebben we:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Aangezien SUMPRODUCT specifiek is geprogrammeerd om de fouten te negeren die het gevolg zijn van het vermenigvuldigen van tekstwaarden, ziet de uiteindelijke array er als volgt uit:
(1,0,0,0,1,0,1)
De enige waarden die vermenigvuldiging "overleven", zijn die welke overeenkomen met 1s in array1 . U kunt denken aan de logica in matrix1 "filteren" de waarden in matrix2 .
