EXCEL: SummeWennS() für Dummies

Die Excel-Funk­tio­nen SUMMEWENN() und SUMMEWENNS() erlau­ben die Addi­ti­on von Wer­ten einer Tabel­le in Abhän­gig­keit von selbst aus­ge­wähl­ten Bedin­gun­gen. In einer Umsatz­ta­bel­le kön­nen Sie bei­spiels­wei­se die Wer­te nach Kun­den, Model­len oder Arti­keln sum­mie­ren. Las­sen Sie sich von den kom­pli­ziert aus­se­hen­den For­meln nicht abschre­cken. Wir erklä­ren Sie Ihnen aus­führ­lich anhand von kon­kre­ten Bei­spie­len und einer Bei­spiel­da­tei zum Download.

Die Excel-Funk­tio­nen SUMMEWENN() und SUMMEWENNS() erlau­ben die Addi­ti­on von Wer­ten einer Tabel­le in Abhän­gig­keit von Bedin­gun­gen. In Lis­ten oder Tabel­len kön­nen Wer­te abhän­gig von selbst aus­ge­wähl­ten Spal­ten addiert werden.

Wel­che Gerich­te kom­men in Fra­ge, wenn Sie nur 7,50 € dabei haben?
(Alle Gerich­te in Spal­te 2, eini­ge Gerich­te in Spal­te 3)

Natür­lich könn­te man die Lis­te oder Tabel­le auch ein­fach fil­tern und das gesuch­te Ergeb­nis mit Hil­fe der Funk­ti­on TEILERGEBNIS() errech­nen. Das hat aber den Nach­teil, dass man immer nur für eine Kon­stel­la­ti­on ein Ergeb­nis ange­zeigt bekommt.

WENNS ein biss­chen ele­gan­ter sein soll, kom­men die beding­ten Sum­men zum Einsatz:

1.   Syntax und Funktionsweise

Vor­ab ein wich­ti­ger Hinweis:

Las­sen Sie sich bit­te von den For­meln in Excel nicht abschre­cken! Sie wer­den das Mus­ter und die Metho­de bald ver­in­ner­li­chen. Sie brau­chen es auch „nur“ ver­ste­hen, aber nicht aus­wen­dig können. 

