Subtotaal gebruiken met sumif in excel

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

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

Met deze specifieke formule kunt u de waarden in het bereik C2:C11 optellen, waarbij de overeenkomstige waarde in het bereik B2:B11 gelijk is aan „Behouden“, zelfs nadat dat celbereik 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 SUMIF in Excel

Stel dat we de volgende dataset hebben met informatie over verschillende basketbalspelers:

Laten we vervolgens de gegevens filteren om alleen de rijen weer te geven waar Western Conference-spelers zich bevinden.

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

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

De gegevens worden automatisch gefilterd om alleen rijen weer te geven waarbij de kolom Conferentie gelijk is aan West :

Als we de functie SUMIF() proberen te gebruiken om de waarden in de kolom Punten op te tellen, waarbij de waarde van de kolom Positie gelijk is aan „Bewaker“, retourneert deze feitelijk de som van de punten voor rijen die gelijk zijn aan „Bewaker“ in de SUMIF() functie. originele dataset:

In plaats daarvan moeten we de volgende formule gebruiken:

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

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

Deze formule retourneert de juiste som van 85 .

We kunnen bevestigen dat dit correct is door handmatig de som van de puntwaarden te berekenen voor rijen waarbij de kolom Positie gelijk is aan „Bewaker“:

Som van bewakingspunten: 12 + 28 + 30 + 15 = 85 .

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 toe te voegen in Excel