Niet-lege cellen tellen in Excel: 5 hoofdmethoden

Ik kan ook over een rij tellen om te zien hoe compleet ieders antwoord is:

=COUNTA(B2:F2)Mogelijk gemaakt door

Voor Sara Ibrahim geeft dit 5 terug omdat ze elk veld invulde. Voor Nour Mahmoud geeft =COUNTA(B6:F6) slechts 1 terug, omdat zij alleen haar e-mail heeft ingevuld en de rest heeft overgeslagen.

Wanneer COUNTA() genoeg is

Gebruik COUNTA() wanneer je een simpele telling nodig hebt en je data schoon is. Als elke lege cel in je bereik echt leeg is (nooit aangeraakt, geen formules), COUNTA() werkt perfect.

Het wordt pas rommelig als formules "" (lege tekenreeksen) retourneren. Ik leg dat randgeval later uitgebreid uit in de sectie "Hoe Excel lege vs. empty cellen behandelt".

Niet-lege cellen tellen met COUNTIF()

COUNTIF() biedt je een andere manier om niet-lege cellen te tellen. In plaats van alles te tellen wat bestaat, telt het cellen die aan een voorwaarde voldoen. En die voorwaarde kan "niet leeg" zijn.

Het "<>"-criterium

Zo ziet de formule eruit:

=COUNTIF(B2:B13,"<>")Mogelijk gemaakt door

Het gedeelte "<>" betekent "niet gelijk aan niets", wat gewoon een andere manier is om "niet leeg" te zeggen. Voor dezelfde e-mailkolom als hiervoor krijg ik 10, net als bij COUNTA().

Waarom niet COUNTIF(range,"*") gebruiken?

Ik zie dit in oudere tutorials, en het is een fout. De wildcard "*" telt alleen tekst. Het negeert volledig getallen, datums en WAAR/ONWAAR-waarden. Als je kolom gemengde datatypen heeft, krijg je een verkeerde telling.

Blijf in plaats daarvan bij "<>". Dat werkt ongeacht het type data.

Wanneer COUNTIF() beter is dan COUNTA()

Het echte voordeel van COUNTIF() is dat het natuurlijk doorloopt in COUNTIFS(). Zodra je niet-lege cellen wilt tellen die óók aan andere voorwaarden voldoen (zoals een specifieke afdeling of datumbereik), staat COUNTIFS() voor je klaar. Als je denkt later voorwaarden toe te voegen, begin dan met COUNTIF() zodat de overstap makkelijk is.

Voor de meeste data geven COUNTA() en COUNTIF(range,"<>") identieke resultaten. Beide tellen dezelfde dingen, inclusief fouten en door formules gegenereerde lege waarden. Het belangrijkste verschil is flexibiliteit. COUNTIF() is je brug naar tellen met meerdere criteria met COUNTIFS().

Niet-lege cellen tellen die aan voorwaarden voldoen

Hier begint COUNTIF() echt te schitteren. COUNTIFS() laat je niet-lege cellen tellen die ook aan andere voorwaarden voldoen.

COUNTIFS() met niet-leeg-logic

Stel, ik houd medewerkerstraining bij en wil weten hoeveel mensen van Sales een score hebben ingeleverd. Dit is de formule:

=COUNTIFS(B2:B11,"Sales",E2:E11,"<>")Mogelijk gemaakt door

Dit betekent: "Tel rijen waar Afdeling 'Sales' is EN Score niet leeg is." In mijn tracker krijg ik 3 omdat Mona Sayed wel bij Sales zit, maar nog geen score heeft ingeleverd.

Meerdere voorwaarden

Je kunt tot 127 criteriaparen stapelen als dat nodig is. Hier is er één die werknemers telt met status "Complete" die ook een score hebben:

=COUNTIFS(C2:C11,"Complete",E2:E11,"<>")Mogelijk gemaakt door

Dit geeft 6 terug omdat Mona Sayed haar training heeft afgerond maar nooit een score heeft ingeleverd.

Nog een voorbeeld. Engineers tellen die een voltooiingsdatum hebben:

=COUNTIFS(B2:B11,"Engineering",D2:D11,"<>")Mogelijk gemaakt door

Dit geeft 3 terug, omdat de training van Omar Mostafa nog in behandeling is en nog geen datum heeft.

Het patroon is simpel: gebruik "<>" voor de kolom die je op niet-lege waarden controleert, en zet je specifieke filters in de andere kolommen.

Hoe Excel "leeg" vs. "empty" cellen behandelt

Hier gaan de meeste tel-fouten mis. Excel ziet verschillende soorten "leeg" die er op het scherm hetzelfde uitzien.

Echt lege cellen

Een echt lege cel is nooit aangeraakt of volledig gewist. ISBLANK() retourneert TRUE, COUNTA() slaat hem over en COUNTBLANK() telt hem mee. Dit is het enige type cel dat alle functies als leeg beschouwen.

Door formules gegenereerde lege tekenreeksen

