Hoe gebruik je subtotaal met if in excel?

DEZE FORMULE IS PURE MAGISCH.

Als u in Excel een SUBTOTAAL-formule met IF (voorwaarde) wilt schrijven, moet u daarvoor verschillende functies gebruiken. Maar voordat we dat doen, moeten we eerst de gegevens begrijpen die we voor dit voorbeeld hebben.

In het bovenstaande voorbeeld heb je drie kolommen:

• Naam
• Leeftijd plaat
• Geslacht

En wanneer u een plaat uit de kolom Leeftijdsplaat filtert, wordt het aantal vrouwen in cel F1 weergegeven. Dit betekent dus dat we een formule hebben die het aantal gefilterde waarden weergeeft, maar met een voorwaarde.

De formule die we hebben:

 =SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))

De SUBTOTAAL ALS-formule begrijpen

Deze formule gebruikt vijf functies: SOMPRODUCT, SUBTOTAAL, OFFSET, RIJ en MAX. Om deze formule te begrijpen, moeten we deze daarom in verschillende delen verdelen.

1.LIJN(C2:C41)-2.0)

Dit deel van de formule gebruikt de functies MIN en ROW.

• In de RIJ hebben we verwezen naar de kolommen ‘Geslacht’ en wordt een array met rijnummers geretourneerd.
• Daarna neemt MIN die array- of rijnummers en retourneert het minimale rijnummer. Daarom hebben we er 2 in dit deel van de formule.

2.LIJN(C2:C41)

In dit deel hebben we alleen de functie RIJ, die een array met rijnummers retourneert.

3. OFFSET(C2,LIJN(C2:C41)-MIN(LIJN(C2:C41)),0)

We hebben nu de OFFSET-functie. Hiermee kunt u een verwijzing naar een bereik maken met een celverwijzing als uitgangspunt. In het referentieargument verwezen we naar cel C2, de eerste cel van waaruit ons geslachtsbereik begint.

In het lijnenargument hebben we dit deel van de formule dat hierboven in de eerste twee delen is besproken. Daarna gebruikten we in het cols-argument 0. Met dit alles retourneert OFFSET een array van alle waarden in de kolom ‘Geslacht’.

4. SUBTOTAAL(3,OFFSET(C2,LIJN(C2:C41)-MIN(LIJN(C2:C41)),0))

We gebruikten de array die werd geretourneerd door de OFFSET in het SUBTOTAAL. En in de functie_num hebben we 3 gebruikt, wat SUBTOTAAL vertelt om de functie COUNTA te gebruiken voor berekeningen.

Wanneer u een filter gebruikt in de kolom ‘Leeftijdsplaat’, retourneert dit SUBTOTAAL-gedeelte van de formule een array met 0 en 1.

In deze tabel hebben we 1 voor waarden die gelijkwaardig zijn aan de waarde waarvoor we het filter hebben toegepast. Zie het onderstaande voorbeeld:

5. (C2:C41=E1)

Dit deel van de formule retourneert een array door een voorwaarde te testen. In deze situatie testen we of de bereikwaarde ‘Vrouwelijk’ is en retourneert TRUE en FALSE in de array.

In deze tabel hebben we TRUE voor de “Vrouwelijke” waarde en FALSE voor de andere.

7. Laatste deel

Uiteindelijk hebben we twee tabellen in SUMPRODUCT. En we hebben ook een asterisk-operator tussen deze arrays.

Wanneer we de twee tabellen met elkaar vermenigvuldigen, krijgen we één tabel met 0 en 1. In deze tabel is één (1) voor de waarde ‘Vrouw’ in geslacht en 21-30 voor ‘Leeftijdsplaat’.

Uiteindelijk retourneert SUMPRODUCT de som met behulp van deze array. En deze som is het aantal cellen met de waarde ‘Vrouw’ in de kolom ‘Geslacht’ wanneer u filtert op 21-30 in de kolom ‘Leeftijdsbereik’.