Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie die SUMIFS-Funktion verwenden, um Daten zu summieren, die sich auf nicht leere oder nicht leere Zellen in Excel und Google Sheets beziehen.
Summe, wenn nicht leer
Zuerst werden wir demonstrieren, wie Daten in Bezug auf nicht leere Zellen summiert werden.
Wir können die SUMIFS-Funktion verwenden, um alle zu summieren Spielstände zum Spieler mit nicht leeren Namen.
| 1 | =SUMIFS(C3:C8,B3:B8,"") |
Um Zeilen mit nicht leeren Zellen zu summieren, schließen wir aus Spielstände mit fehlenden Spieler Namen. Wir verwenden das Kriterium „ungleich leer“ („“) innerhalb der SUMIFS-Funktion.
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:
| 1 | =SUMIFS(D3:D9,B3:B9,"") |
Um eine Zelle, die nur Leerzeichen enthält, wie eine leere Zelle zu behandeln, können wir mithilfe der LEN- und TRIM-Funktionen eine Hilfsspalte hinzufügen Spieler mit Namen.
Die TRIM-Funktion entfernt die zusätzlichen Leerzeichen vom Anfang und Ende jedes Zellenwerts und die LEN-Funktion zählt dann die Anzahl der verbleibenden Zeichen. Wenn das Ergebnis der LEN-Funktion 0 ist, dann ist die Spieler Name muss leer sein oder nur aus Leerzeichen bestehen:
| 1 | =LEN(TRIM(B3)) |
Wir wenden die SUMIFS-Funktion auf die Hilfsspalte an (Summieren wenn größer als 0), und sie berechnet die Summe jetzt genau.
| 1 | =SUMIFS(E3:E9,D3:D9,">0") |
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 Zellen, die nur Leerzeichen enthalten, genauso behandelt werden müssen, als ob sie leer wären, die Verwendung einer Hilfsspalte jedoch nicht geeignet ist, können wir die SUMPRODUCT-Funktion in Kombination mit den LEN- und TRIM-Funktionen verwenden, um Daten in Bezug auf Zellen zu summieren mit nicht-Leerzeichen Spieler Namen:
| 1 | =SUMMENPRODUKT(--(LEN(TRIM(B3:B9))>0),D3:D9) |
In diesem Beispiel verwenden wir die SUMPRODUCT-Funktion, um komplizierte „Summe if“-Berechnungen 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) werden alle getrimmten Spieler Namen mit mehr als 0 Zeichen werden auf TRUE geändert:
| 1 | =SUMMENPRODUKT(--({WAHR; WAHR; FALSCH; WAHR; FALSCH; WAHR; FALSCH}),{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({1; 1; 0; 1; 0; 1; 0},{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 nicht leer sind oder nicht nur aus Leerzeichen bestehen:
| 1 | =SUMMENPRODUKT({25; 10; 0; 5; 0; 17; 0) |
Schließlich werden die Zahlen im Array summiert
| 1 | =57 |
Weitere Details zur Verwendung von Booleschen Anweisungen und dem Befehl „-“ in einer SUMPRODUCT-Funktion finden Sie hier
Summe, wenn in Google Tabellen nicht leer
Diese Formeln funktionieren in Google Sheets genauso wie in Excel.
