Generische Formel
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Zusammenfassung
Um Werte in übereinstimmenden Spalten und Zeilen zu summieren, können Sie die Funktion SUMPRODUCT verwenden. Im gezeigten Beispiel lautet die Formel in J6:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
Dabei werden Daten (C5: G14), Tage (B5: B14) und Codes (C4: G4) als Bereiche bezeichnet.
Erläuterung
Die SUMPRODUCT-Funktion kann Arrays nativ verarbeiten, ohne dass eine Control Shift-Eingabe erforderlich ist.
In diesem Fall multiplizieren wir alle Werte in den benannten Bereichsdaten mit zwei Ausdrücken, die nicht interessierende Werte herausfiltern. Der erste Ausdruck wendet einen Filter an, der auf Codes basiert:
(codes=J4)
Da J4 "A002" enthält, erstellt der Ausdruck ein Array von TRUE FALSE-Werten wie folgt:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Der zweite Ausdruck filtert am Tag:
(days=J5)
Da J4 "Mi" enthält, erstellt der Ausdruck ein Array von TRUE FALSE-Werten wie folgt:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
In Excel werden TRUE FALSE-Werte von jeder mathematischen Operation automatisch auf 1 und 0 gezwungen, sodass die Multiplikationsoperation die obigen Arrays auf Einsen und Nullen zwingt und ein 2D-Array mit denselben Abmessungen wie die Originaldaten erstellt. Der Prozess kann wie folgt dargestellt werden:
Schließlich gibt SUMPRODUCT die Summe aller Elemente im endgültigen Array 9 zurück.
Zählen statt summieren
Wenn Sie übereinstimmende Werte zählen möchten, anstatt zu summieren, können Sie die Formel verkürzen auf:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Beachten Sie, dass diese Anzahl leere Zellen enthält.
Anmerkungen
- Obwohl das Beispiel nur eine übereinstimmende Spalte zeigt, summiert diese Formel mehrere übereinstimmende Spalten korrekt.
- Wenn Sie nur Spalten (keine Zeilen) abgleichen müssen, können Sie eine Formel wie diese verwenden.
- Um nur Zeilen abzugleichen, können Sie die COUNTIFS-Funktion verwenden.
