Ein Problem, das in Excel häufig auftritt, ist das Zählen oder Summieren basierend auf mehreren ODER-Bedingungen. Möglicherweise müssen Sie beispielsweise Daten analysieren und Bestellungen in Seattle oder Denver für Artikel zählen, die rot, blau oder grün sind. Dies kann überraschend schwierig sein, daher ist es natürlich eine gute Herausforderung!
Die Herausforderung
Die folgenden Daten stellen Bestellungen dar, eine Bestellung pro Zeile. Es gibt drei verschiedene Herausforderungen.
Welche Formeln in F9, G9 und H9 zählen Bestellungen unter den folgenden Bedingungen korrekt:
- F9 - T-Shirt oder Hoodie
- G9 - (T-Shirt oder Hoodie) und (Rot, Blau oder Grün)
- H9 - (T-Shirt oder Hoodie) und (Rot, Blau oder Grün) und (Denver oder Seattle)
Die grüne Schattierung wird mit bedingter Formatierung angewendet und zeigt übereinstimmende Werte für jeden Satz von ODER-Kriterien in jeder Spalte an.
Für Ihre Bequemlichkeit stehen die folgenden benannten Bereiche zur Verfügung:
item = B3: B16
Farbe = C3: C16
Stadt = D3: D16
Das Arbeitsblatt ist beigefügt. Hinterlassen Sie Ihre Antworten unten als Kommentare!
Antwort (zum Vergrößern anklicken)Meine Lösung verwendet SUMPRODUCT mit ISNUMBER und MATCH wie folgt:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Welche Bestellungen zählen wo …
- Artikel ist (T-Shirt oder Hoodie) und
- Farbe ist (Rot, Blau oder Grün) und
- Stadt ist (Denver oder Seattle)
Mehrere Personen schlugen ebenfalls den gleichen Ansatz vor. Ich mag diese Struktur, weil sie sich leicht skalieren lässt, um mehr Kriterien zu handhaben, und auch mit Zellreferenzen (anstelle von fest codierten Werten) funktioniert. Bei Zellreferenzen lautet die Formel in H9:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Der Schlüssel zu dieser Formel ist die ISNUMBER + MATCH-Konstruktion. MATCH wird "rückwärts" eingerichtet - Suchwerte stammen aus den Daten und Kriterien werden für das Array verwendet. Das Ergebnis ist jedes Mal, wenn MATCH verwendet wird, ein einzelnes Spaltenarray. Dieses Array enthält entweder # N / A-Fehler (keine Übereinstimmung) oder Zahlen (Übereinstimmung), daher wird ISNUMBER verwendet, um in die booleschen Werte TRUE und FALSE zu konvertieren. Durch das Multiplizieren der Arrays werden die TRUE FALSE-Werte auf 1s und 0s gezwungen, und das endgültige Array in SUMPRODUCT enthält 1s, wobei Zeilen die Kriterien erfüllen. SUMPRODUCT summiert dann das Array und gibt das Ergebnis zurück.
