Deze tutorial laat zien hoe je de Excel SUMIF en SOMMEN Functies in Excel en Google Spreadsheets om gegevens op te tellen die aan bepaalde criteria voldoen.
SUMIF-functieoverzicht
U kunt de SOM.ALS-functie in Excel gebruiken om cellen op te tellen die een specifieke waarde bevatten, cellen optellen die groter zijn dan of gelijk zijn aan een waarde, enz.
(Let op hoe de formule-invoer verschijnt)
SUMIF Functiesyntaxis en argumenten:
| 1 | =SUMIF (bereik, criteria, [som_bereik]) |
bereik - Het cellenbereik waarop u de criteria wilt toepassen.
criteria - De criteria die worden gebruikt om te bepalen welke cellen moeten worden toegevoegd.
sum_range - [optioneel] De cellen die bij elkaar moeten worden opgeteld. Als som_bereik wordt weggelaten, worden de cellen in het bereik in plaats daarvan bij elkaar opgeteld.
Wat is de SUMIF-functie?
De SUMIF-functie is een van de oudere functies die in spreadsheets worden gebruikt. Het wordt gebruikt om door een celbereik te scannen en op een specifiek criterium te controleren en vervolgens waarden op te tellen in een bereik dat overeenkomt met die waarden. De oorspronkelijke SUMIF-functie was beperkt tot slechts één criterium. Na 2007 werd de SUMIFS-functie gecreëerd die een groot aantal criteria mogelijk maakt. Het meeste algemene gebruik blijft hetzelfde tussen de twee, maar er zijn enkele kritische verschillen in de syntaxis die we in dit artikel zullen bespreken.
Als je dat nog niet hebt gedaan, kun je veel van de vergelijkbare structuur en voorbeelden bekijken in het AANTAL.ALS-artikel.
Basis voorbeeld
Laten we eens kijken naar deze lijst met geregistreerde verkopen en we willen het totale inkomen weten.
Omdat we een uitgave hadden, de negatieve waarde, kunnen we niet zomaar een basissom doen. In plaats daarvan willen we alleen de waarden optellen die groter zijn dan 0. De "groter dan 0" is wat onze criteria zullen zijn in een SUMIF-functie. Onze formule om dit aan te geven is
| 1 | =SOM.ALS(A2:A7; ">0") |
Voorbeeld met twee kolommen
Hoewel de oorspronkelijke SUMIF-functie is ontworpen om u een criterium te laten toepassen op het bereik van getallen dat u wilt optellen, zult u vaak een of meer criteria op andere kolommen moeten toepassen. Laten we eens kijken naar deze tabel:
Als we nu de originele SUMIF-functie gebruiken om erachter te komen hoeveel bananen we hebben (vermeld in cel D1), moeten we het bereik geven dat we willen som als het laatste argument, en dus zou onze formule zijn
| 1 | =SOM.ALS(A2:A7, D1, B2:B7) |
Toen programmeurs zich uiteindelijk realiseerden dat gebruikers meer dan één criterium wilden geven, werd de SOMMEN-functie gemaakt. Om één structuur te creëren die voor een willekeurig aantal criteria zou werken, vereist de SUMIFS dat het sombereik eerst wordt vermeld. In ons voorbeeld betekent dit dat de formule moet zijn
| 1 | =SOMMEN.(B2:B7, A2:A7, D1) |
OPMERKING: Deze twee formules krijgen hetzelfde resultaat en kunnen er hetzelfde uitzien, dus let goed op welke functie wordt gebruikt om ervoor te zorgen dat u alle argumenten in de juiste volgorde opsomt.
Werken met datums, meerdere criteria
Wanneer u met datums in een spreadsheet werkt, is het weliswaar mogelijk om de datum rechtstreeks in de formule in te voeren, maar het is het beste om de datum in een cel te plaatsen, zodat u alleen naar de cel in een formule kunt verwijzen. Dit helpt de computer bijvoorbeeld om te weten dat u de datum 27-5-2020 wilt gebruiken, en niet het getal 5 gedeeld door 27 gedeeld door 2022.
Laten we eens kijken naar onze volgende tabel waarin het aantal bezoekers van een site elke twee weken wordt geregistreerd.
We kunnen het begin- en eindpunt specificeren van het bereik dat we willen bekijken in D2 en E2. Onze formule voor het optellen van het aantal bezoekers in dit bereik zou kunnen zijn:
| 1 | =SOMMEN.ALS(B2:B7, A2:A7, ">="&D2, A2:A7, "<="&E2) |
Merk op hoe we de vergelijkingen van "=" met de celverwijzingen konden samenvoegen om de criteria te creëren. Ook al werden beide criteria toegepast op hetzelfde celbereik (A2:A7), je moet het bereik twee keer uitschrijven, één keer per criterium.
Meerdere kolommen
Wanneer u meerdere criteria gebruikt, kunt u ze toepassen op hetzelfde bereik als in het vorige voorbeeld, of u kunt ze toepassen op verschillende bereiken. Laten we onze voorbeeldgegevens combineren in deze tabel:
We hebben enkele cellen ingesteld zodat de gebruiker kan invoeren waarnaar hij wil zoeken in de cellen E2 tot en met G2. We hebben dus een formule nodig die het totaal aantal geplukte appels in februari optelt. Onze formule ziet er als volgt uit:
| 1 | =SOMMEN.(C2:C7, B2:B7, ">="&F2, B2:B7, "<="&G2, A2:A7, E2) |
SOMMEN met logica van het OF-type
Tot nu toe waren de voorbeelden die we hebben gebruikt allemaal op EN gebaseerde vergelijking, waarbij we op zoek zijn naar rijen die aan al onze criteria voldoen. Nu zullen we het geval bekijken wanneer u wilt zoeken naar de mogelijkheid dat een rij aan een of ander criterium voldoet.
Laten we eens kijken naar deze lijst met verkopen:
We willen graag de totale verkoop voor zowel Adam als Bob bij elkaar optellen. Om dit te doen, heb je een aantal opties. Het eenvoudigst is om twee SUMIFS bij elkaar op te tellen, zoals:
| 1 | =SOMMEN.(B2:B7, A2:A7, "Adam")+SOMMEN.(B2:B7, A2:A7, "Bob") |
Hier hebben we de computer onze individuele scores laten berekenen en deze vervolgens bij elkaar optellen.
Onze volgende optie is goed voor wanneer u meer criteriabereiken heeft, zodat u niet de hele formule herhaaldelijk wilt herschrijven. In de vorige formule hebben we de computer handmatig verteld om twee verschillende SUMIFS bij elkaar op te tellen. U kunt dit echter ook doen door uw criteria in een array te schrijven, zoals deze:
| 1 | =SOM(SOMMEN(B2:B7, A2:A7, {"Adam", "Bob"})) |
Kijk hoe de array is opgebouwd tussen de accolades. Wanneer de computer deze formule evalueert, weet hij dat we een SOMMEN-functie willen berekenen voor elk item in onze array, waardoor een array van getallen ontstaat. De buitenste SOM-functie neemt dan die reeks getallen en verandert het in een enkel getal. Als u de formule-evaluatie doorloopt, ziet het er als volgt uit:
| 123 | =SOM(SOMMEN(B2:B7, A2:A7, {"Adam", "Bob"}))=SOM(27401, 43470)=70871 |
We krijgen hetzelfde resultaat, maar we hebben de formule wat beknopter kunnen uitschrijven.
Omgaan met lege plekken
Soms bevat uw gegevensset lege cellen die u moet zoeken of vermijden. Het instellen van de criteria hiervoor kan een beetje lastig zijn, dus laten we naar een ander voorbeeld kijken.
Merk op dat cel A3 echt leeg is, terwijl cel A5 een formule heeft die een tekenreeks met lengte nul van "" retourneert. Als we de totale som willen vinden van werkelijk lege cellen, we zouden een criterium van "=" gebruiken en onze formule zou er als volgt uitzien:
| 1 | =SOMMEN.(B2:B7,A2:A7,"=") |
Aan de andere kant, als we de som willen krijgen voor alle cellen die er visueel blanco uitzien, veranderen we de criteria in "", en de formule ziet eruit als
| 1 | =SOMMEN.(B2:B7,A2:A7,"") |
Laten we het omdraaien: wat als u de som van niet-lege cellen wilt vinden? Helaas kunt u met het huidige ontwerp de string met de lengte nul niet vermijden. U kunt een criterium van "" gebruiken, maar zoals u in het voorbeeld kunt zien, bevat het nog steeds de waarde uit rij 5.
| 1 | =SOMMEN.(B2:B7,A2:A7,"") |
Als u cellen met tekenreeksen met een lengte van nul niet wilt tellen, kunt u overwegen de LEN-functie in een SOMPRODUCT te gebruiken
SUMIF in Google Spreadsheets
De SUMIF-functie werkt in Google Spreadsheets precies hetzelfde als in Excel:
