Gratis tips Excel - Aantal na het toepassen van een filter, met een criteriumcel


Tips Excel		go to : Index

Tip 46
Deze Tip werd geschreven door mezelf.

Aantal na het toepassen van een filter, met een criteriumcel
Op vraag van Tamar plus nog een aantal anderen, heb ik de tip waarbij je het aantal kunt weergeven na het filteren van gegevens, uitgewerkt met een criterium die je ingeeft in een cel.
Klik hier om het bestand te downloaden.

Om eerlijk te zijn, ik wist het ook niet. Dus heb ik me rot gezocht op het Internet en, hoera hoera, een oplossing gevonden. Wie zoekt die vindt, nietwaar.

De formule die we hiervoor gebruiken is:
=SOMPRODUCT(SUBTOTAAL(3;VERSCHUIVING(A2:A10;RIJ(A2:A10)-RIJ(A2);0;1));--(A2:A10=B13))
en werd samengesteld door de heer Laurent Longre.
Deze persoon is me onbekent maar, ere wie ere toekomt.


Laat ik eens proberen uit te leggen wat deze persoon met deze formule, tot stand heeft gebracht. Moeilijk uit te leggen, maar laat ik toch maar een poging wagen.

Hij begint met de functie "SOMPRODUCT".
Deze functie vermenigvuldigt dezelfde elementen in de opgegeven matrices en telt de producten vervolgens op. Deze bestaat uit minimaal 2 en maximaal 30 matrices waarvan je de elementen wil vermenigvuldigen en vervolgens wil optellen.
In dit geval bestaat deze functie uit twee matrices. De eerste berekent het subtotaal van het aantal. Het aantal is vastgelegd door het functienummer 3. De tweede converteert, door middel van de twee min-tekens, de gevonden waarden naar een 1 of een 0. De resultaten van beide worden opgesomd door de functie "SOMPRODUCT".

In de functie SUBTOTAAL, welke voor ons het aantal berekent, vinden we de functie "VERSCHUIVING".
Deze functie geeft als resultaat een verwijzing naar een bereik, met een opgegeven aantal rijen en kolommen van een cel of cellenbereik. Je hebt de mogelijkheid hoeveel rijen en kolommen er als resultaat moeten worden gegeven, in te stellen.

Dit is echt allemaal moeilijk uit te leggen, maar wanneer je de functies van SOMPRODUCT, SUBTOTAAL en VERSCHUIVING een beetje kent, wordt het wel logisch. Heb je hiervan helemaal geen kennis, niet getreurd de formule werkt en dat is het belangrijkste. Wijzig je de gegevens in cel B13, dan zal ook het aantal in cel C13 worden aangepast. Het maakt niet uit of er is gefilterd of niet, altijd wordt het correcte aantal getoond.



Ter informatie nog een lijstje met de verschillende functie-getallen voor de SUBTOTAAL-functie :



Index