In deze tutorial wordt het verschil uitgelegd tussen de SUMIF- en SUMIFS-functies in termen van hun syntaxis en gebruik, en biedt een aantal formulevoorbeelden om waarden op te tellen met meerdere EN / OF-criteria in Excel 2019, 2016, 2013, 2010, 2007, 2003 en lager.
Zoals iedereen weet, biedt Microsoft Excel een matrix van functies om verschillende berekeningen met gegevens uit te voeren. Een paar artikelen geleden hebben we COUNTIF en COUNTIFS onderzocht, die zijn ontworpen voor het tellen van cellen op basis van respectievelijk een enkele conditie en verschillende condities. Vorige week hebben we Excel SUMIF besproken die waarden toevoegt die voldoen aan de opgegeven criteria. Nu is het tijd om de meervoudsversie van SUMIF te bekijken – Excel SUMIFS waarmee waarden op basis van meerdere criteria kunnen worden opgeteld.
Degenen die bekend zijn met de SUMIF-functie, denken misschien dat het converteren naar SUMIFS slechts een beetje extra kost. “S” en een paar aanvullende criteria. Dit lijkt heel logisch… maar “logisch” is het niet altijd het geval bij het omgaan met Microsoft 🙂
- SUMIF-functie – syntaxis & gebruik
- SUMIFS-functie – syntaxis & gebruik
- Hoe SUMIFS te gebruiken in Excel (EN logica)
- Excel SUMIF met meerdere criteria (OR-logica)
- Excel SUMIFS met meerdere OR-voorwaarden
- Excel SUM gebruiken in matrixformules
Excel SUMIF-functie – syntaxis & gebruik
De SUMIF-functie wordt gebruikt om voorwaardelijk waarden op te tellen op basis van een enkel criterium. We hebben de syntaxis SUMIF in detail besproken in het vorige artikel, dus ik zal je nu een korte samenvatting geven.
-
range– het celbereik dat aan de hand van uw criteria moet worden geëvalueerd, vereist. -
criteria– de voorwaarde waaraan moet worden voldaan, vereist. -
sum_range– de cellen die moeten worden opgeteld als aan de voorwaarde is voldaan, optioneel.
Zoals u ziet, is de syntaxis van de Excel SUMIF-functie staat slechts één voorwaarde toe. En toch zeggen we dat Excel SUMIF kan worden gebruikt om waarden met meerdere criteria op te tellen. Hoe kan dat zijn? Door de resultaten van verschillende SUMIF-functies toe te voegen en door SUMIF-formules met matrixcriteria te gebruiken, zoals wordt aangetoond in de volgende voorbeelden.
Excel SUMIFS-functie – syntaxis & gebruik
U gebruikt SUMIFS in Excel om een voorwaardelijke som van waarden te vinden op basis van meerdere criteria. De SUMIFS-functie is geïntroduceerd in Excel 2007, zodat u deze kunt gebruiken in alle moderne versies van Excel 2019, 2016, 2013, 2010 en 2007.
In vergelijking met SUMIFS is de SUMIFS-syntaxis iets complexer :
De eerste 3 argumenten zijn verplicht, aanvullende bereiken en de bijbehorende criteria zijn optioneel.
-
sum_range– een of meer cellen om op te tellen, vereist. Dit kan een enkele cel zijn, een celbereik of een benoemd bereik. Alleen cellen met getallen worden opgeteld; blanco en tekstwaarden worden genegeerd. -
criteria_range1– het eerste bereik dat moet worden geëvalueerd door de bijbehorende criteria, vereist. -
criteria1– de eerste voorwaarde waaraan moet worden voldaan, vereist. U kunt de criteria opgeven in de vorm van een getal, logische uitdrukking, celverwijzing, tekst of een andere Excel-functie. U kunt bijvoorbeeld criteria gebruiken zoals 10, “> = 10”, A1, “kersen” of TODAY (). -
criteria_range2, criteria2, …– dit zijn aanvullende bereiken en criteria die eraan zijn gekoppeld, optioneel. U kunt maximaal 127 paren bereik / criterium gebruiken in SUMIFS-formules.
En nu, laten we ” s bekijk de Excel SUMIFS-formule met twee voorwaarden. Stel dat u een tabel heeft met de partijen fruit van verschillende leveranciers. U hebt de fruitnamen in kolom A, leveranciersnamen in kolom B en hoeveelheid in kolom C. Wat u wilt, is een som van bedragen te weten komen die betrekking hebben op een bepaald fruit en een leverancier, bijv alle appels geleverd door Pete.
Wanneer je “iets nieuws leert”, is het altijd een goed idee om met eenvoudige dingen te beginnen. Laten we dus om te beginnen alle argumenten voor onze SUMIFS-formule definiëren:
- som_bereik – C2: C9
- criteriumbereik1 – A2: A9
- criteria1 – “appels”
- criteria_range2 – B2: B9
- criteria2 – “Pete”
Stel nu de bovenstaande parameters samen, en je krijgt de volgende SUMIFS-formule:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Om te verfijnen de formule verder, kunt u de tekstcriteria “appels” en “Piet” vervangen door celverwijzingen.In dit geval hoeft u “de formule niet te wijzigen om de hoeveelheid ander fruit van een andere leverancier te berekenen:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
SUMIFS en SUMIF gebruiken in Excel – dingen om te onthouden
Aangezien het doel van deze tutorial is om alle mogelijke manieren te behandelen om waarden op te tellen door verschillende voorwaarden, zullen we formulevoorbeelden met beide functies bespreken – Excel SUMIFS en SUMIF met meerdere criteria. Om ze correct te gebruiken, moet u duidelijk begrijpen wat deze twee functies gemeen hebben en op welke manier ze verschillen.
Hoewel het gemeenschappelijke deel duidelijk is – vergelijkbare bestemming en parameters – zijn de verschillen zijn niet zo voor de hand liggend, maar zeer essentieel.
1. De volgorde van argumenten
In Excel SUMIF- en SUMIFS-functies is de volgorde van argumenten anders. In het bijzonder is het sombereik het eerste parameter in SUMIFS, maar het is de 3e in SUMIF.
Op het eerste gezicht lijkt het misschien dat Microsoft opzettelijk de leercurve voor zijn gebruikers compliceert. Howev eh, bij een nadere beschouwing zul je de redenering erachter zien. Het punt is dat optelbereik optioneel is in SUMIF. Als je het weglaat, geen probleem, zal je SUMIF-formule de waarden in het bereik (eerste parameter) optellen.
In SUMIFS is het optelbereik erg belangrijk en verplicht, en daarom komt het op de eerste plaats. Waarschijnlijk dachten Microsoft-jongens dat iemand, na het toevoegen van het 10e of 100e bereik / criterium-paar, zou kunnen vergeten het bereik op te geven dat moet worden opgeteld:)
2. Sombereik en criteriumbereik moeten even groot zijn
In de SUMIF-functie hoeft het argument optelbereik niet noodzakelijk dezelfde grootte te hebben als het bereikargument, zolang u de cel linksboven maar rechts heeft. In Excel SUMIFS moet elk criteriumbereik hetzelfde aantal rijen en kolommen bevatten als de parameter optelbereik.
=SUMIF(A2:A9,F1,C2:C18) retourneert bijvoorbeeld het juiste resultaat omdat Excel beschouwt alleen de cel meest linksboven in het argument optelbereik (C2 in dit voorbeeld, wat correct is), en bevat dan zoveel kolommen en rijen als het bereikargument.
Oké, voldoende strategie (dwz theorie) , laten we dieper ingaan op de tactiek (dwz formulevoorbeelden 🙂
Hoe SUMIFS in Excel te gebruiken – formulevoorbeelden
Zojuist bespraken we een eenvoudige SUMIFS-formule met twee tekst criteria. Op dezelfde manier kunt u Excel SUMIFS gebruiken met meerdere criteria uitgedrukt door getallen, datums, logische uitdrukkingen en andere Excel-functies.
Voorbeeld 1. Excel SUMIFS met vergelijkingsoperatoren
Stel dat u in onze tabel met fruitleveranciers alle leveringen door Mike wilt optellen met een aantal van 200 of meer. Hiervoor gebruikt u de vergelijkingsoperator “groter dan o r gelijk aan “(> =) in criteria2 en verkrijg de volgende SUMIFS-formule:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
We hebben alle mogelijke vergelijkingsoperatoren in detail besproken bij de bespreking van de Excel SUMIF-functie, dezelfde operatoren kan worden gebruikt in SUMIFS-criteria. De volgende formule retourneert bijvoorbeeld de som van alle waarden in de cellen C2: C9 die groter zijn dan of gelijk aan 200 en kleiner dan of gelijk aan 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Voorbeeld 2. Excel gebruiken SUMIFS met datums
Als u waarden wilt optellen met meerdere criteria op basis van de huidige datum, gebruik dan de functie TODAY () in uw SUMIFS criteria, zoals hieronder aangetoond. De volgende formule somt waarden op in kolom D als een overeenkomstige datum in kolom C in de afgelopen 7 dagen valt, inclusief vandaag:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
Op een vergelijkbare manier kunt u de Excel SUMIF-functie gebruiken om waarden in een bepaald datumbereik op te tellen. De volgende SUMIFS-formule voegt bijvoorbeeld de waarden in de cellen C2: C9 toe als een datum in kolom B valt tussen 1-okt-2014 en 31-okt-2014.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Hetzelfde resultaat kan worden bereikt door het verschil tussen twee SUMIF-functies te berekenen, zoals aangetoond in dit voorbeeld – Hoe SUMIF te gebruiken om waarden in een bepaald datumbereik op te tellen. Excel SUMIFS is echter veel eenvoudiger en begrijpelijker, nietwaar?
Voorbeeld 3. Excel SUMIFS met lege en niet-lege cellen
Bij het analyseren van rapporten en andere gegevens moet vaak waarden worden opgeteld die overeenkomen met lege of niet-lege cellen.
Laten we nu eens kijken hoe u een SUMIFS-formule kunt gebruiken met “blanco” en “niet-lege” criteria op real gegevens.
Stel, u heeft een besteldatum in kolom B, leverdatum in kolom C en aantal. in kolom D. Hoe vind je het totaal aan producten die nog niet zijn geleverd? Dat wil zeggen, u wilt de som van waarden weten die overeenkomen met niet-lege cellen in kolom B en lege cellen in kolom C.
De oplossing is om de SUMIFS-formule te gebruiken met 2 criteria:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Excel SUMIF gebruiken met meerdere OR-criteria
Zoals opgemerkt in het begin van deze tutorial, is de SUMIFS-functie ontworpen met AND-logica. Maar wat als u waarden moet optellen met meerdere OF-criteria, dwz wanneer aan ten minste een van de voorwaarden is voldaan?
Voorbeeld 1. SUMIF + SUMIF
De eenvoudigste oplossing is optellen de resultaten die worden geretourneerd door verschillende SUMIF-functies. De volgende formule laat bijvoorbeeld zien hoe u het totaal aan producten kunt vinden dat door Mike en John is geleverd:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Zoals u ziet, voegt de eerste SUMIF-functie de hoeveelheden toe die overeenkomen met “Mike”, de andere SUMIF-functie retourneert de bedragen die betrekking hebben op “John” en vervolgens voegt u deze 2 getallen toe.
Voorbeeld 2. SUM & SUMIF met een array-argument
De bovenstaande oplossing is heel eenvoudig en kan de klus snel klaren als er alleen een paar criteria. Maar een SUMIF + SUMIF-formule kan enorm opgroeien als u waarden wilt optellen met meerdere OF-voorwaarden. In dit geval is een betere benadering het gebruik van een matrixcriteriumargument in de SUMIF-functie. Laten we deze benadering nu eens bekijken.
U kunt beginnen door al uw voorwaarden op te sommen, gescheiden door kommas en vervolgens de resulterende door kommas gescheiden lijst tussen {accolades} te plaatsen, wat technisch een array wordt genoemd.
Het matrixargument dat uit 3 waarden bestaat, dwingt je SUMIF-formule om drie afzonderlijke resultaten te retourneren, maar aangezien we de formule in een enkele cel schrijven, zou het alleen het eerste resultaat retourneren, dat wil zeggen het totaal van de producten geleverd door John . Om deze benadering met matrixcriteria te laten werken, moet je nog een klein trucje gebruiken – plaats je SUMIF-formule in een SOM-functie, zoals deze:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Zoals u ziet, maakt een matrixcriterium de formule veel compacter in vergelijking met SUMIF + SUMIF, en kunt u zoveel waarden toevoegen als die je leuk vindt in de array.
Deze benadering werkt zowel met getallen als met tekstwaarden. Als je bijvoorbeeld in plaats van de leveranciersnamen in kolom C, leverancier-IDs had zoals 1, 2, 3 enz. ., dan ziet uw SUMIF-formule er ongeveer zo uit:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
In tegenstelling tot tekstwaarden hoeven getallen niet tussen dubbele aanhalingstekens te staan tussen array-argumenten.
Voorbeeld 3. SUMPRODUCT & SUMIF
In het geval dat uw voorkeur uitgaat naar het vermelden van de criteria in sommige cellen in plaats van specificeer ze rechtstreeks in de formule, je kunt SUMIF gebruiken in combinatie met de SUMPRODUCT-functie die componenten in de gegeven arrays vermenigvuldigt en de som van die producten retourneert.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Waar G2: G4 de cellen zijn die uw criteria bevatten, de namen van de leveranciers in ons geval, zoals geïllustreerd in de onderstaande schermafbeelding.
Maar natuurlijk weerhoudt niets u ervan een opsomming van de waarden in een matrixcriteria van uw SUMIF-functie als u:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Het resultaat dat door beide formules wordt geretourneerd, is identiek aan wat u ziet in de schermafbeelding:
Excel SUMIFS met meerdere OF-criteria
Als u voorwaardelijk waarden in Excel wilt optellen, niet alleen met meerdere OF-voorwaarden, maar met verschillende sets voorwaarden, moet u SUMIFS gebruiken in plaats van SUMIF. De formules zullen erg lijken op wat we zojuist hebben besproken.
Zoals gewoonlijk kan een voorbeeld helpen om het punt beter te illustreren. Laten we in onze tabel met fruitleveranciers de leverdatum (kolom E) en vind de totale hoeveelheid geleverd door Mike, John en Pete in oktober.
Voorbeeld 1. SUMIFS + SUMIFS
De formule die door deze benadering wordt geproduceerd, bevat veel herhaling en uiterlijk omslachtig, maar het is gemakkelijk te begrijpen en, belangrijker nog, het werkt 🙂
Voorbeeld 2. SUM & SUMIFS met een array-argument
Ik heb geprobeerd de essentie van deze benadering uit te leggen in het SUMIF-voorbeeld, dus nu kunnen we die formule gewoon kopiëren, de volgorde van argumenten wijzigen (zoals u zich herinnert is het anders in SUMIF en SUMIFS) en aanvullende criteria toevoegen. resulterende formule is compacter dan SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Het resultaat dat door deze formule wordt geretourneerd, is exact hetzelfde als dat u ziet in bovenstaande schermafbeelding.
Voorbeeld e 3. SOMPRODUCT & SUMIFS
Zoals u zich herinnert, verschilt de SUMPRODUCT-benadering van de vorige twee in de manier waarop u elk van uw criteria in een aparte cel invoert in plaats van ze rechtstreeks in de formule te specificeren. In het geval van meerdere criteriumsets, zal de functie SUMPRODUCT niet voldoende zijn en zult u ook ISNUMBER en MATCH moeten gebruiken.
Dus, aangenomen dat de benodigdhedennamen in de cellen H1: H3 staan, startdatum in cel H4 en einddatum in cel H5, neemt onze SOMPRODUCT-formule de volgende vorm aan:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Veel mensen vragen zich af waarom dubbele streepjes (-) in SOMPRODUCT-formules worden gebruikt. Het punt is dat Excel SOMPRODUCT alle behalve numerieke waarden negeert, terwijl de vergelijkingsoperatoren in onze formule Booleaanse waarden (TRUE / FALSE) retourneren, die niet-numeriek zijn. Om deze Booleaanse waarden om te zetten in 1 “s en 0” s, gebruik je het dubbele minteken, dat technisch gezien de dubbele unaire operator wordt genoemd. De eerste unaire dwingt TRUE / FALSE respectievelijk naar -1/0. De tweede unaire ontkent de waarden, d.w.z. keert het teken om en verandert ze in +1 en 0, wat de SUMPRODUCT-functie kan begrijpen.
Ik hoop dat de bovenstaande uitleg logisch is. En zelfs als dat niet het geval is, onthoud dan deze vuistregel – gebruik de dubbele unaire operator (-) wanneer u vergelijkingsoperatoren gebruikt in uw SOMPRODUCT-formules.
Excel SUM gebruiken in matrixformules
Zoals u zich herinnert, heeft Microsoft de SUMIFS-functie geïmplementeerd in Excel 2007. Als iemand nog steeds Excel 2003, 2000 of eerder gebruikt, moet u een SUM-matrixformule gebruiken om waarden met meerdere EN-criteria toe te voegen. werkt ook in moderne versies van Excel 2013 – 2007 en kan worden beschouwd als een ouderwetse tegenhanger van de SUMIFS-functie.
In de SUMIF-formules die hierboven zijn besproken, heb je al array-argumenten gebruikt, maar een matrixformule is iets anders.
Voorbeeld 1. Som met meerdere EN-criteria in Excel 2003 en eerder
Laten we teruggaan naar het allereerste voorbeeld waarin we een som van bedragen ontdekten naar een bepaald fruit en leverancier:
Zoals u al weet, is deze taak eenvoudig te volbrengen met een gewone SUMIFS-formule:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Laten we nu eens kijken hoe dezelfde taak kan worden vervuld in vroege “SUMIFS-vrije” versies van Excel . Allereerst noteert u alle voorwaarden waaraan moet worden voldaan in de vorm van bereik = “voorwaarde”. In dit voorbeeld hebben we twee bereik / voorwaarde-paren:
Conditie 1: A2: A9 = “appels”
Conditie 2: B2: B9 = “Pete”
Vervolgens schrijft u een SOM-formule die al uw bereik / voorwaarde-paren “vermenigvuldigt”, elk tussen haakjes. De laatste vermenigvuldiger is het sombereik, C2: C9 in ons geval:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Zoals geïllustreerd in de onderstaande schermafbeelding, is de formule werkt perfect in de laatste Excel 2013-versie.
Voorbeeld 2. SUM-matrixformules in moderne Excel-versies
Zelfs in moderne versies van Excel mag de kracht van de SUM-functie niet worden onderschat. De SUM-matrixformule is niet alleen gymnastiek van de geest, maar heeft een praktische waarde, zoals aangetoond in het volgende voorbeeld.
Stel dat , je hebt twee kolommen, B en C, en je moet tellen hoe vaak kolom C groter is dan kolom B, wanneer een waarde in kolom C groter of gelijk is aan 10. Een onmiddellijke oplossing die in je opkomt, is het gebruik van de SUM matrixformule:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Zie geen praktische toepassing op de bovenstaande formule? Denk er op een andere manier over na:)
Stel dat u de bestellijst heeft zoals weergegeven in de onderstaande schermafbeelding en u wilt weten hoeveel producten op een bepaalde datum niet volledig zijn geleverd. Vertaald naar de taal van Excel hebben we de volgende voorwaarden:
Conditie 1: een waarde in kolom B (geordende items) is groter dan 0
Conditie 2: een waarde in kolom C (Geleverd) in minder dan in kolom B
Voorwaarde 3: Een datum in kolom D (Vervaldatum) is minder dan 11/1/2014.
De drie reeksen / voorwaardeparen samen, krijgt u de volgende formule:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Welnu, de formulevoorbeelden die in deze tutorial worden besproken, hebben alleen maar duidelijk gemaakt wat Excel SUMIFS- en SUMIF-functies echt kunnen doen. Maar hopelijk hebben ze u geholpen in de goede richting te wijzen en kunt u nu waarden optellen in uw Excel-werkmappen, ongeacht hoeveel ingewikkelde omstandigheden u moet overwegen.
- Hoe SUMIF in Excel te gebruiken – formulevoorbeelden om cellen voorwaardelijk op te tellen
- Hoe Excel SUMIFS en SUMIF met meerdere criteria te gebruiken – formulevoorbeelden
- VERT.ZOEKEN gebruiken & SUM- of SUMIF-functies in Excel
- SUMIF in Google Spreadsheets met formulevoorbeelden
- AANTAL.ALS in Excel – tel zo niet blanco, groter dan, duplicaat of uniek
- Cellen tellen en optellen op kleur in Excel