Gefilterde lijst met AANTALLEN.ALS

Beste lezers,

Ik heb een probleem met een Excel-formule en hoop dat hier iemand meeleest die mij zou kunnen helpen.

Het gaat om het volgende: ik heb een Excel-bestand met twee tabbladen (zie bijlage in derde bericht). Op de eerste (Project Leads) staan diverse gegevens verdeeld over zeven kolommen, op de tweede (Overview) staan berekeningen die verwijzen naar de gegevens in het eerste tabblad.

Met behulp van de formule AANTALLEN.ALS lukt het me om te berekenen hoeveel rijen gegevens overeenkomen met twee variabelen die ik heb ingesteld (bijvoorbeeld: hoeveel deelnemers van de cursus Nederlands doen er mee in Amsterdam). Ik heb vooraf ingesteld welke gegevens ik wil weten. Ik heb dus ook een formule voor hoeveel deelnemers Engels er in Rotterdam meedoen, maar omdat die er niet zijn staat hier 0. Tot dusver gaat het goed.

Maar ik wil graag de totalen weten als ik de gegevens in het eerste tabblad filter (bijvoorbeeld om alleen de deelnemers te zien die in 2016 meededen, en niet die uit 2017). Zoals wellicht bekend, laat AANTALLEN.ALS zich niet beïnvloeden door filters. Dankzij dit forum kwam ik een topic tegen wat hierover gaat (http://www.helpmij.nl/forum/showthr...ebruiken-in-combinatie-met-AANTAL-ALS-formule), maar helaas werkt deze formule niet als ik deze aanpas naar mijn bestand.

Op dit moment gebruik ik de volgende formule (die ik invoer met CTRL + SHIFT + ENTER):

=SOM(SUBTOTAAL(3;VERSCHUIVING('Project Leads'!A2:A151;RIJ('Project Leads'!A2:A151)-MIN(RIJ('Project Leads'!A2:A151));;AANTALLEN.ALS('Project Leads'!B2:B151;Overview!C1;'Project Leads'!G2:G151;Overview!A10))))

Dit geeft mij het totaal aantal zichtbare rijen, wat zicht aanpast als ik de gegevens filter. Maar wat me niet lukt, is alleen de zichtbare rijen tellen van de twee ingestelde variabelen (ik zie dus alleen het totaal aantal zichtbare deelnemers).

Ziet iemand misschien wat ik hier fout doe? Alvast hartelijk dank voor de aandacht en reactie!

Je bestand ontbreekt.

Hartelijk dank voor je reactie. Excuseer, bij deze het bestand.

Bekijk bijlage Deelnemerslijst.xlsx

Het gaat om de geel gemarkeerde cel, die bevat de eerdergenoemde formule en filtert op het aantal zichtbare rijen in het eerste tabblad. De overige cellen waar cijfers in staan berekenen alleen het totale aantal rijen, dus die kan ik niet filteren per jaar.

Overigens stond er een fout in mijn beschrijving van de variabelen. Het gaat om het aantal deelnemers per cursus per stad, niet om de namen. Dit heb ik ook hierboven aangepast.

Voordat we beginnen met ingewikkelde formules: is een draaitabel niet een goede optie voor analyse van de gegevens? Daarin heeft microsoft al die ingewikkelde formules al ingebouwd, en je kan filteren naar hartenlust.

Zie bijlage.

Wat AlexCel zegt! :thumb:
En met gebruik van Slicers wordt filteren in een draaitabel helemaal een feestje:

Bedankt voor de reacties, dat ziet er zeker mooi uit! Ik heb eraan gedacht, maar kreeg niet het format wat ik wilde. Die van jullie ziet er al een stuk beter uit.

Het enige nadeel van de draaitabel (die van Gijsbert, want die combineert de jaren) is echter dat als een rij of kolom geen waarden heeft, deze uit de draaitabel verdwijnt. Dat is niet de bedoeling; als een combinatie van variabelen niet voorkomt wil ik graag de waarde nul zien en geen lege cel. Dus het format van de tabel zou altijd gelijk moeten blijven, ongeacht hoe je filtert. Kan dat ook met een draaitabel?

dat is als volgt op te lossen:

rechtsklik eens op de bv Den Haag en kies Veldinstellingen
klik op tab Indeling & afdrukken
zet vinkje voor "Items zonder gegevens weergeven"

Daarna voor bv "Frans" herhalen

Bijlage naar wens?

Zeker! Hartelijk dank voor de zeer snelle reacties en hulp allemaal :thumb:

Voor degenen die dit topic later vinden en met hetzelfde of een vergelijkbaar probleem zitten en dit na willen doen (of als ik er zelf later nog een moet maken): wat moet je in de gaten houden bij het maken van zo'n draaitabel wil je die net zo mooi krijgen als in de bijlage?

Proberen en oefenen en kijk eens op gratiscursus.be naar een Excelcursus.

http://www.gratiscursus.be/Excel_2016/index.html

Bedankt voor de tip, die ga ik doorkijken

Ik heb eigenlijk nog een aanvullende vraag. Ik heb nu een deelnemer aan de lijst toegevoegd en de draaitabel verandert niet mee. Die moet ik dus telkens opnieuw maken? Dat is eigenlijk niet zo praktisch. Heeft iemand daar misschien nog tips voor?

Klik met de rechtermuisknop op de draaitabel, en kies vernieuwen.

Greetz/Excelbat

Super!

Als je wilt kun je dat ook automatiseren.
Als de draaitabel een eigen tabje heeft kun je achter dat tabblad zetten:

Als je daarna het bestand opslaat met macro's wordt de draaitabel elke keer als je het tabblad selecteert vernieuwd

Draaitabel selecteren --> drukken op Alt+F5 is ook nog een optie. :thumb:

Haije zei:

Als je wilt kun je dat ook automatiseren.
Als de draaitabel een eigen tabje heeft kun je achter dat tabblad zetten:

Code:

Private Sub Worksheet_Activate()
    ActiveSheet.PivotTables(1).PivotCache.Refresh
End Sub

Als je daarna het bestand opslaat met macro's wordt de draaitabel elke keer als je het tabblad selecteert vernieuwd

Klik om te vergroten...

Die heb ik toegevoegd, superhandig! :thumb:

Nu nog voorkomen dat telkens de draaitabelvelden worden weergegeven als het bestand wordt geopend. Ik kwam deze macro tegen maar op de een of andere manier werkt 'ie niet:

Sub HideFieldList()
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

Het quoten is niet nodig.

De code hoort thuis in de module van ThisWorkbook

Bedankt voor de suggestie! Ik had in eerste instantie verkeerd gelezen en de code in de verkeerde module gezet. Hartelijk dank voor de hulp allemaal. Ik heb er zelf een hoop van geleerd en hoop dat anderen ook iets aan dit topic hebben