In Excel eine Summe auszurechnen, ist nicht sonderlich kompliziert. Wenn jedoch aus einer bunt gemischten Tabelle die Gesamtsummen der Rechnungen bestimmter Kunden oder ähnliches herausgefunden werden sollen, hört sich das schon schwieriger an.
In dieser Anleitung erfahren Sie, wie man die beschriebene Problematik mit Hilfe der Summewenn Funktion lösen kann.
Prinzipieller Aufbau der Summewenn Funktion
=Summewenn(Bereich in dem die Werte gesucht werden sollen z.B. A2:A200; Wert der gesucht werden soll z.B. 12; Bereich aus dem Werte zusammengerechnet werden sollen z.B. B2:B200)
Die einzelnen Teile der Formel werden mit einem Semikolon getrennt.
Die oben beschriebene Formel würde korrekt so aussehen: =Summewenn(A2:A200;12;B2:B200)
Beispielaufgabe 1: Anwendung der Summewenn Funktion
In diesem Beispiel sollen die Rechnungsbeträge nicht wie auf dem Bildausschnitt zu sehen gewöhnlich summiert werden, sondern die Rechnungsbeträge der einzelnen Kunden und die verschiedenen Leistungsarten sollen separat zusammengerechnet werden, ohne die Tabelle zu verändern oder die Werte einzeln addieren zu müssen. Alle Werte einzeln zu addieren wäre bei einer Tabelle mit 20000 Einträgen sicherlich etwas zu mühselig.
Summierung der Rechnungen nach Kundennummer
Wie aus der Liste zu erkennen, gibt es in diesem Beispiel 6 verschiedene Kunden. Diese wurden alle einzeln noch einmal zusammen in den Spalten E + F aufgeführt.
Die Formel „=Summewenn(A:A;F2;C:C)“ summiert alle Kundenrechnungen der Kundennummer 1.
In Worten bedeutet die Formel soviel wie: Suche in der Spalte „A“ nach dem Inhalt aus der Zelle „F2“, wenn du den Inhalt der Zelle „F2“ in der Spalte „A“ findest, rechne die Werte der Spalte „C“ miteinander zusammen, die sich in den zutreffenden Zeilen befinden.
Man muss nicht zwingend ganze Spalten auswählen, aber wenn die Formelbeinhaltende Zelle in andere Zellen übertragen werden soll, wie in diesem Fall, müssen Sie an die absolute Adressierung denken, damit die Ergebnisse nicht verfälscht werden.
So wie die Formel in diesem Beispiel aufgebaut ist, kann Sie dank der relativen Adressierung für Spalte „F“ einfach nach unten kopiert und eingefügt werden, die Zeilenangabe für Spalte „F“ führt sich automatisch richtig fort.
Entsprechend sieht die Formel für die Kundennummer 2 wie folgt aus: „=Summewenn(A:A;F4;C:C)“, so setzt sich das für die weiteren Kunden beim kopieren fort.
Summierung der Rechnungen nach Leistungsart
Jetzt wurde eine weitere Spalte hinzugefügt, hier werden ebenfalls mit der Summewenn Formel bestimmte Beträge zusammengerechnet. Allerdings handelt es sich diesmal nicht um den Kunden, sondern um die erbrachten Leistungen.
Wie man sieht, ist es vom Prinzip her genau dasselbe, nur das dieses Mal Wörter statt Zahlen gesucht werden.
Wie im vorherigen Beispiel können die Formeln mit kopieren und einfügen übertragen werden. Falls Sie vorhaben, nach Wörtern zu suchen, ohne einen Zellenbezug zu verwenden, denken Sie daran einen String für das gesuchte Wort zu setzten, also das Wort in Anführungszeichen zu setzen.
Als Beispiel: Die Formel könnte auch anstatt „=Summewenn(B:B;H2;C:C)“ so lauten, „=Summewenn(B:B;“Reparatur“;C:C)“, so könnten Sie die Formel allerdings nicht mehr nach unten kopieren.
Beispielaufgabe 2: Anwendung der Summewenn Funktion mit einer Wenn-Verschachtelung
In diesem Beispiel wird eine Summewenn Funktion mit einer normalen Wenn-Funktion verschachtelt. Für diese Aufgabe muss die Tabelle zuerst nach der Kundennummer sortiert werden.
In dieser Aufgabe sollen auf dem schnellsten Wege die Rechnungssummen der einzelnen Kunden addiert werden. Bei einer Liste mit 500 Kunden wäre diese Variante der anderen gegenüber sicherlich erheblich effektiver. Später können durch Filterfunktionen oder durch weitere Formeln die Ergebnisse noch anschaulicher dargestellt werden, als auf dieser Grafik.
Die Formel lautet: =Wenn(J3<>J4;Summewenn(J:J;J3;L:L);““)
In Worten bedeutet die Formel in etwa folgendes: Wenn der Inhalt der Zelle „J3“ ungleich dem Inhalt der Zelle „J4“ ist, suche in der Spalte „J“ nach dem Inhalt aus der Zelle „J3“, wenn du den Inhalt der Zelle „J3“ in der Spalte „J“ findest, rechne die Werte der Spalte „L“ miteinander zusammen, die sich in den zutreffenden Zeilen befinden, sonst lasse die Zelle leer.
Wenn man die Formel herunterzieht, erreicht man das Ergebnis, wie auf dem Bildausschnitt zu sehen. Mit dieser Formel wird immer die Gesamtrechnungssumme des Kunden in der letzten Zeile eingetragen, in der die Kundennummer dieses Kunden zu finden ist, egal wie viele Rechnungen ein Kunde bekommen hat. Sehr wichtig ist bei diesem Vorgehen, die Tabelle wie oben beschrieben nach dem zu suchenden Wert (hier die Kundennummer) zu sortieren.
Kommentare: (2)
LG Lara
Gruß
Sven