Dit trapt mensen telkens weer. Wanneer een formule zoals =IF(A1>60,"Pass","") "" retourneert, lijkt de cel leeg maar bevat die eigenlijk een formulieresultaat. Zo zien verschillende functies het:

• COUNTA() → Telt hem (niet leeg)
• COUNTIF(range,"<>") → Telt hem (niet leeg)
• COUNTBLANK() → Telt hem (leeg)
• ISBLANK() → Retourneert FALSE

Zie je het probleem? COUNTBLANK() zegt dat het leeg is, maar ISBLANK() zegt van niet. Dit zet zelfs doorgewinterde Excel-gebruikers op het verkeerde been.

De SUMPRODUCT() + LEN()-workaround

Om alleen cellen met daadwerkelijk zichtbare inhoud te tellen (geen formule-blanks, geen spaties), gebruik je dit:

=SUMPRODUCT(--(LEN(B5:B10)>0))Mogelijk gemaakt door

Dit controleert hoeveel tekens elke cel heeft. Als LEN() 0 retourneert, is de cel hoe dan ook leeg. Het -- deel zet WAAR/ONWAAR om naar 1/0, en SUMPRODUCT()` telt ze bij elkaar op.

Voor een nóg strengere telling die ook cellen met spaties negeert:

=SUMPRODUCT(--(LEN(TRIM(B5:B10))>0))Mogelijk gemaakt door

Cellen met verborgen tekens

Soms lijkt een cel leeg maar staat er een spatie in (CHAR(32)) of een vaste spatie (CHAR(160)) vanuit webdata of pdf's. COUNTA() telt hem, LEN() retourneert 1 of meer, en ISBLANK() zegt FALSE.

Om deze in je telling te vangen, gebruik je de strengere formule die ik eerder liet zien: =SUMPRODUCT(--(LEN(TRIM(range))>0)). Het TRIM() gedeelte verwijdert gewone spaties vóór het tellen. Voor vaste spaties, maak je data eerst schoon met =SUBSTITUTE(A1,CHAR(160),"") of gebruik de CLEAN() -functie.

Niet-lege cellen tellen in gefilterde data

Hier trapt menig gebruiker in: je filtert je data, schrijft een COUNTA()-formule en het resultaat bevat nog steeds de verborgen rijen. Dat komt omdat COUNTA() geen rekening houdt met filters. Het telt alles in het bereik, of je het nu ziet of niet.

SUBTOTAL(): het filterbewuste alternatief

SUBTOTAL() lost dit op. Het telt met respect voor filters.

=SUBTOTAL(3,D2:D11)Mogelijk gemaakt door

De 3 vertelt SUBTOTAL() om een COUNTA()-achtige telling te doen. Het verschil: het slaat rijen over die door filters verborgen zijn.

Functienummers: 3 vs. 103

SUBTOTAL() heeft twee modi:

• Functienummer 3: Slaat rijen over die door AutoFilter zijn verborgen, maar telt handmatig verborgen rijen wel mee.
• Functienummer 103: Slaat ALLE verborgen rijen over (zowel filter als handmatig).

=SUBTOTAL(3,D2:D11)    -- Skips AutoFilter rows
=SUBTOTAL(103,D2:D11)  -- Skips ALL hidden rowsMogelijk gemaakt door

Meestal is 3 wat je nodig hebt. Gebruik 103 alleen als je rijen handmatig hebt verborgen (rechtsklik > Verbergen) en die ook wilt uitsluiten.

AGGREGATE(): een alternatief voor rommelige data

Als je gefilterde data foutwaarden bevat, zal SUBTOTAL() een fout geven. AGGREGATE() gaat hier beter mee om:

=AGGREGATE(3,7,D2:D11)Mogelijk gemaakt door

De 3 is het functienummer (COUNTA()), en 7 zegt dat verborgen rijen en fouten moeten worden genegeerd. Dit is de veiligste optie voor rommelige data, maar de meesten hebben het niet nodig.

Niet-lege cellen tellen in Excel-tabellen

Als je data in een Excel-tabel staat (klik Invoegen > Tabel of druk op Ctrl+T), kun je gestructureerde verwijzingen gebruiken in plaats van celadressen. Dat maakt je formules duidelijker en ze updaten automatisch wanneer de tabel groeit.

Gestructureerde verwijzingen met COUNTA()

In plaats van =COUNTA(C2:C9) te schrijven, schrijf je:

=COUNTA(TaskTracker[Assigned To])Mogelijk gemaakt door

Dit telt niet-lege cellen in de kolom Assigned To van mijn TaskTracker-tabel. De verwijzing slaat de koprij automatisch over en breidt uit wanneer ik nieuwe rijen toevoeg.

COUNTIFS() combineren met gestructureerde verwijzingen

COUNTIFS() werkt uitstekend met tabellen:

=COUNTIFS(TaskTracker[Status],"Done",TaskTracker[Completion Date],"<>")Mogelijk gemaakt door

Dit telt taken met status "Done" die een voltooiingsdatum hebben. In mijn tracker krijg ik 4 omdat alle afgeronde taken datums hebben.

Waarom tabellen beter zijn om te tellen

Twee dingen maken tabellen handig. Ten eerste slaan gestructureerde verwijzingen de koprij automatisch over, dus je hoeft je geen zorgen te maken over starten vanaf rij 2. Ten tweede: als je een nieuwe rij aan de tabel toevoegt, nemen alle formules met gestructureerde verwijzingen de nieuwe data automatisch mee. Je hoeft bereiken niet handmatig bij te werken.

Dit is perfect voor data die in de tijd groeit, zoals logs, trackers of data die je regelmatig importeert.

Veelvoorkomende problemen bij het tellen van niet-lege cellen

Telling is hoger dan verwacht

Wat er gebeurt: COUNTA() zegt 6, maar je ziet slechts 4 cellen met data.

Waarom: Twee cellen hebben formules die "" retourneren. Zoals eerder genoemd: ze lijken leeg, maar zijn dat technisch niet.

Oplossing: Gebruik =SUMPRODUCT(--(LEN(range)>0)) in plaats van COUNTA(). Dit telt alleen cellen met zichtbare inhoud en negeert door formules gegenereerde lege waarden.

Verborgen spaties verpesten de telling

Wat er gebeurt: Een cel lijkt leeg, maar COUNTA() telt hem en ISBLANK() zegt FALSE.

Waarom: Er staan spaties in de cel, waarschijnlijk afkomstig van het kopiëren van data van een website of uit een ander bestand.

Oplossing: Controleer met =LEN(A1). Als dat meer dan 0 retourneert terwijl de cel leeg lijkt, heb je verborgen tekens. Maak je data schoon met =TRIM(A1), of gebruik de strengere formule die ik eerder liet zien: =SUMPRODUCT(--(LEN(TRIM(range))>0)).

Foutwaarden worden meegeteld terwijl je dat niet wilt

Wat er gebeurt: Je wilt niet-lege cellen tellen, maar fouten zoals #DIV/0! of #N/A worden meegenomen.

Waarom: Zowel COUNTA() als COUNTIF(range,"<>") beschouwen fouten als niet-leeg.

Oplossing: Als je data ook filters heeft, gebruik =AGGREGATE(3,7,range) zoals ik in de sectie over gefilterde data noemde. De 7 slaat zowel verborgen rijen als fouten over. Voor niet-gefilterde data gebruik je =SUMPRODUCT(--(LEN(IFERROR(range,""))>0)).

Telling wordt niet bijgewerkt na filteren

Wat er gebeurt: Je filtert je data en COUNTA() laat nog steeds de on-gefilterde telling zien.

Waarom: COUNTA() negeert filters volledig.

Oplossing: Gebruik =SUBTOTAL(3,range) zoals ik in de sectie over gefilterde data behandelde. Dat past zich automatisch aan op wat zichtbaar is.

Best practices voor het tellen van niet-lege cellen

Na deze issues vaker te hebben gezien dan ik wil toegeven, doe ik nu het volgende:

• Begin met COUNTA() voor schone, simpele datasets. Het is snel en eenvoudig.

• Gebruik COUNTIF(range,"<>") als je later voorwaarden wilt kunnen toevoegen. Het sluit naadloos aan op COUNTIFS() voor tellen met meerdere criteria.

• Test op formule-blanks voordat je de telling vertrouwt. Controleer een paar "lege" cellen met =LEN(cell). Als je iets anders dan 0 krijgt, schakel dan over op de SUMPRODUCT()+LEN()-aanpak.

• Gebruik SUBTOTAL(3,range) voor gefilterde data. Het is de enige tel-functie die filters respecteert.

• Maak geïmporteerde data eerst schoon. Gebruik TRIM() en controleer op CHAR(160) bij data van websites of andere bronnen.

• Gebruik Excel-tabellen voor groeiende datasets. Gestructureerde verwijzingen updaten automatisch wanneer je rijen toevoegt.

• Documenteer complexe formules. Als je SUMPRODUCT(--(LEN(TRIM(range))>0)) gebruikt, voeg dan een opmerking toe met uitleg waarom. Je toekomstige zelf zal je dankbaar zijn.

Conclusie

Excel biedt meerdere manieren om niet-lege cellen te tellen, en de juiste hangt af van je situatie. COUNTA() werkt in de meeste gevallen. COUNTIF() en COUNTIFS() laten je voorwaarden toevoegen. SUBTOTAL() respecteert filters. En SUMPRODUCT() met LEN() is je back-up wanneer formules onzichtbare lege waarden creëren.

Het gaat niet om formules uit je hoofd leren. Het gaat erom te begrijpen wat Excel met "leeg" bedoelt. Een formule die "" retourneert, lijkt leeg maar is het niet. Een spatie is onzichtbaar maar wordt meegeteld. Als je deze verschillen eenmaal snapt, zul je niet meer verrast worden door verkeerde tellingen.

Wil je je Excel-skills verder uitbouwen? Bekijk dan onze cursus Data Analysis in Excel . Die behandelt formules, draaitabellen en datacleaning die goed aansluiten op alles wat we hier hebben besproken.