Excel – SUMME aus Kreuztabelle – Suchkriterien in Spalte und Zeile
Im Dueling Excel Podcast # 123 haben Mr Excel und Excelisfun zwei wunderbare Möglichkeiten demonstriert, wie ausgehend aus einer Kreuztabelle bedingte Summen gebildet werden können. Aufgrund der Bedingungen, konnte eine normale SUMMEWENN-Funktion nicht ohne Anpassungen genommen werden.
In den Zellen I1
und H2
befinden sich die Suchkriterien für das Datum und die Region. Nur bei passendem Datum soll die Summe der Werte gebildet werden.
Die Matrixfunktion in Zelle I2
muss mit den Tasten STRG + SHIFT + ENTER bestätigt werden:
{=SUMME(WENN((A2:A8=H2)*(B1:F1=I1);B2:F8;0))}
A2:A8=H2
sorgt für einen paarweisen Vergleich der Regionen in Spalte A mit dem Suchkriterium in Zelle H2
. Immer wenn beide Werte identisch sind, kommt hier ein WAHR
heraus. Bei der Multiplikation ergibt WAHR
eine 1
.
B1:F1=I1
vergleicht paarweise das Datum in der ersten Zeile mit dem Suchkriterium in Zelle I1
.
Wenn nun beide Kriterien für jeweils eine Kombination aus dem Bereich A2:A8
und B1:F1
zutrifft, wird hier WAHR * WAHR
gerechnet und das Ergebnis 1
bzw. WAHR
resultiert. In diesem Fall wird der Wert der Matrix aus B2:F8
zurückgegeben. Andernfalls eine Null. Hierfür sorgt die WENN-Funktion.
Die Matrixformel sorgt dafür, dass erst alle Zellen des Bereichs einzeln ermittelt werden und dann die passenden Werte für die Funktion SUMME übrig bleiben.
Zur Verdeutlichung, welche Werte identifiziert werden, multipliziere ich die Spalte K2:K8
mit der Zeile L1:P1
.
Die Formel =A2=$H$2
aus Zelle K2
wurde nach unten kopiert. Die Formel =B1=$I$1
aus Zelle L1
nach rechts.
Im Bereich L2:P8
liegt die Matrixfunktion {=K2:K8*L1:P1}
.
Es können auch mehrere Spalten in Frage kommen. Im folgenden Beispiel wird der Wochentag als Spaltenkriterium herangezogen.
Die Formel aus Zelle B12
prüft, ob der Wochentag in der zweiten Zeile mit dem Wochentag in Zelle B11
übereinstimmt:
{=SUMME(WENN((A12=A3:A9)*(WOCHENTAG(B2:M2;2)=VERGLEICH(B11;O2:O8;0));B3:M9;0))}
Link zur Übungsdatei:
E363_Matrix_Auswahl
Link zum YouTube-Video: