Wie ich in vielen meiner Blogs erwähnt habe, ist ein SUMPRODUCT eine sehr vielseitige Funktion und kann für mehrere Zwecke verwendet werden. In diesem Artikel werden wir sehen, wie wir diese Funktion verwenden können, um Werte mit mehreren ODER-Kriterien zu zählen.
Generische SUMPRODUCT-Formel zum Zählen mit mehreren oder Kriterien
| =SUMMENPRODUKT(--(((Kriterien1)+(Kriterien2)+… )>0) |
Kriterien1: Dies sind alle Kriterien, die ein Array von TRUE und FALSE zurückgeben.
Kriterien2: Dies ist das nächste Kriterium, das Sie überprüfen möchten. Ebenso können Sie so viele Kriterien festlegen, wie Sie möchten.
Die obige generische Formel wird häufig modifiziert, um den Anforderungen zu entsprechen, mit mehreren ODER-Kriterien zu zählen. Aber die Grundformel ist diese. Zuerst werden wir anhand eines Beispiels sehen, wie dies funktioniert, und danach werden wir andere Szenarien besprechen, in denen Sie diese Formel ein wenig ändern müssen.
Beispiel: Benutzer zählen, wenn Händlercode oder JahrStreichhölzer Verwenden von SUMPRODUCT
Hier haben wir also einen Datensatz von Verkäufern. Die Daten enthalten viele Spalten. Was wir tun müssen, ist die Anzahl der Benutzer zu zählen, die den Code "INKA" oder das Jahr "2016" haben. Stellen Sie sicher, dass, wenn jemand beides hat (Code als "inka" und Jahr 2016), es als 1 gezählt werden sollte.
Hier haben wir also zwei Kriterien. Wir verwenden die oben genannte SUMPRODUCT-Formel:
| =SUMMENPRODUKT(--(((Code=I3)+(Jahr=K3))>0)) |
Code und Jahr werden hier als Bereiche bezeichnet.
Dies ergibt 7.
In den Daten haben wir 5 Datensätze des INKA-Codes und 4 Datensätze des Jahres 2016. Aber 2 Datensätze haben sowohl "INKA" als auch 2016 als Code bzw. Jahr. Diese Datensätze werden als 1 gezählt. Und so erhalten wir 7.
Wie funktioniert es?
Schauen wir uns also Schritt für Schritt an, wie die Formel gelöst wird, dann werde ich besprechen, wie es funktioniert.
| =SUMMENPRODUKT(--(((Code=I3)+(Jahr=K3))>0)) |
| 1=>SUMMENPRODUKT(--(({WAHR;FALSCH;WAHR;WAHR;WAHR;WAHR;… }+{FALSCH;FALSCH;FALSCH;WAHR;WAHR;… })>0)) |
| 2=>SUMMENPRODUKT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMMENPRODUKT(--({WAHR;FALSCH;WAHR;WAHR;WAHR;WAHR;WAHR;…}) |
| 4=>SUMMENPRODUKT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
Im ersten Schritt wird der Wert von I3 ("INKA") mit jeder Zelle im Codebereich verglichen. Dies gibt ein Array von TRUE und FALSE zurück. WAHR für jede Übereinstimmung. Aus Platzgründen habe ich nicht alle TRUE-FALSE angezeigt. Ebenso wird der Wert von K3 (2016) mit jeder Zelle im Jahresbereich abgeglichen.
Im nächsten Schritt fügen wir diese beiden Arrays hinzu, die ein neues Array mit numerischen Werten ergeben. Wie Sie vielleicht wissen, wird TRUE in Excel als 1 und FALSE als 0 behandelt. Wenn also TRUE und TRUE addiert werden, erhalten wir 2 und den Rest können Sie verstehen.
Im nächsten Schritt prüfen wir, welcher Wert im Array größer als 0 ist. Dies wandelt das Array wieder in ein echtes falsches Array um. Für jeden 0-Wert, den wir erhalten, werden False und rest in true umgewandelt. Jetzt ist die Anzahl der TRUE-Werte im Array unsere Antwort. Aber wie zählen wir sie? Hier ist wie.
Doppelte negative (--) Vorzeichen werden verwendet, um boolesche Werte in 1s und 0s umzuwandeln. Jeder TRUE-Wert im Array wird also in 1 und FALSE in 0 umgewandelt.
Im letzten Schritt fasst das SUMPRODUCT dieses Array zusammen und wir erhalten unsere Antwort als 7.
Hinzufügen weiterer oder Kriterien zum Zählen mit SUMPRODUCT
Wenn Sie also mehr oder Kriterien zum Zählen hinzufügen müssen, können Sie der Funktion einfach Kriterien mit dem +-Zeichen hinzufügen.
Zum Beispiel, wenn Sie der obigen Formel ein weiteres Kriterium hinzufügen möchten, sodass die Anzahl der Mitarbeiter hinzugefügt wird, die mehr als 5 Produkte verkauft haben. Die SUMPRODUCT-Formel sieht einfach so aus:
| =SUMMENPRODUKT(--(((Code=I3)+(Jahr=K3)+(Umsatz>5))>0)) |
Einfach! ist es nicht?
Aber nehmen wir an, Sie möchten zwei Kriterien von Code Bereich. Angenommen, Sie möchten "INKB" zählen. Also, wie machst du das? Eine Methode verwendet die obige Technik, aber das würde sich wiederholen. Nehmen wir an, ich möchte 10 weitere Kriterien aus dem gleichen Bereich hinzufügen. In solchen Fällen ist diese Technik für das Zählen mit SUMPRODUCT nicht so schlau.
Nehmen wir an, wir haben Daten so angeordnet.
Die Kriteriencodes befinden sich in einer Zeile I2:J2. Wichtig ist hier die Anordnung der Daten. Die SUMMENPRODUKT-Formel für 3 ODER-Kriterienzählereinstellungen lautet:
| =SUMMENPRODUKT(--(((Code=I2:J2)+(Jahr=I3:J3))>0)) |
Dies ist die SUMPRODUCT-Formel zum Zählen mit mehreren Kriterien, wenn mehrere Kriterien aus einem Bereich hintereinander geschrieben werden.
Dies gibt die richtige Antwort zurück, die 10 ist.
Wenn Sie in J3 ein beliebiges Jahr eingeben, addiert die Formel auch diese Zahl.
Dies wird verwendet, wenn sich die Kriterien in einer Zeile befinden. Funktioniert es, wenn sich die Kriterien in einer Spalte für denselben Bereich befinden? Nein, wird es nicht.
In diesem Beispiel müssen wir mehrere Codes zählen, aber diese Typcodes werden in eine Spalte geschrieben. Wenn wir die obige SUMPRODUCT-Formel verwenden, erhalten wir einen #N/A-Fehler. Wir werden nicht darauf eingehen, wie es zu diesem Fehler kam, da dieser Artikel dadurch zu lang wird. Mal sehen, wie wir das schaffen können.
Damit diese Formel funktioniert, müssen Sie die Codekriterien in die TRANSPOSE-Funktion einschließen. Damit funktioniert die Formel.
| =SUMMENPRODUKT(--(((Code=TRANSPOSE(H3:H4))+(Jahr=TRANSPOSE(I3:I4)))>0)) |
Dies ist die Formel zum Zählen mit mehreren oder Bedingungen im gleichen Bereich, wenn Kriterien in einer Spalte aufgeführt sind.
Also ja, Kumpel, ich hoffe, ich war klar genug und es machte Sinn. Ich hoffe, es dient Ihrem Zweck, hier zu sein. Wenn diese Formel Ihr Problem nicht gelöst hat, teilen Sie mir Ihre Anforderungen im Kommentarbereich unten mit. Ich helfe Ihnen gerne in jeder Hinsicht. Sie können alle Zweifel im Zusammenhang mit Excel/VBA erwähnen. Bis dahin lerne weiter, bleib überragend.
So verwenden Sie die SUMMENPRODUKT-Funktion in Excel: Gibt die SUMME nach der Multiplikation von Werten in mehreren Arrays in Excel zurück. Diese Funktion kann verwendet werden, um mehrere Aufgaben zu erledigen. Dies ist eine der vielseitigsten Funktionen.
ZÄHLENWENN mit dynamischem Kriterienbereich : Um mit dynamischem Kriterienbereich zu zählen, verwenden wir einfach die INDIRECT-Funktion. Diese Funktion kann
ZÄHLENWENN mit ODER für mehrere Kriterien : Zellen mit mehreren übereinstimmenden Kriterien mit der ODER-Funktion zählen. Um eine ODER-Logik in die ZÄHLENWENN-Funktion einzufügen, müssen Sie die ODER-Funktion nicht verwenden.
Verwenden der WENN mit UND / ODER-Funktionen in Microsoft Excel : Diese logischen Funktionen werden verwendet, um Berechnungen mit mehreren Kriterien durchzuführen. Bei IF werden die ODER- und UND-Funktionen verwendet, um Übereinstimmungen ein- oder auszuschließen.
So verwenden Sie die ODER-Funktion in Microsoft Excel : Die Funktion wird verwendet, um alle TRUE-Werte in mehrere Kriterien aufzunehmen.
So zählen Sie Zellen, die dies oder das in Excel in Excel enthalten :Auf Zellen, die dies oder das enthalten, können wir die SUMPRODUCT-Funktion verwenden. So führen Sie diese Berechnungen durch.
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
So verwenden Sie die Excel SVERWEIS-Funktion| Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie Excel ZÄHLENWENN-Funktion| Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.