Arte SheetsLernen. Ausprobieren. Verstehen.

Bedingte Summen

SUMIF nach Kategorie: Ausgaben inklusive Rückerstattungen summieren

Summieren Sie Kategorien mit SUMIF, behalten Sie negative Gutschriften bei und prüfen Sie die Teilmengen gegen die Gesamtsumme.

Einkäufe und Rückgaben einheitlich in einem Journal führen

Ein Gemeinschaftsgarten erfasst fünf Buchungen: Seeds 24, Tools 60, Seeds 18, Tools -12 und Compost 35. Der negative Eintrag bei Tools steht für eine Rückgabe, die die Ausgaben für Werkzeuge mindert. Tragen Sie die Kategorien in A2:A6 und die vorzeichenbehafteten Beträge in B2:B6 ein.

Schreiben Sie Tools in D2 als gesuchte Kategorie. Alle Beträge lauten auf dieselbe Währung; Währungsumrechnungen oder Steuern spielen in dieser Übung keine Rolle.

ZeileKategorieBetrag
2Saatgut (Seeds)24
3Werkzeuge (Tools)60
4Saatgut (Seeds)18
5Werkzeuge (Tools)-12
6Kompost (Compost)35

Zeilen nach Kategorie auswählen und deren Beträge addieren

SUMIF vergleicht zuerst A2:A6 mit der Kategorie in D2 und addiert anschließend die zugehörigen Werte aus B2:B6. Für Tools fließen 60 und -12 ein, sodass das Ergebnis 48 lautet.

Die Rückgabe ist kein Ausreißer, den man aus dem Bereich löschen sollte. Ihr Minuszeichen bildet genau die Ausgabenminderung ab. Würden Sie nur positive Beträge filtern, beantworten Sie eine andere Frage: die Bruttoeinkäufe vor Abzug von Retouren.

=SUMIF(A2:A6,D2,B2:B6)

Die Abfrage ändern, ohne die Formel umzuschreiben

Ersetzen Sie Tools in D2 durch Seeds. Die Summe wechselt auf 42 (aus 24 plus 18). Wählen Sie Compost, ergibt sich 35. Eine separate Auswahlzelle lässt sich deutlich leichter kontrollieren als Text in Anführungszeichen direkt in der Formel.

Sie können eine Kategorie zwar auch direkt als "Tools" in die Formel schreiben, dürfen D2 aber nicht in Anführungszeichen setzen, wenn Sie den Zellbezug meinen. "D2" sucht buchstäblich nach einer Kategorie mit dem Namen D2.

Eine Nullsumme beweist nicht, dass keine Buchungen vorlagen

Tragen Sie Water ein – eine Kategorie, die im Journal nicht vorkommt –, liefert SUMIF den Wert 0. Wählen Sie danach wieder Tools und ändern Sie die Rückgabe in B5 auf -60: Auch jetzt ist die Summe 0, obwohl zwei Buchungen gefunden wurden.

Eine Null kann also bedeuten: keine passenden Zeilen, ausschließlich Nullbeträge oder positive und negative Buchungen, die sich gegenseitig aufheben. Prüfen Sie die Einzelzeilen oder zählen Sie die Treffer der Kategorie, bevor Sie jede Nullsumme pauschal als „Keine Einkäufe“ beschriften.

Quellbereiche beim Kopieren einer Auswertung fixieren

Angenommen, in D2, D3 und D4 stehen untereinander Tools, Seeds und Compost. Fixieren Sie in der untenstehenden Formel die beiden Quellbereiche, bevor Sie die Formel neben diesen Kategorien nach unten kopieren. Der Bezug D2 bleibt relativ, damit die nächste Zeile automatisch D3 auswertet.

Halten Sie beide Quellbereiche exakt deckungsgleich: Eine Kategorie in A5 gehört zum Betrag in B5, nicht zu B4. Wenn das Journal wächst, erweitern Sie beide Bereiche um dieselben Zeilen. Das Übungsfeld berechnet Formeln direkt, simuliert aber kein Ziehen am Ausfüllkästchen.

=SUMIF($A$2:$A$6,D2,$B$2:$B$6)

Auf SUMIFS umsteigen, sobald eine zweite Bedingung hinzukommt

Für eine reine Kategoriensumme genügt eine Bedingung. Fragt ein Bericht jedoch nach Tools, die in einem bestimmten Monat gekauft wurden, kommen Datumsbedingungen hinzu. Dafür eignet sich SUMIFS mit einer zusätzlichen Datumsspalte.

Beachten Sie die geänderte Argumentreihenfolge: Bei SUMIF steht der Summenbereich an dritter Stelle, bei SUMIFS dagegen an erster. Hängen Sie also nicht einfach ein S an den Funktionsnamen an, ohne die Argumente neu zu ordnen.

Das Vorzeichen mit einer gezielten Änderung überprüfen

Setzen Sie die Ausgangsdaten zurück und wählen Sie Tools. Ändern Sie B5 von -12 auf 12: Das Ergebnis steigt von 48 auf 72. Die Formel rechnet weiterhin fehlerfrei, doch das geänderte Vorzeichen hat die Rückgabe in einen zusätzlichen Einkauf verwandelt.

Stellen Sie danach -12 wieder her und ordnen Sie A5 der Kategorie Seeds zu. Tools ergibt nun 60, weil die Gutschrift einer anderen Kategorie zugerechnet wird. Diese beiden Tests prüfen sowohl den Kategorieabgleich als auch das Rechnen mit Vorzeichen.

Beispiel ausprobieren

Verwenden Sie die Ausgangswerte oder ändern Sie diese, um Ihr Verständnis zu testen.

Weiterführende Literatur