Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie Daten zu bestimmten Wochennummern in Excel und Google Sheets summieren.
Summe wenn nach Wochennummer
Um nach Wochennummer zu „summieren“, verwenden wir die SUMIFS-Funktion. Aber zuerst müssen wir eine Hilfsspalte hinzufügen, die die WEEKNUM-Funktion enthält.
Die Wochennummer Helferspalte wird mit der WEEKNUM-Funktion berechnet:
| 1 | =WOCHENNUMMER(B3,1) |
Als nächstes verwenden wir die SUMIFS-Funktion, um alle zu summieren Der Umsatz die in einem bestimmten Wochennummer.
| 1 | =SUMIFS(D3:D9,C3:C9,F3) |
Sum If by Week Number - Ohne Hilfsspalte
Die Hilfsspaltenmethode ist einfach zu befolgen, aber Sie können die Berechnung auch in einer einzigen Formel replizieren, indem Sie die SUMPRODUCT-Funktion in Kombination mit der WEEKNUM-Funktion verwenden, um die Gesamtzahl der Verkäufe von Wochennummer.
| 1 | =SUMMENPRODUKT(--(WOCHENNUMMER(B3:B9+0,1)=E3),C3:C9) |
In diesem Beispiel können wir die SUMPRODUCT-Funktion verwenden, um komplizierte „Summen-wenn“-Berechnungen durchzuführen. Gehen wir das obige Beispiel durch.
Dies ist unsere endgültige Formel:
| 1 | =SUMMENPRODUKT(--(WOCHENNUMMER(B3:B9+0,1)=E3),C3:C9) |
Zuerst listet die SUMPRODUCT-Funktion das Array von Werten aus den Zellbereichen auf:
| 1 | =(--(({"1/3/2020"; "1/6/2020"; "1/9/2020"; "1/12/2020"; "1/15/2020"; "1/18/2020"; "1/21/2020"}+0,1)=1), {4; 9; 1; 7; 6; 2; 5}) |
Dann berechnet die WEEKNUM-Funktion die Wochennummer von jedem der Verkaufstermine.
Die WEEKNUM-Funktion ist nicht dafür ausgelegt, mit Array-Werten zu arbeiten, daher müssen wir Null ("+0") hinzufügen, damit WEEKNUM die Werte richtig verarbeiten kann.
| 1 | =SUMMENPRODUKT(--({1; 2; 2; 3; 3; 3; 4}=1), {4; 9; 1; 7; 6; 2; 5}) |
Wochennummer Werte gleich 1 werden in WAHR-Werte geändert.
| 1 | =SUMMENPRODUKT(--({WAHR; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH}), {4; 9; 1; 7; 6; 2; 5}) |
Als nächstes wandeln die Doppelstriche (-) die TRUE- und FALSE-Werte in 1s und 0s um:
| 1 | =SUMMENPRODUKT({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
Die SUMPRODUCT-Funktion multipliziert dann jedes Paar von Einträgen in den Arrays, um ein Array von . zu erzeugen Anzahl der Verkäufe die haben a Wochennummer von 1:
| 1 | =SUMMENPRODUKT({4; 0; 0; 0; 0; 0; 0}) |
Schließlich werden die Zahlen im Array summiert:
| 1 | =4 |
Diese Formel wird dann für die anderen möglichen Werte von wiederholt Wochennummer.
Weitere Details zur Verwendung von Booleschen Anweisungen und dem Befehl „-“ in einer SUMPRODUCT-Funktion finden Sie hier.
Zellenreferenzen sperren
Um unsere Formeln leichter lesbar zu machen, haben wir die Formeln ohne gesperrte Zellbezüge gezeigt:
| 1 | =SUMMENPRODUKT(--(WOCHENNUMMER(B3:B9+0,1)=E3),C3:C9) |
Diese Formeln funktionieren jedoch nicht richtig, wenn Sie sie an anderer Stelle in Ihrer Datei kopieren und einfügen. Stattdessen sollten Sie gesperrte Zellbezüge wie folgt verwenden:
| 1 | =SUMMENPRODUKT(--(WOCHENNUMMER($B$3:$B$9+0,1)=E3),$C$3:$C$9) |
Lesen Sie unseren Artikel zum Sperren von Zellreferenzen, um mehr zu erfahren.
Summe wenn nach Wochennummer in Google Tabellen
Diese Formeln funktionieren in Google Sheets genauso wie in Excel.
Die WEEKNUM-Funktion ist jedoch in Google Sheets flexibler als in Excel und akzeptiert Array-Ein- und -Ausgaben. Daher ist die Operation {Array}+0 in der Formel WEEKNUM(B3:B9+0,1) nicht erforderlich.
Die vollständige SUMPRODUCT-Formel kann in Google Tabellen wie folgt geschrieben werden:
| 1 | =SUMMENPRODUKT(--(WOCHENNUMMER($B$3:$B$9+0,1)=E3),$C$3:$C$9) |
