Deze zelfstudie biedt een gedetailleerde demonstratie van het gebruik van SUMIF- en SUMIFS-functies in Google Spreadsheets met formules en voorbeelden.
SUMIF is een van de wiskundige functies in Google Spreadsheets, die wordt gebruikt om cellen voorwaardelijk op te tellen. Kortom, de SUMIF-functie zoekt naar een specifieke voorwaarde in een celbereik en telt vervolgens de waarden op die aan de gegeven voorwaarde voldoen.
U hebt bijvoorbeeld een lijst met uitgaven in Google-bladen en u wilt alleen de uitgaven optellen die boven een bepaalde maximale waarde liggen. Of je hebt een lijst met bestelartikelen en de bijbehorende bedragen, en je wilt alleen het totale bestelbedrag van een bepaald artikel weten. Dat is waar de SUMIF-functie van pas komt.
De SUMIF kan worden gebruikt om waarden op te tellen op basis van getalvoorwaarde, tekstvoorwaarde, datumvoorwaarde, jokertekens en op basis van lege en niet-lege cellen. Google Spreadsheets heeft twee functies om waarden samen te vatten op basis van criteria: SUMIF en SUMIFS. De functie SUMIF telt getallen op op basis van één voorwaarde, terwijl SUMIFS getallen optelt op basis van meerdere voorwaarden.
In deze tutorial leggen we uit hoe je de SUMIF- en SUMIFS-functies in Google Spreadsheets kunt gebruiken om getallen op te tellen die aan een bepaalde voorwaarde(n) voldoen.
SUMIF-functie in Google Spreadsheets - Syntaxis en argumenten
De SUMIF-functie is slechts een combinatie van de SOM- en IF-functie. De ALS-functie scant door het cellenbereik voor een bepaalde voorwaarde, en vervolgens telt de SOM-functie de getallen op die overeenkomen met de cellen die aan de voorwaarde voldoen.
Syntaxis van SUMIF-functie:
De syntaxis van de SUMIF-functie in Google Spreadsheets is als volgt:
=SUMIF(bereik, criteria, [som_bereik])Argumenten:
bereik - Het cellenbereik waarin we zoeken naar de cellen die aan de criteria voldoen.
criteria – De criteria die bepalen welke cellen moeten worden toegevoegd. U kunt het criterium baseren op het getal, de tekenreeks, de datum, de celverwijzing, de uitdrukking, de logische operator, het jokerteken en andere functies.
sum_range – Dit argument is optioneel. Het is het gegevensbereik met waarden die moeten worden opgeteld als de corresponderende bereikinvoer overeenkomt met de voorwaarde. Als u dit argument niet opneemt, wordt in plaats daarvan het 'bereik' opgeteld.
Laten we nu eens kijken hoe we de SUMIF-functie kunnen gebruiken om waarden met verschillende criteria op te tellen.
SUMIF-functie met nummercriteria
U kunt getallen optellen die aan bepaalde criteria voldoen in een celbereik door een van de volgende vergelijkingsoperatoren te gebruiken om criteria te maken.
- groter dan (>)
- minder dan (<)
- groter dan of gelijk aan (>=)
- kleiner dan of gelijk aan (<=)
- gelijk aan (=)
- niet gelijk aan ()
Stel dat u de volgende spreadsheet heeft en u bent geïnteresseerd in de totale verkopen van 1000 of hoger.
Zo kunt u de SUMIF-functie invoeren:
Selecteer eerst de cel waar u de uitvoer van de som wilt laten verschijnen (D3). Om getallen in B2:B12 op te tellen die groter zijn dan of gelijk zijn aan 1000, typt u deze formule en drukt u op 'Enter':
=SOM.ALS(B2:B12,">=1000",B2:B12)In deze voorbeeldformule zijn de argumenten bereik en som_bereik (B2:B12) hetzelfde, omdat verkoopcijfers en criteria op hetzelfde bereik worden toegepast. En we hebben het getal vóór de vergelijkingsoperator ingevoerd en tussen aanhalingstekens geplaatst omdat de criteria altijd tussen dubbele aanhalingstekens moeten staan, behalve bij een celverwijzing.
De formule zocht naar getallen die groter zijn dan of gelijk zijn aan 1000 en telde vervolgens alle overeenkomende waarden op en toonde het resultaat in cel D3.
Aangezien de argumenten bereik en som_bereik hetzelfde zijn, kunt u hetzelfde resultaat bereiken zonder de argumenten som_bereik in de formule, zoals deze:
=SOM.ALS(B2:B12,">=1000")
Of u kunt de celverwijzing (D2) opgeven die het getal bevat in plaats van de getalcriteria, en de vergelijkingsoperator samenvoegen met die celverwijzing in het criteriaargument:
=SOM.ALS(B2:B12,">="&D2)Zoals u kunt zien, wordt de vergelijkingsoperator nog steeds tussen dubbele aanhalingstekens ingevoerd en worden de operator en de celverwijzing aaneengeschakeld door een ampersand (&). En u hoeft de celverwijzing niet tussen aanhalingstekens te plaatsen.
Opmerking: Wanneer u verwijst naar de cel die criteria bevat, zorg er dan voor dat u geen voorloop- of volgspatie laat in de waarde in de cel. Als uw waarde onnodige spatie heeft voor of na de waarde in de cel waarnaar wordt verwezen, retourneert de formule '0' als resultaat.
U kunt op dezelfde manier ook andere logische operatoren gebruiken om voorwaarden in het argument criteria te maken. Als u bijvoorbeeld waarden kleiner dan 500 wilt optellen:
=SOM.ALS(B2:B12,"<500")
Som als getallen gelijk zijn aan
Als u getallen wilt toevoegen die gelijk zijn aan een bepaald getal, kunt u ofwel alleen het getal invoeren of het getal met het gelijkteken in het criteriumargument invoeren.
Als u bijvoorbeeld de corresponderende verkoopbedragen (kolom B) wilt optellen voor hoeveelheden (kolom C) waarvan de waarde gelijk is aan 20, probeert u een van deze formules:
=SOM.ALS(C2:C12,"=20",B2:B12)
=SOM.ALS(C2:C12,"20",B2:B12)
=SOM.ALS(C2:C12,E2,B2:B12)
Probeer deze formule om getallen in kolom B op te tellen met een hoeveelheid die niet gelijk is aan 20 in kolom C:
=SOM.ALS(C2:C12,"20",B2:B12)
SUMIF-functie met tekstcriteria
Als u getallen in een celbereik (kolom of rij) wilt optellen die overeenkomen met de cellen met een specifieke tekst, kunt u eenvoudig die tekst of de cel die de tekst bevat opnemen in het criteriaargument van uw SUMIF-formule. Houd er rekening mee dat tekenreeksen altijd tussen dubbele aanhalingstekens (" ") moeten staan.
Als u bijvoorbeeld het totale aantal verkopen in de regio 'West' wilt, kunt u de onderstaande formule gebruiken:
=SOM.ALS(C2:C13,"West",B2:B13)In deze formule zoekt de SUMIF-functie naar de waarde 'West' in celbereik C2:C13 en telt de bijbehorende verkoopwaarde op in kolom B. Vervolgens wordt het resultaat weergegeven in cel E3.
U kunt ook verwijzen naar de cel die tekst bevat in plaats van de tekst in het argument criteria te gebruiken:
=SOM.ALS(C2:C12,E2,B2:B12)
Laten we nu de totale inkomsten van alle regio's bekijken, behalve 'West'. Om dat te doen, gebruiken we niet gelijk aan de operator () in de formule:
=SOM.ALS(C2:C12,""&E2,B2:B12)
SUMIF met jokertekens
In de bovenstaande methode controleert de SUMIF-functie met tekstcriteria het bereik tegen de exact gespecificeerde tekst. Vervolgens telt het de getallen op tot exacte tekst en negeert het alle andere getallen, inclusief gedeeltelijk overeenkomende tekstreeksen. Om de getallen met gedeeltelijk overeenkomende tekstreeksen op te tellen, moet u een van de volgende jokertekens in uw criteria aanpassen:
?(vraagteken) wordt gebruikt om een willekeurig teken te matchen, waar dan ook in de tekenreeks.*(sterretje) wordt gebruikt om overeenkomende woorden te vinden samen met een willekeurige reeks tekens.~(tilde) wordt gebruikt om teksten te matchen met een vraagteken (?) of een sterretje (*).
We zullen deze voorbeeldspreadsheet voor producten en hun hoeveelheden gebruiken om getallen met jokertekens op te tellen:
Sterretje (*) Jokerteken
Als u bijvoorbeeld de hoeveelheden van alle Apple-producten wilt optellen, gebruikt u deze formule:
=SOM.ALS(A2:A14,"Appel*",B2:B14)Deze SUMIF-formule vindt alle producten met het woord "Apple" aan het begin en een willekeurig aantal tekens erachter (aangegeven met '*'). Zodra de match is gevonden, vat het de . samen Hoeveelheid nummers die overeenkomen met de overeenkomende tekstreeksen.
Het is ook mogelijk om meerdere wildcards in de criteria te gebruiken. En u kunt ook jokertekens invoeren met celverwijzingen in plaats van directe tekst.
Om dat te doen, moeten de jokertekens tussen dubbele aanhalingstekens (" ") staan en aaneengeschakeld worden met de celverwijzing(en):
=SOM.ALS(A2:A14,"*"&D2&"*",B2:B14)Deze formule telt de hoeveelheden op van alle producten met het woord 'Redmi' erin, ongeacht waar het woord zich in de tekenreeks bevindt.
Vraagteken (?) Jokerteken
U kunt het jokerteken vraagteken (?) gebruiken om tekstreeksen te matchen met afzonderlijke tekens.
Als u bijvoorbeeld hoeveelheden van alle Xiaomi Redmi 9-varianten wilt vinden, kunt u deze formule gebruiken:
=SOM.ALS(A2:A14,"Xiaomi Redmi 9?",B2:B14)De bovenstaande formule zoekt naar tekstreeksen met het woord "Xiaomi Redmi 9" gevolgd door enkele tekens en somt de bijbehorende op Hoeveelheid nummers.
Tilde (~) Jokerteken
Als u een echt vraagteken (?) of een sterretje (*) wilt gebruiken, plaatst u het tilde-teken (~) vóór het jokerteken in het voorwaardegedeelte van de formule.
Voer de onderstaande formule in om de hoeveelheden in kolom B toe te voegen met de bijbehorende tekenreeks met een asterisk-teken aan het einde:
=SOM.ALS(A2:A14,"Samsung Galaxy V~*",B2:B14)
Probeer de onderstaande formule om hoeveelheden in kolom B toe te voegen die een vraagteken (?) hebben in kolom A in dezelfde rij:
=SOM.ALS(A2:A14,"~?",B2:B14)
SUMIF-functie met datumcriteria
De functie SUMIF kan u ook helpen om waarden voorwaardelijk op te tellen op basis van datumcriteria, bijvoorbeeld getallen die overeenkomen met een bepaalde datum, of vóór een datum of na een datum. U kunt ook een van de vergelijkingsoperatoren met een datumwaarde gebruiken om datumcriteria te maken voor het optellen van getallen.
De datum moet worden ingevoerd in door Google Spreadsheets ondersteunde datumnotatie, of als een celverwijzing die een datum bevat, of met behulp van een datumfunctie zoals DATE() of TODAY().
We zullen deze voorbeeldspreadsheet gebruiken om u te laten zien hoe de SUMIF-functie met datumcriteria werkt:
Stel dat u de verkoopbedragen wilt optellen die plaatsvonden op of vóór (<=) 29 november 2019 in de bovenstaande dataset, dan kunt u die verkoopcijfers op een van de volgende manieren toevoegen met de functie SUMIF:
=SOM.ALS(C2:C13,"<=29 november 2019",B2:B13)De bovenstaande formule controleert elke cel van C2 tot C13 en komt alleen overeen met die cellen die datums bevatten op of vóór 29 november 2019 (29/11/2019). En telt vervolgens het verkoopbedrag op dat overeenkomt met die overeenkomende cellen uit het celbereik B2:B13 en geeft het resultaat weer in cellen E3.
De datum kan aan de formule worden toegevoegd in elk formaat dat wordt herkend door Google Spreadsheets, zoals '29 november 2019′, '29 nov 2019' of '29/11/2019', enz. Onthoud de datumwaarde en de operator moet altijd tussen dubbele aanhalingstekens plaatsen.
U kunt ook de functie DATE() in de criteria gebruiken in plaats van directe datumwaarde:
=SOM.ALS(C2:C13,"<="&DATUM(2019,11,29),B2:B13)
Of u kunt celverwijzing gebruiken in plaats van datum in het criteriagedeelte van de formule:
=SOM.ALS(C2:C13,"<="&E2,B2:B13)
Als u de verkoopbedragen bij elkaar wilt optellen op basis van de datum van vandaag, kunt u de functie VANDAAG() gebruiken in het argument criteria.
Als u bijvoorbeeld alle verkoopbedragen voor de datum van vandaag wilt optellen, gebruikt u deze formule:
=SOM.ALS(C2:C13,VANDAAG(),B2:B13)
SUMIF-functie met lege of niet-lege cellen
Soms moet u de getallen in een celbereik optellen met lege of niet-lege cellen in dezelfde rij. In dergelijke gevallen kunt u de SOM.ALS-functie gebruiken om waarden op te tellen op basis van criteria waar cellen al dan niet leeg zijn.
Som indien leeg
Er zijn twee criteria in Google Spreadsheets om lege cellen te vinden: "" of "="".
Als u bijvoorbeeld alle verkoopbedragen wilt optellen die tekenreeksen met een lengte nul bevatten (ziet er visueel leeg uit) in kolom C, gebruikt u dubbele aanhalingstekens zonder spatie ertussen in de formule:
=SOM.ALS(C2:C13,"",B2:B13)
Om alle verkoopbedragen in kolom B op te tellen met volledig lege cellen in kolom C, neemt u "=" op als de criteria:
=SOM.ALS(C2:C13,"=",B2:B13)
Som indien niet leeg:
Als u cellen wilt optellen die een waarde bevatten (niet leeg), kunt u "" gebruiken als de criteria in de formule:
Gebruik deze formule om bijvoorbeeld het totale aantal verkopen met datums te krijgen:
=SOM.ALS(C2:C13,"",B2:B13)
SUMIF gebaseerd op meerdere criteria met OR-logica
Zoals we tot nu toe hebben gezien, is de SUMIF-functie ontworpen om getallen op te tellen op basis van slechts één criterium, maar het is mogelijk om waarden op te tellen op basis van meerdere criteria met de SUMIF-functie in Google Spreadsheets. Dit kan worden gedaan door meer dan één SOM.ALS-functie samen te voegen in een enkele formule met OF-logica.
Als u bijvoorbeeld het verkoopbedrag in de regio 'West' of 'Zuid' (OF-logica) in het opgegeven bereik (B2:B13) wilt optellen, gebruikt u deze formule:
=SOM.ALS(C2:C13,"West",B2:B13)+SOM.ALS(C2:C13,"Zuid",B2:B13)Deze formule telt cellen op als ten minste één van de voorwaarden WAAR is. Vandaar dat het bekend staat als 'OF-logica'. Het zal ook waarden optellen wanneer aan alle voorwaarden is voldaan.
Het eerste deel van de formule controleert het bereik C2:C13 voor de tekst 'West' en telt de waarden in het bereik B2:B13 op wanneer aan de overeenkomst wordt voldaan. Het tweede deel van de controles voor de tekstwaarde 'Zuid' in hetzelfde bereik C2:C13 en sommeert vervolgens waarden met de overeenkomende tekst in hetzelfde sum_bereik B2:B13. Vervolgens worden beide sommen bij elkaar opgeteld en weergegeven in cel E3.
In gevallen dat aan slechts één criterium wordt voldaan, wordt alleen die somwaarde geretourneerd.
U kunt ook meerdere criteria gebruiken in plaats van slechts één of twee. En als u meerdere criteria gebruikt, is het beter om een celverwijzing als criterium te gebruiken in plaats van de directe waarde in de formule te schrijven.
=SOM.ALS(C2:C13,E2,B2:B13)+SOM.ALS(C2:C13,E3,B2:B13)+SOM.ALS(C2:C13,E4,B2:B13)
SUMIF met OR-logica voegt waarden toe wanneer aan ten minste één van de opgegeven criteria wordt voldaan, maar als u alleen waarden wilt optellen als aan alle opgegeven voorwaarden is voldaan, moet u de nieuwe functie SUMIFS() gebruiken.
SUMIFS-functie in Google Spreadsheets (meerdere criteria)
Wanneer u de SOM.ALS-functie gebruikt om waarden op te tellen op basis van meerdere criteria, kan de formule te lang en ingewikkeld worden en bent u vatbaar voor fouten. Bovendien kunt u met SUMIF alleen waarden optellen voor een enkel bereik en wanneer een van de voorwaarden WAAR is. Dat is waar de SOMMEN-functie binnenkomt.
Met de SOMMEN-functie kunt u waarden optellen op basis van meerdere overeenkomende criteria in een of meer bereiken. En het werkt op EN-logica, wat betekent dat het alleen waarden kan optellen als aan alle gegeven voorwaarden is voldaan. Zelfs als één voorwaarde onwaar is, wordt als resultaat '0' geretourneerd.
SUMIFS Functiesyntaxis en argumenten
De syntaxis van de SOMMEN-functie is als volgt:
=SUMIFS(som_bereik, criteria_bereik1, criterium1, [criterium_bereik2, ...], [criterium2, ...])Waar,
- sum_range – Het cellenbereik met de waarden die u wilt optellen wanneer aan alle voorwaarden is voldaan.
- criteria_bereik1 – Het is het cellenbereik waarin u controleert op criteria1.
- criteria1 – Het is de voorwaarde die u moet controleren aan de hand van criteria_range1.
- criteria_range2, criterium2, …– De aanvullende bereiken en criteria om te evalueren. En u kunt meer bereiken en voorwaarden aan de formule toevoegen.
We gebruiken de dataset in de volgende schermafbeelding om te laten zien hoe de SOMMEN-functie werkt met verschillende criteria.
SOMMEN met tekstvoorwaarden
U kunt waarden optellen op basis van twee verschillende tekstcriteria in verschillende bereiken. Stel dat u bijvoorbeeld het totale verkoopbedrag van het geleverde Tentartikel wilt weten. Gebruik hiervoor deze formule:
=SOMMEN.(D2:D13,A2:A13,"Tent",C2:C13,"Geleverd")In deze formule hebben we twee criteria: "Tent" en "Geleverd". De SOMMEN-functie controleert op het item ‘Tent’ (criterium1) in het bereik A2:A13 (criteria_bereik1) en controleert op de status ‘Geleverd’ (criterium2) in het bereik C2:C13 (criterium_bereik2). Wanneer aan beide voorwaarden is voldaan, wordt de overeenkomstige waarde in het celbereik D2:D13 (som_bereik) bij elkaar opgeteld.
SUMIFS met nummercriteria en logische operatoren
U kunt voorwaardelijke operators gebruiken om voorwaarden met getallen te maken voor de SOMMEN-functie.
Gebruik deze formule om de totale verkoop van meer dan 5 hoeveelheden van een artikel in de staat Californië (CA) te vinden:
=SOMMEN.(E2:E13,D2:D13,">5",B2:B13,"CA")Deze formule heeft twee voorwaarden: ">5" en "CA".
Deze formule controleert op hoeveelheden (aantal) groter dan 5 in het bereik D2:D13 en controleert op de status 'CA' in het bereik B2:B13. En wanneer aan beide voorwaarden is voldaan (wat betekent dat ze in dezelfde rij staan), wordt het bedrag in E2:E13 bij elkaar opgeteld.
SOMMEN met datumcriteria
Met de SOMMEN-functie kunt u ook meerdere voorwaarden in hetzelfde bereik en verschillende bereiken controleren.
Stel dat u het totale verkoopbedrag van de geleverde artikelen na 31/5/2021 en voor 10/6/2021 wilt controleren, gebruik dan deze formule:
=SOMMEN.(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Bovenstaande formule heeft drie voorwaarden: 31/5/2021,10/5/2021 en Geleverd. In plaats van directe datum- en tekstwaarden te gebruiken, verwezen we naar cellen die die criteria bevatten.
De formule controleert op datums na 31/5/2021 (G1) en datums voor 10/6/2021 (G2) in hetzelfde bereik D2:D13, en controleert op de status 'Geleverd' tussen die twee datums. Telt vervolgens het gerelateerde bedrag op in het bereik E2:E13.
SUMIFS met lege en niet-lege cellen
Soms wilt u misschien de som van waarden vinden wanneer een corresponderende cel leeg is of niet. Om dat te doen, kunt u een van de drie criteria gebruiken die we eerder hebben besproken: "=", "", en "".
Als u bijvoorbeeld alleen het aantal 'Tent'-items wilt optellen waarvan de leveringsdatum nog niet is bevestigd (lege cellen), kunt u criteria van "=" gebruiken:
=SOMMEN.(D2:D13,A2:A13,"Tent",C2:C13,"=")De formule zoekt naar het item 'Tent' (criterium1) in kolom A met bijbehorende lege cellen (criterium2) in kolom C en telt vervolgens het overeenkomstige bedrag op in kolom D. De "=" vertegenwoordigt een volledig lege cel.
Om het totaalbedrag van 'Tent'-items te vinden waarvoor de leveringsdatum is bevestigd (geen lege cellen), gebruikt u "" als criterium:
=SOMMEN.(D2:D13,A2:A13,"Tent",C2:C13,"")We hebben zojuist "=" geruild voor "" in deze formule. Het vindt de som van Tent-items met niet-lege cellen in kolom C.
SOMMEN met OR-logica
Omdat de SOMMEN-functie werkt op EN-logica, telt deze alleen op als aan alle voorwaarden is voldaan. Maar wat als u de waarde wilt optellen op basis van meerdere criteria wanneer aan een van de criteria is voldaan. De truc is om meerdere SUMIFS-functies te gebruiken.
Als u bijvoorbeeld het verkoopbedrag voor 'Fietsenrek' OF 'Rugzak' wilt optellen wanneer de status 'Besteld' is, probeer dan deze formule:
=SUMIFS(D2:D13,A2:A13,"Fietsdrager",C2:C13,"Besteld") +SUMIFS(D2:D13,A2:A13,"Rugzak",C2:C13,"Besteld")De eerste SOMMEN-functie controleert twee criteria "Fietsenrek" en "Besteld" en telt de bedragwaarden in kolom D op. Vervolgens controleert de tweede SUMIFS twee criteria "Rugzak" en "Besteld" en telt de bedragwaarden in kolom D op. En dan , worden beide sommen bij elkaar opgeteld en weergegeven op F3. In eenvoudige woorden, deze formule somt op wanneer 'Fietsenrek' of 'Rugzak' wordt besteld.
Dat is alles wat u moet weten over de SUMIF- en SUMIFS-functie in Google Spreadsheets.