Generieke formule
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Samenvatting
Om zichtbare rijen alleen met criteria te tellen, kunt u een vrij complexe formule gebruiken op basis van SOMPRODUCT, SUBTOTAAL en VERSCHUIVING. In het getoonde voorbeeld is de formule in C12:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Voorwoord
De SUBTOTAAL-functie kan eenvoudig sommen en tellingen genereren voor verborgen en niet-verborgen rijen. Het is echter niet in staat om zonder enige hulp criteria als AANTAL.ALS of SOM.ALS te verwerken. Een oplossing is om SOMPRODUCT te gebruiken om zowel de SUBTOTAAL-functie (via OFFSET) als de criteria toe te passen. De details van deze benadering worden hieronder beschreven.
Uitleg
In de kern werkt deze formule door twee arrays in SOMPRODUCT op te zetten. De eerste array past criteria toe, en de tweede array zorgt voor zichtbaarheid:
=SUMPRODUCT(criteria*visibility)
Het criterium wordt toegepast bij een deel van de formule:
=(C5:C8=C10)
Wat een array als deze genereert:
(FALSE;TRUE;FALSE;TRUE)
Waar waar betekent "voldoet aan criteria". Merk op dat omdat we vermenigvuldiging (*) gebruiken op deze array, de TRUE FALSE-waarden automatisch worden geconverteerd naar enen en nullen door de wiskundige bewerking, dus we eindigen met:
(0;1;0;1)
Het zichtbaarheidsfilter wordt toegepast met SUBTOTAAL, met functienummer 103.
SUBTOTAAL is in staat om verborgen rijen uit te sluiten tijdens het uitvoeren van berekeningen, dus we kunnen het in dit geval gebruiken om een "filter" te genereren om verborgen rijen binnen SUMPRODUCT uit te sluiten. Het probleem is echter dat SUBTOTAL een enkel getal retourneert, terwijl we een reeks resultaten nodig hebben om het met succes te gebruiken in SOMPRODUCT. De truc is om OFFSET te gebruiken om SUBTOTAL één referentie per rij te geven, zodat OFFSET één resultaat per rij retourneert.
Dat vereist natuurlijk nog een truc, namelijk om OFFSET een array te geven die één getal per rij bevat, beginnend met nul. We doen dat met een uitdrukking die is gebouwd op de ROW-functie:
=ROW(C5:C8)-MIN(ROW(C5:C8)
die een array als deze zal genereren:
(0;1;2;3)
Samengevat wordt de tweede array (die de zichtbaarheid afhandelt met SUBTOTAAL) als volgt gegenereerd:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
En tot slot hebben we:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Wat 1 teruggeeft.
Meerdere criteria
U kunt de formule als volgt uitbreiden om meerdere criteria te verwerken:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Resultaten samenvatten
Om een som van waarden te retourneren in plaats van een telling, kunt u de formule aanpassen om een sombereik op te nemen:
=SUMPRODUCT(criteria*visibility*sumrange)
De criteria en zichtbaarheidsmatrices werken hetzelfde als hierboven uitgelegd, met uitzondering van cellen die niet zichtbaar zijn. Als je een gedeeltelijke overeenkomst nodig hebt, kun je een uitdrukking construeren met ISNUMBER + SEARCH, zoals hier wordt uitgelegd.
