Summieren, wenn Zellen in einer anderen Spalte bestimmten Text enthalten

In diesem Tutorial erfahren Sie, wie Sie Werte summieren, wenn Zellen in einer anderen Spalte einen bestimmten oder teilweisen Text enthalten. Nehmen Sie beispielsweise den folgenden Datenbereich: Um die Gesamtsumme der Produkte zu ermitteln, die den Text „T-Shirt“ enthalten, können Sie sowohl die SUMMEWENN- als auch die SUMMENPRODUKT-Funktion in Excel nutzen.

• Werte summieren, wenn Zelle bestimmten oder teilweisen Text mit der SUMMEWENN-Funktion enthält
• Werte summieren, wenn Zelle bestimmten oder teilweisen Text mit der SUMMENPRODUKT-Funktion enthält

Werte summieren, wenn Zelle bestimmten oder teilweisen Text mit der SUMMEWENN-Funktion enthält

Um Werte zu summieren, wenn eine Zelle in einer anderen Spalte einen bestimmten Text enthält, verwenden Sie die SUMMEWENN-Funktion mit einem Platzhalter (*). Die generische Syntax lautet:

Generische Formel mit hartkodiertem Text:

• range: Der Datenbereich, den Sie anhand der Kriterien auswerten möchten;
• *text*: Das Kriterium, nach dem Sie Werte summieren möchten. Verwenden Sie hier den *-Platzhalter, um eine beliebige Anzahl von Zeichen zu finden. Um alle Elemente zu finden, die einen bestimmten Text enthalten, setzen Sie diesen Text zwischen zwei *-Zeichen. ()Beachten Sie, dass Sie den Text und den Platzhalter in doppelte Anführungszeichen setzen müssen.)
• sum_range: Der Zellbereich mit den zugehörigen numerischen Werten, die summiert werden sollen.

Generische Formel mit Zellbezug:

• range: Der Datenbereich, den Sie anhand der Kriterien auswerten möchten;
• "*"&cell&„*": Das Kriterium, nach dem Sie Werte summieren möchten;
  • *: Der Platzhalter für eine beliebige Anzahl von Zeichen.
  • Zelle: Die Zelle enthält den gesuchten, spezifischen Text.
  • &: Mit diesem Verkettungsoperator (&) verbinden Sie eine Zellreferenz mit Sternchen.
• sum_range: Der Zellbereich mit den übereinstimmenden numerischen Werten, die Sie summieren möchten.

Nachdem Sie die grundlegende Funktionsweise kennen, verwenden Sie eine der folgenden Formeln und drücken Sie anschließend die Eingabetaste, um das Ergebnis zu erhalten:

Hinweis: Die SUMMEWENN-Funktion unterscheidet nicht zwischen Groß- und Kleinschreibung.

Werte summieren, wenn Zelle bestimmten oder teilweisen Text mit der SUMMENPRODUKT-Funktion enthält

Auch die SUMMENPRODUKT-Funktion unterstützt Sie dabei, diese Aufgabe ohne Platzhalter zu erledigen. Integrieren Sie hierzu die Funktionen ISTZAHL und SUCHEN innerhalb einer SUMMENPRODUKT-Funktion. Die generische Syntax lautet:

• sum_range: Der Zellbereich mit den übereinstimmenden numerischen Werten, die Sie summieren möchten;
• criteria: Das Kriterium, nach dem Sie Werte summieren möchten – es kann eine Zellreferenz oder ein von Ihnen definierter spezifischer Text sein.
• range: Der Datenbereich, den Sie anhand der Kriterien auswerten möchten;

Geben Sie eine der folgenden Formeln in eine leere Zelle ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten:

Erläuterung dieser Formel:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(„T-Shirt",$A$2:$A$12))))

• SUCHEN(„T-Shirt";$A$2:$A$12): Die SUCHEN-Funktion gibt die Position des Textes „T-Shirt“ innerhalb des Bereichs A2:A12 zurück und liefert so ein Array wie folgt: {5;#WERT!;#WERT!;7;#WERT!;7;#WERT!;#WERT!;#WERT!;#WERT!;7}.
• ISTZAHL(SUCHEN(„T-Shirt";$A$2:$A$12))= ISTZAHL({5;#WERT!;#WERT!;7;#WERT!;7;#WERT!;#WERT!;#WERT!;#WERT!;7}): Die ISTZAHL-Funktion prüft auf numerische Werte und liefert ein neues Array zurück: {WAHR;FALSCH;FALSCH;WAHR;FALSCH;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;WAHR}.
• $B$2:$B$12*(ISTZAHL(SUCHEN(„T-Shirt";$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{WAHR;FALSCH;FALSCH;WAHR;FALSCH;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;WAHR}: Um diese beiden Arrays zu multiplizieren, wandelt die mathematische Operation die Werte WAHR und FALSCH automatisch in 1 und 0 um. Die Multiplikation der beiden Arrays ergibt somit: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1;0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
• SUMMENPRODUKT($B$2:$B$12*(ISTZAHL(SUCHEN(„T-Shirt";$A$2:$A$12)))) =SUMMENPRODUKT({347;0;0;430;0;379;0;0;0;0;449}): Am Ende summiert die SUMMENPRODUKT-Funktion alle Werte im Array – und liefert so das Endergebnis: 1605.

Verwendete verwandte Funktion:

Weitere Artikel: