Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de SUMIFS-functie kunt gebruiken om gegevens op te tellen die overeenkomen met lege of lege cellen in Excel en Google Spreadsheets.
Som indien leeg
Eerst laten we zien hoe u rijen met lege cellen optelt.
De SOMMEN-functie somt gegevens op die aan bepaalde criteria voldoen.
We kunnen de SOMMEN-functie gebruiken om alles op te tellen Scores voor Spelers zonder namen in het onderstaande voorbeeld.
| 1 | =SOMMEN.(C3:C8,B3:B8,"") |
We gebruiken dubbele aanhalingstekens ("") om een lege cel in Excel weer te geven. Ons voorbeeld negeert Spelers A, B, C en D en telt de scores voor onbekend op Spelers.
Spaties behandelen als lege cellen - met hulpkolom
U moet voorzichtig zijn bij interactie met lege cellen in Excel. Cellen kunnen voor u leeg lijken, maar Excel behandelt ze niet als leeg. Dit kan gebeuren als de cel spaties, regeleinden of andere onzichtbare tekens bevat. Dit is een veelvoorkomend probleem bij het importeren van gegevens in Excel uit andere bronnen.
Als we cellen die alleen spaties bevatten, op dezelfde manier moeten behandelen alsof ze leeg waren, werkt de formule in het vorige voorbeeld niet. Merk op hoe de SUMIFS-formule cel B9 hieronder ("") niet als leeg beschouwt:
Om een cel met alleen spaties te behandelen alsof het een lege cel is, kunnen we een hulpkolom toevoegen met de TRIM-functie om de extra spaties uit de waarde van elke cel te verwijderen:
| 1 | =TRIM(B3) |
We passen de SOMMEN-functie toe op de helperkolom en deze berekent nu de som nauwkeurig.
| 1 | =SOMMEN.(E3:E9,D3:D9,"") |
De helperkolom is gemakkelijk te maken en gemakkelijk te lezen, maar misschien wilt u een enkele formule hebben om de taak te volbrengen. Dit wordt behandeld in de volgende sectie.
Spaties behandelen als lege cellen - zonder helperkolom
Als een hulpkolom niet geschikt is voor uw behoeften, kunt u de SOMPRODUCT-functie gebruiken in combinatie met de LEN- en TRIM-functies om lege rijen op te tellen.
| 1 | =SOMPRODUCT(--(LEN(TRIM(B3:B9))=0),D3:D9) |
In dit voorbeeld gebruiken we de SOMPRODUCT-functie om een ingewikkelde "som als"-berekening uit te voeren. Laten we de formule doornemen.
Dit is onze definitieve formule:
| 1 | =SOMPRODUCT(--(LEN(TRIM(B3:B9))=0),D3:D9) |
Ten eerste geeft de SOMPRODUCT-functie een lijst met waarden uit de twee celbereiken:
| 1 | =SOMPRODUCT(--(LENGTE(TRIM({"A"; "B"; ""; "C"; ""; "XX"; " "}))=0),{25; 10; 15; 5 ; 8; 17; 50) |
Vervolgens verwijdert de TRIM-functie voorloop- en volgspaties uit Speler namen:
| 1 | =SOMPRODUCT(--(LENGTE({"A"; "B"; ""; "C"; ""; "XX"; ""})=0),{25; 10; 15; 5; 8; 17; 50) |
De LEN-functie berekent de lengte van de getrimde Speler namen:
| 1 | =SOMPRODUCT(--({1; 1; 0; 1; 0; 2; 0}=0),{25; 10; 15; 5; 8; 17; 50) |
Met de logische test (=0), eventueel getrimd Speler namen met 0 tekens worden gewijzigd in TRUE:
| 1 | =SOMPRODUCT(--({FALSE; ONWAAR; WAAR; ONWAAR; WAAR; ONWAAR; WAAR}),{25; 10; 15; 5; 8; 17; 50) |
Vervolgens zetten de dubbele streepjes (-) de TRUE en FALSE-waarden om in 1s en 0s:
| 1 | =SOMPRODUCT({0; 0; 1; 0; 1; 0; 1},{25; 10; 15; 5; 8; 17; 50) |
De SOMPRODUCT-functie vermenigvuldigt vervolgens elk paar vermeldingen in de arrays om een array te produceren van Scores alleen voor Speler namen die leeg zijn of alleen uit spaties bestaan:
| 1 | =SOMPRODUCT({0; 0; 15; 0; 8; 0; 50) |
Ten slotte worden de getallen in de array bij elkaar opgeteld:
| 1 | =73 |
Meer details over het gebruik van Booleaanse instructies en het “-” commando in een SUMPRODUCT-functie vindt u hier.
Som indien leeg in Google Spreadsheets
Deze formules werken in Google Spreadsheets precies hetzelfde als in Excel.