Excel hilft Ihnen! Wenn Sie die jewei­li­ge For­mel ein­tip­pen, zeigt Ihnen Excel die benö­tig­ten Anga­ben der For­mel an, sobald Sie „(“ gedrückt haben. (Das gilt übri­gens grund­sätz­lich in Excel.)

Pro­bie­ren Sie es ein­fach ein­mal aus! Es sieht kom­pli­zier­ter aus als es ist!

Nach­fol­gend fin­den Sie hier Erklä­run­gen zu den Formeln.

1.1.    Die Funktion SUMMEWENN()

Die Funk­ti­on SUMMEWENN() erwar­tet die­se Ein­ga­ben:
=SUMMEWENN(Bereich; Such­kri­te­ri­en; [Summe_Bereich])

  • Bereich:   „Wo?“
    Such­be­reich in Ihrer Tabel­le, der nach den Such­kri­te­ri­en durch­sucht wer­den soll (meis­tens eine Spalte)
  • Such­kri­te­ri­um: „Was?“
    Das Such­kri­te­ri­um ist der Wert oder der logi­sche Aus­druck, nach dem der „Bereich“ durch­sucht wer­den soll.
    Sie kön­nen nach Zah­len, Tex­ten oder auch nach Datums­wer­ten suchen. (z.B. 4711, „Mei­er“; 31.12.2021)
    Ver­glei­che mit logi­schen Ope­ra­to­ren sind eben­falls mög­lich. Sie kön­nen z.B. alle Umsät­ze addie­ren, die grö­ßer als 1.000 € sind.
    Oder Sie addie­ren alle Wer­te, die klei­ner als der Wert in der Zel­le A1 ist.
    Tex­te und logi­sche Ope­ra­to­ren müs­sen in der For­mel in Anfüh­rungs­stri­chen ange­ge­ben wer­den (z.B. „Auto“, „>1000“, „<=“&A1).
  • [Summe_Bereich]: Wel­che Wer­te?
    Der [Summe_Bereich] ist der Zell­be­reich Ihrer Tabel­le, aus dem bei Über­ein­stim­mung von Such­kri­te­ri­um und Bereich die Wer­te sum­miert wer­den sollen.

Das bedeu­tet: für alle Zei­len, in denen das Such­kri­te­ri­um im Bereich gefun­den wird, wer­den die zuge­hö­ri­gen Wer­te in [Summe_Bereich] addiert.

Das ist schwie­ri­ger zu beschrei­ben als auszuprobieren.

Im fol­gen­den Bei­spiel wer­den die Umsatz­da­ten nach ver­schie­de­nen Kri­te­ri­en addiert. Es wer­den bei­spiels­wei­se alle Model­le auf­ad­diert, oder alle Arti­kel. Auch eine Tren­nung der Wer­te in „> grö­ßer als“ oder „< klei­ner als“ ein Kri­te­ri­um ist mög­lich (sie­he Beispieldatei).

Und so sehen die For­meln dann in der Pra­xis aus:

Ähn­lich kön­nen Sie auch die Sum­men je Arti­kel ermit­teln. Sie müs­sen dann ledig­lich den „Bereich“ in die Spal­te C legen. (vgl. Beispieldatei)

Da immer nur ein Such­kri­te­ri­um ange­ge­ben wer­den kann, reicht die SUMMEWENN() Funk­ti­on für die Aus­wer­tung grö­ße­rer Daten­be­stän­de oft nicht aus. Man kann eben nur ein Kri­te­ri­um auswählen.

Wenn Sie bei­spiels­wei­se die Sum­me der Modell-Arti­kel-Vari­an­ten benö­ti­gen, kommt des­halb die Funk­ti­on SUMMEWENNS() zum Einsatz.

1.2.    Die Funktion SUMMEWENNS()

Anders als mit der Funk­ti­on SUMMEWENN() kann man mit der Funk­ti­on SUMMEWENNS() in einem Daten­be­stand Kri­te­ri­en auf meh­re­re Spal­ten festlegen.

Die Syn­tax der Funk­ti­on SUMMEWENNS() gestal­tet sich wie folgt:

=SUMMEWENNS(Summe_Bereich;Kriterien_Bereich1;Kriterium1; [Kriterien_Bereich2];[Kriterium2];)

  • Summe_Bereich
    [Summe_Bereich] ist der Zell­be­reich Ihrer Tabel­le, indem bei Über­ein­stim­mung von Such­kri­te­ri­en und [Kriterien_Bereich] die Wer­te sum­miert wer­den sollen.
  • Kriterien_Bereich1
    Mit Kriterien_Bereich1 ist der Zell­be­reich gemeint, in dem nach den Kriterium1 gefil­tert wer­den soll.
  • Kriterium1
    Kriterium1 ist der Wert, nach dem der Kriterien_Bereich1 durch­sucht wer­den soll. Hier gilt das glei­che wie bei SUMMEWENN(): Es kann nach Zah­len, Tex­ten oder auch nach Datums­wer­ten gesucht wer­den. Ver­glei­che mit logi­schen Ope­ra­to­ren sind eben­falls mög­lich. Tex­te und logi­sche Ope­ra­to­ren müs­sen dann aber in Anfüh­rungs­stri­chen ange­ge­ben wer­den (z.B. „Auto“, „>10“, „<=“&A1), wenn auf einen Zell­be­zug ver­zich­tet wird.
  • [Kriterien_Bereich2] und [Kriterium2] etc. sind wei­te­re optio­na­le Fil­ter. Sie funk­tio­nie­ren ana­log zum Kriterien_Bereich1 und dem Kriterium1.

1.3.    Der Unterschied von SUMMEWENN() und SUMMEWENNS()

Haben Sie es gemerkt? Die Funk­tio­nen fra­gen die Anga­ben in den For­meln in einer ande­ren Rei­hen­fol­ge ab. An einem Bei­spiel lässt sich der Unter­schied zwi­schen den Excel Funk­tio­nen SUMMEWENN() und SUMMEWENNS() am ein­fachs­ten ver­deut­li­chen.
Bei­de Funk­tio­nen lie­fern das glei­che Ergeb­nis, die Rei­hen­fol­gen der Argu­men­te in der Syn­tax unter­schei­den sich jedoch:

Sum­me­wenn() Sum­me­wenn()

SUMMEWENN() Die Syn­tax der Funk­ti­on SUMMEWENN() beginnt mit dem Kriterienbereich…SUMMEWENNS() … wäh­rend die Syn­tax der Funk­ti­on SUMMEWENNS() mit dem Ergeb­nis­be­reich beginnt.
SUMMEWENN() vs SUMMEWENNS()

2.   SUMMEWENNS-Formel / #WERT!-Fehler

Häu­fi­ge Feh­ler erge­ben sich dar­aus, dass die Argu­men­te „Kriterien_Bereich“ und „Summe_Bereich“ eine unter­schied­li­che Anzahl von Zei­len und Spal­ten auf­wei­sen. Stim­men Sie dann die Berei­che auf­ein­an­der ab.

Wenn die For­mel auf eine Zel­le oder einen Bereich in einer geschlos­se­nen Arbeits­map­pe ver­weist, ergibt sich eben­falls ein #Wert!-Fehler. Öff­nen Sie dann die in der For­mel ange­ge­be­ne Arbeits­map­pe und drü­cken Sie F9, um die For­meln zu aktualisieren.

3.   Zusammenfassung, Einschränkungen und Fazit

Die Rei­hen­fol­ge der Argu­men­te in der Syn­tax unter­schei­det sich bei bei­den For­meln. Des­halb kommt es leicht zu Ver­wechs­lun­gen beim Ein­satz bei­der Funk­tio­nen. Wenn Sie statt­des­sen immer die Funk­ti­on SUMMEWENNS() ver­wen­den, haben Sie alle Optio­nen. Sie sind damit dann recht fle­xi­bel auf­ge­stellt und kön­nen Ihre For­mel bei Bedarf erweitern.

Lei­der funk­tio­nie­ren bei­de Funk­tio­nen nicht, wenn die Daten in einer ande­ren Arbeits­map­pe gespei­chert sind und die­se nicht geöff­net ist. Damit sind bei­de Funk­tio­nen z.B. im Con­trol­ling für einen effi­zi­en­ten Ein­satz nur bedingt geeig­net. Die­se Beschrän­kung lässt sich mit der Excel-Funk­ti­on SUMMENPRODUKT() umge­hen. Sie funk­tio­niert auch bei der Abfra­ge von Wer­ten aus geschlos­se­nen Mappen.

Quellen und Empfehlungen* zur SUMMEWENNS()

4.   Ressourcen

Hier fin­den Sie wei­ter­füh­ren­de Infor­ma­tio­nen, die Bei­spiel­da­tei zum Down­load und exter­ne Links:

Excel-Tipps (nicht nur) für Controller

kos­ten­frei­es eBook mit Tipps zu For­ma­tie­run­gen und For­meln – hier im Blog

Latest posts by Win­fried Eitel (see all)

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht.