Hoe niet-lege cellen toevoegen?
Om de waarde van niet-lege cellen op te tellen, moet u een niet-lege waarde opgeven (“<>” met de operator groter dan en kleiner dan) in het criteriumargument van de functie. Hiermee zoekt SUMIF alleen naar niet-lege cellen en beschouwt op basis daarvan alleen [sum_range]-waarden. Hieronder hebben wij de som van de donaties met namen berekend.
SUMIF gebruiken voor niet-lege cellen
- Voer eerst in de cel de =SUMIF( in.
- Nu verwijzen de bereikcriteria naar het bereik A2:A13.
- Voer vervolgens de criteria voor niet-lege cellen in met “<>”.
- Daarna verwijst u in het argument [sum_range] naar het bereik B2:B13.
- Voer aan het einde de afsluitende haakjes in en druk op Enter om het resultaat te krijgen.
Wanneer u de formule invoert en op Enter drukt, wordt het donatiebedrag alleen geretourneerd voor de cellen waarin u een naam in de naamkolom heeft.
=SUMIF(A2:A13,"<>",B2:B13)SUMPRODUCT naar SUM Niet-lege echte cellen
Laten we zeggen dat je een lege cel hebt en in die cel heb je een spatie. Nu ziet deze cel eruit als een lege cel, maar niet leeg .
In deze situatie berekent SUMIF ook de som voor deze cel.
Om dit probleem op te lossen, kunt u een combinatie van SUMPRODUCT , LEN en TRIM gebruiken. Met deze combinatie kunt u een matrixformule maken. Zie het onderstaande voorbeeld.
=SUMPRODUCT(--(LEN(TRIM(A2:A13))>0),B2:B13)Wanneer u deze functie invoert, worden deze lege spaties uit de cellen verwijderd met behulp van TRIM. Maar er is één ding dat u moet weten over SOMMEPROD. Het kan een hele tabel in één cel bevatten zonder de toetsen CTRL+ALT+ENTER te gebruiken.
Laten we deze formule nu in vijf kleine delen opsplitsen om het te begrijpen en te zien hoe deze verbazingwekkende formule werkt.
In het eerste deel gebruikten we TRIM en verwezen we naar de volledige naamkolom. TRIM neemt de kolomwaarden en verwijdert de spatie uit cellen waar u alleen spaties heeft. Dit zijn de cellen die geen waarde hebben, maar er uitzien als niet-lege cellen.
In het tweede deel hebben we LEN die de lengte van de tekens in de naamkolom telt. Voor alle cellen waarin u geen waarden heeft, is het aantal tekens nul.
Zoals u kunt zien, hebben we waarden groter dan nul voor alle niet-lege cellen.
In het derde deel hebben we een groter dan-operator gebruikt om het resultaat van LEN en TRIM te vergelijken. En het zet de telling om in WAAR en ONWAAR. Voor een getal groter dan nul, een WAAR en anders ONWAAR.
In het vierde deel hebben we een dubbel minteken dat deze waarden WAAR en ONWAAR omzet in 0 en 1. Voor WAAR een 0 en ONWAAR een 1.
Daarna hebben we in het vijfde deel SUMPRODUCT met twee arrays. En als deze arrays met elkaar worden vermenigvuldigd, krijg je alleen de som voor de cellen die niet leeg zijn en geen onzichtbare ruimte hebben.