In diesem Artikel lernen wir, IF mithilfe von Nachschlagewerten in Excel zu SUMMEN.
In einfachen Worten, während wir mit verschiedenen Datentabellen arbeiten, müssen wir manchmal Werte in ersten tabellenbasierten Werten in einer anderen Tabelle berechnen. Bedingung ist, die Summe der Werte in der ersten Tabelle mit den Werten der zweiten Tabelle zu erhalten
Für diesen Artikel benötigen wir die folgenden Funktionen:
- SUMMENPRODUKT-Funktion
- SUMIF-Funktion
Die SUMMENPRODUKT-Funktion ist eine mathematische Funktion in Excel. Es funktioniert in mehreren Bereichen. Es multipliziert die entsprechenden Arrays und fügt sie dann hinzu.
Gesamt= ( A1 * B1 * C1 *… + A2 * B2 * C2 *… +… )
Syntax:
= SUMMENPRODUKT ( array1 , [array2] ,… )
array : Dies ist der Bereich oder eine Liste von Werten.
Die SUMIF-Funktion in Excel gibt die SUMME der Zahlen zurück, die die angegebenen Kriterien erfüllt haben. SUM-Bereich und Kriterienbereich können gleich oder unterschiedlich sein.
Generische Excel-SUMIF-Formel:
= SUMIF ( Kriterien_Bereich , Kriterien , Summe_Bereich)
Criteria_range : Bereich, in dem die Bedingung angewendet wird.
Kriterien: Bedingung für die Anwendung auf Kriterien_Bereich.
sum_range : Summenbereich anhand von Kriterien.
Jetzt werden wir aus diesen Funktionen eine Formel machen. Hier geben wir die Daten an und wir brauchten Summenergebnisse, bei denen der Wert mit dem Wert in der Nachschlagetabelle übereinstimmt.
Generische Formel:
= SUMPRODUCT ( SUMIF ( result, records, sum_nums))
Ergebnis : Werte, die in der Nachschlagetabelle abgeglichen werden sollen
aufzeichnen : Werte in der ersten Tabelle
sum_nums : Werte, die gemäß übereinstimmenden Werten summiert werden sollen.
Lassen Sie uns diese Formel testen, indem wir sie an einem Beispiel ausführen
Hier berechnen wir die Gesamtpunktzahl einer Fußballmannschaft anhand der Punktetabelle oder Nachschlagetabelle.
Hier Ergebnis ( B8:B10 ) als benannter Bereich & Punkte ( C8:C10 ) als benannter Bereich angegeben.
Jetzt verwenden wir die folgende Formel, um die SUMME der Punkte für den Monat1 in der Zelle F3 zu erhalten
Verwenden Sie die Formel:
= SUMPRODUKT ( SUMIF ( Ergebnis, B3:E3, Punkte))
Erläuterung:
- Die SUMIF-Funktion gleicht das Ergebnis mit den Werten in B3:E3 ab.
- Dann werden dem Ergebnis entsprechende Punkte an die SUMMENPRODUKT-Funktion zurückgegeben.
- Die SUMPRODUCT-Funktion gibt die SUMME der als Array angegebenen Werte an die Funktion zurück.
Wie Sie hier sehen können, beträgt die Gesamtpunktzahl des Teams im ersten Monat 7.
3 (W) + 0 (L) + 1 (D) + 3 (W) = 7 Punkte
Kopieren Sie die Formel in andere Zellen, wählen Sie die Zellen aus, die die erste Zelle enthalten, in der die Formel bereits angewendet wurde, verwenden Sie die Tastenkombination Strg + D oder verwenden Sie die Option zum Ziehen der Zelle in Excel.
Wie Sie in der obigen Momentaufnahme sehen können, sind die Punkte, die in den 4 verschiedenen Monaten erzielt wurden.
Holen Sie sich die SUMME aller Punkte für 4 Monate.
Das bedeutet, dass das Team in 4 Monaten insgesamt 18 Punkte erzielt hat.
Alternative Methode
Verwenden Sie die SUM- und Array-Methode anstelle der SUMPRODUCT-Funktion.
Verwenden Sie die Formel:
{ = SUM ( SUMIF ( Ergebnis , B3:E3 , Punkte )) }
Geschweifte Klammern ist die Array-Methode, die vorschlägt, dass die Funktion alle zurückgegebenen Werte von der SUMIF-Funktion berechnet.
Hinweis: Nicht Verwenden Sie die geschweiften Klammern-Symbole manuell. Verwenden Sie die Strg + Umschalt + Eingabetaste um die Array-Methode in Excel zu verwenden.
Wie Sie aus der obigen Formel sehen können, können Sie die Gesamtpunktzahl mithilfe der bedingten Nachschlagetabelle erhalten.
Anmerkungen:
- Die Formel funktioniert nur mit Zahlen.
- Die Formel funktioniert nur, wenn keine Duplikate in der Nachschlagetabelle vorhanden sind
- Die Funktion SUMMENPRODUKT betrachtet nicht-numerische Werte als 0.
- Die Funktion SUMMENPRODUKT betrachtet den logischen Wert TRUE als 1 und False als 0.
- Das Argument Array muss die gleiche Länge haben sonst die Funktion
Ich hoffe, dieser Artikel zum Zurückgeben von SUM nur aus Formeln in Excel ist erklärend. Weitere Artikel zu den SUMPRODUCT-Funktionen finden Sie hier. Bitte teilen Sie Ihre Anfrage unten im Kommentarfeld. Wir werden Ihnen helfen.
So verwenden Sie die SUMMENPRODUKT-Funktion in Excel: Gibt die SUMME nach der Multiplikation von Werten in mehreren Arrays in Excel zurück.
SUM, wenn das Datum dazwischen liegt : Gibt die SUMME der Werte zwischen den angegebenen Daten oder dem Zeitraum in Excel zurück.
Summe, wenn das Datum größer als das angegebene Datum ist: Gibt die SUMME der Werte nach dem angegebenen Datum oder Zeitraum in Excel zurück.
2 Möglichkeiten, in Excel nach Monat zu summieren: Gibt die SUMME der Werte innerhalb eines bestimmten Monats in Excel zurück.
So summieren Sie mehrere Spalten mit Bedingung: Gibt die SUMME von Werten über mehrere Spalten mit Bedingung in Excel zurück
populäre Artikel
50 Excel-Shortcut zur Steigerung Ihrer Produktivität
Bearbeiten einer Dropdown-Liste
Absolute Referenz in Excel
Bei bedingter Formatierung
Wenn mit Wildcards
Vlookup nach Datum
Konvertieren Sie Zoll in Fuß und Zoll in Excel 2016
Vor- und Nachname in Excel eintragen
Zählen Sie Zellen, die entweder mit A oder B übereinstimmen