In dit artikel leren we SOM IF met behulp van opzoekwaarden in Excel.
In eenvoudige bewoordingen, terwijl we met verschillende gegevenstabellen werken, moeten we soms waarden berekenen in de eerste tabelgebaseerde waarden in een andere tabel. Voorwaarde is om de som van de waarden in de eerste tabel te krijgen met behulp van de tweede tabelwaarden
Voor dit artikel hebben we de volgende functies nodig:
- SOMPRODUCT-functie
- SUMIF-functie
De functie SOMPRODUCT is een wiskundige functie in Excel. Het werkt op meerdere bereiken. Het vermenigvuldigt de overeenkomstige arrays en voegt ze vervolgens toe.
Totaal= ( A1 * B1 * C1 *… + A2 * B2 * C2 *… +… )
Syntaxis:
= SOMPRODUCT (array1, [array2],…)
array : Het is het bereik of een lijst met waarden.
SUMIF-functie in Excel retourneert de SOM van de getallen die aan de opgegeven criteria hebben voldaan. SUM-bereik en criteriabereik kunnen hetzelfde of verschillend zijn.
Generieke Excel SUMIF-formule:
= SUMIF (criteria_bereik, criteria, som_bereik)
criteria_range : bereik waar voorwaarde wordt toegepast.
criteria: voorwaarde om toe te passen op criteria_bereik.
sum_range : sombereik op basis van criteria.
Nu gaan we een formule maken van deze functies. Hier zullen we de gegevens geven en we hadden somresultaten nodig waarbij de waarde overeenkomt met de waarde in de opzoektabel.
Generieke formule:
= SOMPRODUCT ( SUMIF ( resultaat, records, sum_nums))
resultaat : waarden die overeen moeten komen in de opzoektabel
dossier : waarden in de eerste tabel
sum_nums : waarden die moeten worden opgeteld volgens overeenkomende waarden.
Laten we deze formule testen door het op een voorbeeld uit te voeren
Hier berekenen we het totale aantal punten van een voetbalteam met behulp van de puntentabel of opzoektabel.
Hier resultaat (B8:B10) gegeven als benoemd bereik & punten (C8:C10) gegeven als benoemd bereik.
Nu zullen we de onderstaande formule gebruiken om de SOM van punten voor Maand1 in F3-cel te krijgen
Gebruik de formule:
= SOMPRODUCT ( SUMIF ( resultaat, B3:E3, punten))
Uitleg:
- De functie SUMIF komt overeen met het resultaat met waarden in B3:E3.
- Vervolgens worden punten die overeenkomen met het resultaat teruggegeven aan de functie SOMPRODUCT.
- De functie SOMPRODUCT retourneert de SOM van waarden die als matrix aan de functie zijn gegeven.
Zoals je hier kunt zien, is het totale aantal punten dat het team in de eerste maand heeft gescoord 7.
3 (W) + 0 (L) + 1 (D) + 3 (W) = 7 punten
Kopieer de formule in andere cellen, selecteer de cellen die de eerste cel nemen waar de formule al is toegepast, gebruik de sneltoets Ctrl + D of gebruik de celoptie voor slepen in Excel.
Zoals je kunt zien in de bovenstaande momentopname, zijn de punten gescoord in de 4 verschillende maanden.
Ontvang de SOM van alle punten voor 4 maanden.
Dat betekent dat het team in 4 maanden in totaal 18 punten heeft gescoord.
Alternatieve methode
Gebruik de SOM- en matrixmethode in plaats van de SOMPRODUCT-functie.
Gebruik de formule:
{ = SOM ( SUMIF ( resultaat , B3:E3 , punten )) }
Krullende accolades is de matrixmethode om de functie voor te stellen om alle geretourneerde waarden te berekenen met de functie SUMIF.
Let op: niet doen gebruik de accolades-symbolen handmatig. Gebruik de Ctrl + Shift + Enter om de matrixmethode in Excel te gebruiken.
Zoals je aan de bovenstaande formule kunt zien, kun je totale punten krijgen met behulp van een voorwaardelijke opzoektabel.
Opmerkingen:
- De formule werkt alleen met getallen.
- De formule werkt alleen als er geen duplicaten zijn in de opzoektabel
- De functie SOMPRODUCT beschouwt niet-numerieke waarden als nullen.
- De functie SOMPRODUCT beschouwt de logische waarde TRUE als 1 en False als 0.
- De argumentarray moet even lang zijn, anders is de functie
Ik hoop dat dit artikel over het retourneren van SUM alleen uit formules in Excel verklarend is. Vind hier meer artikelen over SUMPRODUCT-functies. Deel uw vraag hieronder in het opmerkingenveld. Wij zullen u helpen.
Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na vermenigvuldiging van waarden in meerdere arrays in Excel.
SOM als datum tussen ligt : Retourneert de SOM van waarden tussen bepaalde datums of periode in Excel.
Som als datum groter is dan opgegeven datum: Retourneert de SOM van waarden na de opgegeven datum of periode in Excel.
2 manieren om per maand op te tellen in Excel: Retourneert de SOM van waarden binnen een bepaalde specifieke maand in Excel.
Meerdere kolommen optellen met voorwaarde: Retourneert de SOM van waarden over meerdere kolommen met voorwaarde in Excel
populaire artikels
50 Excel-snelkoppeling om uw productiviteit te verhogen
Een vervolgkeuzelijst bewerken
Absolute referentie in Excel
Indien met voorwaardelijke opmaak
Indien met jokertekens
Opzoeken op datum
Converteer inches naar feet en inches in Excel 2016
Voeg voor- en achternaam toe in Excel
Tel cellen die overeenkomen met A of B