Samenvatting
In bepaalde gevallen kunt u SUMIFS gebruiken als een opzoekformule om een numerieke waarde op te halen. In het getoonde voorbeeld is de formule in G6:
=SUMIFS(sales,region,G4,quarter,G5)
waarbij regio (B5: B20), kwartaal (C5: C20) en verkoop (D5: D20) bereiken worden genoemd.
Het resultaat is een verkoop in het derde kwartaal voor de regio Central, 127.250.
Uitleg
Als u nieuw bent met de SUMIFS-functie, kunt u hier een basisoverzicht met veel voorbeelden vinden.
De SUMIFS-functie is ontworpen om numerieke waarden op te tellen op basis van een of meer criteria. In specifieke gevallen kunt u echter SUMIFS gebruiken om een numerieke waarde op te zoeken die aan de vereiste criteria voldoet. De belangrijkste redenen om dit te doen zijn eenvoud en snelheid.
In het getoonde voorbeeld hebben we kwartaalverkoopgegevens voor vier regio's. We beginnen met SUMIFS een sombereik te geven, en de eerste voorwaarde, die regio test op de waarde in G4, "Centraal":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Sombereik is verkoop (D5: D20)
- Criteriabereik 1 is regio (B5: B20)
- Criteria 1 is G4 ("Centraal")
Vervolgens voegen we het tweede bereik / criteriapaar toe, dat kwartaal controleert:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Criteriabereik 2 is kwart (C5: C20)
- Criterium 2 is G5 ("Q3")
Met deze criteria retourneert SUMIFS 127.250, het Central Q3-verkoopnummer.
Het gedrag van SUMIFS is om alle overeenkomende waarden op te tellen. Omdat er echter maar één overeenkomende waarde is, is het resultaat hetzelfde als de waarde zelf.
Hieronder bekijken we verschillende opties voor opzoekformules.
Opzoekformule-opties
In dit gedeelte worden kort andere formule-opties besproken die hetzelfde resultaat opleveren. Met uitzondering van SOMPRODUCT (onderaan), zijn dit meer traditionele opzoekformules die de positie van de doelwaarde lokaliseren en de waarde op die locatie retourneren.
Met VERT.ZOEKEN
Helaas is VERT.ZOEKEN geen goede oplossing voor dit probleem. Met een hulpkolom is het mogelijk om een VERT.ZOEKEN-formule te bouwen die overeenkomt met meerdere criteria (voorbeeld hier), maar het is een lastig proces waarbij je aan de brongegevens moet sleutelen.
Met INDEX en MATCH
INDEX en MATCH is een zeer flexibele opzoekcombinatie die voor allerlei opzoekproblemen kan worden gebruikt, en dit voorbeeld is geen uitzondering. Met INDEX en MATCH kunnen we de omzet per regio en kwartaal opzoeken met een matrixformule zoals deze:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
De truc met deze benadering is om booleaanse logica te gebruiken met array-bewerkingen in de MATCH-functie om een array van 1s en 0s te bouwen als de lookup-array. Vervolgens kunnen we de MATCH-functie vragen om het nummer 1 te vinden. Zodra de lookup-array is gemaakt, wordt de formule opgelost in:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Met slechts 1 resterend in de opzoekmatrix, retourneert MATCH een positie van 11 naar de functie INDEX, en INDEX retourneert het verkoopnummer op die positie, 127.250.
Zie voor meer details: INDEX en MATCH met meerdere criteria
Met XLOOKUP
XLOOKUP is een flexibele nieuwe functie in Excel die native arrays kan verwerken. Met XLOOKUP kunnen we exact dezelfde aanpak gebruiken als met INDEX en MATCH, met behulp van booleaanse logica en array-bewerkingen om een lookup-array te maken:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Nadat de matrixbewerkingen zijn uitgevoerd, wordt de formule omgezet in:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
En XLOOKUP retourneert hetzelfde resultaat als hierboven, 127.250.
Meer: XLOOKUP met meerdere criteria
Met ZOEKEN
De functie ZOEKEN is een oudere functie in Excel die veel mensen niet eens kennen. Een van de belangrijkste sterke punten van LOOKUP is dat het native arrays kan verwerken. LOOKUP heeft echter een paar duidelijke zwakke punten:
- Kan niet worden vergrendeld in 'modus voor exact zoeken'
- Gaat er altijd van uit dat de opzoekgegevens zijn gesorteerd, AZ
- Retourneert altijd een geschatte overeenkomst (als de exacte overeenkomst niet kan worden gevonden)
Desalniettemin kan LOOKUP worden gebruikt om dit probleem als volgt op te lossen:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
wat vereenvoudigt tot:
=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)
If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.
We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.
More detailed explanation here.
With SUMPRODUCT
As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:
=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))
After the array math inside SUMPRODUCT is complete, the formula simplifies to:
=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))
This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.
See this example for a more complete explanation.
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
Summary
SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements:
- The result must be numeric data
- Criteria must match only one result
Als de situatie niet aan beide vereisten voldoet, is SUMIFS geen goede keuze.
