SUBTOTAAL ALS Formule – Excel & Google Spreadsheets

Voorbeeldwerkboek downloaden

Download het voorbeeldwerkboek

Deze tutorial laat zien hoe je "subtotaal als" kunt berekenen, waarbij alleen zichtbare rijen met criteria worden geteld.

SUBTOTAAL Functie

De functie SUBTOTAAL kan verschillende berekeningen uitvoeren op een reeks gegevens (telling, som, gemiddelde, enz.). Het belangrijkste is dat het kan worden gebruikt om alleen op zichtbare (gefilterde) rijen te rekenen. In dit voorbeeld gebruiken we de functie om zichtbare rijen te tellen (AANTALLEN) door het argument SUBTOTAL function_num in te stellen op 3 (Een volledige lijst met mogelijke functies vindt u hier.)

=SUBTOTAAL(3,$D$2:$D$14)

Merk op hoe de resultaten veranderen als we rijen handmatig filteren.

SUBTOTAAL IF

Om een ​​"Subtotaal Als" te maken, gebruiken we een combinatie van SOMPRODUCT, SUBTOTAAL, OFFSET, RIJ en MIN in een matrixformule. Met behulp van deze combinatie kunnen we in wezen een generieke functie "SUBTOTAAL ALS" maken. Laten we een voorbeeld doornemen.

We hebben een lijst met leden en hun aanwezigheidsstatus voor elk evenement:

Stel dat we worden gevraagd om het aantal leden dat een evenement dynamisch heeft bijgewoond te tellen, aangezien we de lijst handmatig als volgt filteren:

Om dit te bereiken, kunnen we deze formule gebruiken:

=SOMPRODUCT((=)*(SUBTOTAAL(3,OFFSET(,RIJ()-MIN(RIJ()),0))))
=SOMPRODUCT((D2:D14="Aanwezig")*(SUBTOTAAL(3,OFFSET(D2,RIJ(D2:D14)-MIN(RIJ(D2:D14)),0))))

Wanneer u Excel 2022 en eerder gebruikt, moet u de matrixformule invoeren door op te drukken CTRL + SHIFT + ENTER om Excel te vertellen dat u een matrixformule invoert. U weet dat de formule correct is ingevoerd als een matrixformule wanneer accolades rond de formule verschijnen (zie afbeelding hierboven).

Hoe werkt de formule?

De formule werkt door twee arrays binnen SOMPRODUCT te vermenigvuldigen, waarbij de eerste array onze criteria behandelt en de tweede arrayfilters alleen voor zichtbare rijen:

=SOMPRODUCT(*)

De criteriareeks

De matrix met criteria evalueert elke rij in ons waardenbereik (status bijgewoond in dit voorbeeld) en genereert een matrix zoals deze:

=(=)
=(D2:D14="Bijgewoond")

Uitgang:

{WAAR; ONWAAR; ONWAAR; WAAR; ONWAAR; TUUR; TUUR; TUUR; ONWAAR; ONWAAR; WAAR; ONWAAR; WAAR}

Merk op dat de uitvoer in de eerste array in onze formule negeert of de rij zichtbaar is of niet, en dat is waar onze tweede array van pas komt om te helpen.

De zichtbaarheidsreeks

Door SUBTOTAL te gebruiken om niet-zichtbare rijen in ons assortiment uit te sluiten, kunnen we onze zichtbaarheidsarray genereren. SUBTOTAL alleen zal echter een enkele waarde retourneren, terwijl SUMPRODUCT een reeks waarden verwacht. Om dit te omzeilen, gebruiken we OFFSET om één rij tegelijk te passeren. Deze techniek vereist het voeden van OFFSET een array die één nummer tegelijk bevat. De tweede array ziet er als volgt uit:

=SUBTOTAAL(3,OFFSET(,RIJ()-MIN(RIJ()),0))
=SUBTOTAAL(3,OFFSET(D2,RIJ(D2:D14)-MIN(RIJ(D2:D14)),0))

Uitgang:

{1;1;0;0;1;1}

De twee aan elkaar naaien:

=SOMPRODUCT({WAAR; WAAR; ONWAAR; ONWAAR; WAAR; WAAR} * {1; 1; 0; 0; 1; 1})
= 4

SUBTOTAAL ALS met meerdere criteria

Om meerdere criteria toe te voegen, gewoon meerdere criteria samen binnen het SOMPRODUCT, zoals zo:

=SOMPRODUCT((=)*(=)*(SUBTOTAAL(3,OFFSET(,RIJ()-MIN(RIJ()),0)))))
=SOMPRODUCT((E2:E14="Aanwezig")*(B2:B14=2019)*(SUBTOTAAL(3,OFFSET(E2,RIJ(E2:E14)-MIN(RIJ(E2:E14)),0)) ))

SUBTOTAAL IF in Google Spreadsheets

De SUBTOTAAL ALS-functie werkt precies hetzelfde in Google Spreadsheets als in Excel:

wave wave wave wave wave