Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie die SUMIFS-Funktion verwenden, um Daten zu summieren, die leeren oder leeren Zellen in Excel und Google Sheets entsprechen.
Summe wenn leer
Zuerst zeigen wir, wie man Zeilen mit leeren Zellen summiert.
Die SUMIFS-Funktion summiert Daten, die bestimmte Kriterien erfüllen.
Wir können die SUMIFS-Funktion verwenden, um alle zu summieren Spielstände zum Spieler ohne Namen im Beispiel unten.
| 1 | =SUMIFS(C3:C8,B3:B8,"") |
Wir verwenden doppelte Anführungszeichen (""), um eine leere Zelle in Excel darzustellen. Unser Beispiel ignoriert Spieler A, B, C und D und summiert die Werte für unbekannt Spieler.
Leerzeichen als leere Zellen behandeln – mit Hilfsspalte
Sie müssen vorsichtig sein, wenn Sie mit leeren Zellen in Excel interagieren. Zellen können für Sie leer erscheinen, aber Excel behandelt sie nicht als leer. Dies kann auftreten, wenn die Zelle Leerzeichen, Zeilenumbrüche oder andere unsichtbare Zeichen enthält. Dies ist ein häufiges Problem beim Importieren von Daten aus anderen Quellen in Excel.
Wenn wir Zellen, die nur Leerzeichen enthalten, genauso behandeln müssen, als ob sie leer wären, funktioniert die Formel im vorherigen Beispiel nicht. Beachten Sie, dass die SUMIFS-Formel die Zelle B9 unten (” “) nicht als leer ansieht:
Um eine Zelle, die nur Leerzeichen enthält, wie eine leere Zelle zu behandeln, können wir mit der TRIM-Funktion eine Hilfsspalte hinzufügen, um die zusätzlichen Leerzeichen aus dem Wert jeder Zelle zu entfernen:
| 1 | =TRIM (B3) |
Wir wenden die SUMIFS-Funktion auf die Hilfsspalte an und sie berechnet jetzt die Summe genau.
| 1 | =SUMIFS(E3:E9,D3:D9,"") |
Die Hilfsspalte ist einfach zu erstellen und leicht zu lesen, aber vielleicht möchten Sie eine einzige Formel haben, um die Aufgabe zu erfüllen. Dies wird im nächsten Abschnitt behandelt.
Behandeln von Leerzeichen als leere Zellen - ohne Hilfsspalte
Wenn eine Hilfsspalte für Ihre Anforderungen nicht geeignet ist, können Sie die SUMPRODUCT-Funktion in Kombination mit den LEN- und TRIM-Funktionen verwenden, um leere Zeilen zu summieren.
| 1 | =SUMMENPRODUKT(--(LEN(TRIM(B3:B9))=0),D3:D9) |
In diesem Beispiel verwenden wir die SUMPRODUCT-Funktion, um eine komplizierte „Summe if“-Berechnung durchzuführen. Gehen wir die Formel durch.
Dies ist unsere endgültige Formel:
| 1 | =SUMMENPRODUKT(--(LEN(TRIM(B3:B9))=0),D3:D9) |
Zuerst listet die SUMPRODUCT-Funktion das Array von Werten aus den beiden Zellbereichen auf:
| 1 | =SUMMENPRODUKT(--(LEN(TRIM({"A"; "B"; ""; "C"; ""; "XX"; ""}))=0),{25; 10; 15; 5 ; 8; 17; 50) |
Dann entfernt die TRIM-Funktion führende und nachgestellte Leerzeichen aus Spieler Namen:
| 1 | =SUMMENPRODUKT(--(LEN({"A"; "B"; ""; "C"; ""; "XX"; ""})=0),{25; 10; 15; 5; 8; 17; 50) |
Die LEN-Funktion berechnet die Längen der getrimmten Spieler Namen:
| 1 | =SUMMENPRODUKT(--({1; 1; 0; 1; 0; 2; 0}=0),{25; 10; 15; 5; 8; 17; 50) |
Mit dem logischen Test (=0) kann jeder getrimmte Spieler Namen mit 0 Zeichen werden auf TRUE geändert:
| 1 | =SUMMENPRODUKT(--({FALSCH; FALSCH; WAHR; FALSCH; WAHR; FALSCH; WAHR}),{25; 10; 15; 5; 8; 17; 50) |
Als nächstes wandeln die Doppelstriche (-) die TRUE- und FALSE-Werte in 1s und 0s um:
| 1 | =SUMMENPRODUKT({0; 0; 1; 0; 1; 0; 1},{25; 10; 15; 5; 8; 17; 50) |
Die SUMPRODUCT-Funktion multipliziert dann jedes Paar von Einträgen in den Arrays, um ein Array von . zu erzeugen Spielstände nur für Spieler Namen, die leer sind oder nur aus Leerzeichen bestehen:
| 1 | =SUMMENPRODUKT({0; 0; 15; 0; 8; 0; 50) |
Schließlich werden die Zahlen im Array summiert:
| 1 | =73 |
Weitere Details zur Verwendung von Booleschen Anweisungen und dem Befehl „-“ in einer SUMPRODUCT-Funktion finden Sie hier.
Summe, wenn in Google Tabellen leer
Diese Formeln funktionieren in Google Sheets genauso wie in Excel.
