Subtotaal gebruiken met sumproduct in excel

U kunt de volgende formule gebruiken om de functies SUBTOTAAL en SUMPRODUCT in Excel te combineren:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

Met deze specifieke formule kunt u het product van de waarden van het bereik C2:C11 en het bereik D2:D11 optellen, zelfs nadat dat cellenbereik op de een of andere manier is gefilterd.

Het volgende voorbeeld laat zien hoe u deze formule in de praktijk kunt gebruiken.

Voorbeeld: SUBTOTAAL gebruiken met SUMPRODUCT in Excel

Stel dat we de volgende dataset hebben met informatie over de verkoop van verschillende producten bij twee verschillende supermarkten:

Laten we vervolgens de gegevens filteren, zodat alleen de rijen worden weergegeven waarvan de waarde in de kolom Store B is.

Om dit te doen, markeert u het celbereik A1:D11 . Klik vervolgens op het tabblad Gegevens langs het bovenste lint en klik op de knop Filter .

Klik vervolgens op de vervolgkeuzepijl naast Opslaan en zorg ervoor dat alleen het vakje naast B is aangevinkt. Klik vervolgens op OK :

De gegevens worden automatisch gefilterd om alleen rijen weer te geven waarvan de kolom Store gelijk is aan B :

Als we de functie SUMRPODUCT() proberen te gebruiken om het product van de kolomwaarden Sales en Price op te tellen, wordt in feite de som van het product van deze twee kolommen in de oorspronkelijke gegevensset geretourneerd:

In plaats daarvan moeten we de volgende formule gebruiken:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

De volgende schermafbeelding laat zien hoe u deze formule in de praktijk kunt gebruiken:

Deze formule retourneert de juiste som van 77,5 .

We kunnen bevestigen dat dit correct is door handmatig de som van het product van de waarden tussen de kolommen Verkoop en Prijs te berekenen:

Som van het product van de waarden tussen verkoop en prijs: (3*2) + (12*2,5) + (5*3,5) + (8*3) = 77,5 .

Aanvullende bronnen

In de volgende zelfstudies wordt uitgelegd hoe u andere veelvoorkomende bewerkingen in Excel uitvoert:

Hoe gefilterde rijen in Excel te verwijderen
Hoe gefilterde rijen in Excel te tellen
Hoe gefilterde rijen in Excel op te tellen