SUMMEWENN für Datum und ungleiche Werte in Excel
Schritt-für-Schritt-Anleitung
Um die Summe über eine Spalte in Excel zu berechnen, während du sowohl auf ungleiche Werte in einer anderen Spalte als auch auf spezifische Datumsbedingungen achtest, kannst du die folgenden Schritte befolgen:
-
Datenstruktur: Stelle sicher, dass deine Daten in drei Spalten organisiert sind:
- Spalte A: Enthält Werte "x" oder leer.
- Spalte B: Enthält Datumswerte im Format dd:mm:yyyy.
- Spalte C: Enthält die zu summierenden Zahlen.
-
Kriterien festlegen: Lege deine Filterkriterien in Zelle F1 (Jahr) und F2 (Monat) fest.
-
Formel eingeben: Nutze die SUMPRODUCT-Funktion für die Berechnung:
=SUMPRODUCT(C2:C5*(A2:A5<>"x")*(MONTH(B2:B5)=F2)*(YEAR(B2:B5)=F1))
Diese Formel summiert die Werte in Spalte C, wenn in Spalte A kein "x" steht und das Datum in Spalte B dem angegebenen Monat und Jahr entspricht.
Häufige Fehler und Lösungen
-
Fehler bei der Verwendung von SUMIFS: Wenn du SUMIFS verwendest, stelle sicher, dass du die Kriterien korrekt angibst. Die Verwendung von Funktionen wie MONTH und YEAR direkt in den Kriterien funktioniert nicht.
-
Fehler bei der Datumsformatierung: Achte darauf, dass die Datumsangaben in Spalte B korrekt formatiert sind. Ein falsches Format kann dazu führen, dass die Berechnungen nicht wie gewünscht funktionieren.
Alternative Methoden
Wenn du die SUMPRODUCT-Funktion vermeiden möchtest, kannst du auch folgende Alternativen ausprobieren:
-
SUMMEWENN mit Array-Formeln: Eine Array-Formel könnte auch funktionieren, allerdings benötigst du dafür die Eingabe mit STRG+SHIFT+ENTER. Zum Beispiel:
{=SUMMEWENN(A2:A5;"<>x";C2:C5*(MONAT(B2:B5)=F2)*(JAHR(B2:B5)=F1))}
-
Pivot-Tabellen: Eine Pivot-Tabelle kann ebenfalls verwendet werden, um die Summe basierend auf verschiedenen Kriterien zu aggregieren, allerdings ist dies weniger dynamisch als die oben genannten Methoden.
Praktische Beispiele
Angenommen, du hast folgende Daten:
| A |
B |
C |
| x |
01:09:2019 |
100 |
|
15:09:2019 |
200 |
| x |
20:09:2019 |
150 |
|
30:09:2019 |
300 |
Wenn in Zelle F1 das Jahr 2019 und in Zelle F2 der Monat 9 steht, würde die SUMPRODUCT-Formel =SUMPRODUCT(C2:C5*(A2:A5<>"x")*(MONTH(B2:B5)=F2)*(YEAR(B2:B5)=F1)) eine Summe von 500 zurückgeben, weil nur die Zeilen ohne "x" in Spalte A berücksichtigt werden.
Tipps für Profis
-
Verwende benannte Bereiche: Statt direkte Zellbezüge zu verwenden, kannst du die relevanten Bereiche benennen, um die Lesbarkeit deiner Formeln zu erhöhen.
-
Überprüfe die Leistung: Bei großen Datenmengen kann die Verwendung von SUMPRODUCT langsamer sein. Teste die Leistung deiner Formeln und erwäge, auf SUMIFS umzusteigen, wo möglich.
-
Zusätzliche Kriterien: Du kannst weitere Bedingungen in deine SUMPRODUCT-Formel einfügen, um noch spezifischere Berechnungen durchzuführen, z.B. für andere Werte oder Datumsbereiche.
FAQ: Häufige Fragen
1. Wie funktioniert die Bedingung ungleich in Excel?
Die Bedingung ungleich wird in Excel mit dem Zeichen <> dargestellt. Zum Beispiel: A2<>"x" bedeutet, dass die Zelle A2 nicht "x" sein darf.
2. Kann ich die Funktion auch für mehrere Kriterien verwenden?
Ja, die SUMPRODUCT-Funktion ist sehr flexibel und ermöglicht es dir, mehrere Bedingungen einfach hinzuzufügen. Du musst nur die entsprechenden Vergleichsbedingungen in die Funktion einfügen.