Alleen lege cellen optellen in Excel

In Excel komt het geregeld voor dat je alleen bepaalde bedragen wilt optellen. Bijvoorbeeld alleen bedragen van facturen die nog niet zijn betaald. Voor dit soort situaties is de functie SOM.ALS ideaal.

De functie SOM.ALS telt namelijk waarden op op basis van een voorwaarde. Dit wordt in Excel een criterium genoemd.

De schrijfwijze van deze functie is als volgt:

=SOM.ALS(bereik;criterium;optelbereik)

Voor het werken met lege of gevulde cellen zijn twee operatoren belangrijk:

• "" betekent leeg
• "<>” betekent bevat een waarde

Met deze twee operatoren kun je Excel dus eenvoudig onderscheid laten maken tussen lege cellen, en cellen waarin juist wel iets staat.

Voorbeeld 1: openstaande facturen optellen

Stel je hebt een overzicht met facturen:

• In kolom A staat het factuurbedrag
• In kolom B staat de betaaldatum

Een lege cel in kolom B betekent dat de factuur nog niet betaald is.

De formule in dit voorbeeld is =SOM.ALS(B2:B6;"";A2:A6)

Dit werkt als volgt:

• Excel controleert het bereik B2:B6
• en alleen als de cel leeg is ( "" )
• wordt het bijbehorende bedrag uit het bereik A2:A6 opgeteld.

Het resultaat is het totaalbedrag aan openstaande facturen.

Het omgekeerde is ook mogelijk. Als je juist wilt weten welk bedrag al is betaald, dan gebruik je de operator "<>", zodat Excel alle cellen in de som meeneemt die niet leeg zijn.

Er zijn een aantal manieren waarop je deze formule dynamischer kunt maken.

De makkelijkste manier is om het bereik om te zetten naar een tabel. Wanneer je dan op een later moment een nieuwe factuur toevoegt, wordt de formule automatisch bijgewerkt.

Wil je geen tabel maken, dan kun je er ook voor kiezen om bij bereik de gehele kolom B in te voeren en bij het optelbereik de gehele kolom A.

De formule =SOM.ALS(B:B;"";A:A) werkt als volgt:

• Excel kijkt nu naar de gehele kolom B
• alleen als de cel leeg is
• wordt het bijbehorende bedrag uit kolom A opgeteld.

Wanneer je gehele kolommen gebruikt, is het wel belangrijk dat je deze kolommen alleen voor dit overzicht gebruikt.

Dit werkt overigens ook met bijvoorbeeld kruisjes, teksten, etc. Het gaat er steeds alleen om of cellen leeg zijn of niet.

Voorbeeld 2: aantal facturen tellen

Tot nu toe hebben we bedragen opgeteld. Wil je niet het totaalbedrag, maar het aantal facturen weten, dan gebruik je de functie AANTAL.ALS. Met deze functie tel je namelijk hoeveel cellen er in een bereik aan de voorwaarde voldoen.

Het principe blijft hetzelfde, behalve dat je nu geen optelbereik nodig hebt.

• Aantal openstaande facturen: =AANTAL.ALS(B2:B6;"")
• Aantal betaalde facturen: =AANTAL.ALS(B2:B6;"<>")

Voorbeeld 3: combineren met extra voorwaarden

We kunnen het voorbeeld ook gaan uitbreiden met meerdere voorwaarden. In dat soort situaties kun je de functie SOMMEN.ALS gebruiken.

Stel het voorbeeld ziet er nu als volgt uit:

• In kolom A staat het bedrag
• In kolom B staat de betaaldatum
• In kolom C staat de klantnaam

We willen nu alleen openstaande facturen van één specifieke klant optellen, bijvoorbeeld Johan.

De formule wordt dan =SOMMEN.ALS(A2:A6;B2:B6;"";C2:C6;"Johan") of =SOMMEN.ALS(A:A;B:B;"";C:C;"Johan")

Excel telt nu alleen:

• De bedragen uit kolom A
• waar kolom B leeg is
• en kolom C gelijk is aan Johan.

In dit geval is het bedrag €500.

Op deze manier kun je snel en eenvoudig een factuuroverzicht maken van openstaande bedragen per klant.

En hiervoor geldt ook dat als je wilt tellen hoeveel facturen een bepaalde klant nog open heeft staan, dat je de AANTALLEN.ALS functie kan gebruiken. Dit werkt net zoals met het eerdere voorbeeld met de functie AANTAL.ALS, maar dan met meerdere voorwaarden.

De formule =AANTALLEN.ALS(B:B;"";C:C;"Johan") telt hoeveel rijen er zijn waarbij:

• Kolom B leeg is
• en kolom C gelijk is aan Johan

In dit voorbeeld is dat er 1.