Sie brauchen nichts zu nisten. Die Funktion SUMPRODUCTist eine sehr mächtige Funktion, mit der Sie basierend auf sehr komplexen Bedingungen entweder zählen oder summieren können. Es unterstützt sowohl den logischen AND- als auch den OR-Operator.
TL; DR-Version
=SUMPRODUCT((($A$2:$A$10="Apple")+($A$2:$A$10="Pear"))*(($B$2:$B$10="Red")+($B$2:$B$10="Green"))*($C$2:$C$10<>"Yes")) Lange Version (sorry ...)
Sie sollten wirklich lernen, wie man diese Funktion verwendet, weil sie sehr nützlich ist und viel Zeit sparen kann. Hier finden Sie eine kurze Anleitung zur Verwendung, aber ich empfehle Ihnen, eine Art Testdatei mit einfachen Beispielen wie dem von Ihnen bereitgestellten aufzubewahren, damit Sie damit experimentieren können. Zögern Sie nicht, die verschiedenen Probleme, die Sie lösen möchten, aufzuteilen.
Grundsätzlich SUMPRODUCTenthält a mehrere Mitglieder, von denen jedes eine Bedingung ist. Wenn Sie diese Bedingungen multiplizieren, erhalten Sie ein logisches UND. Wenn Sie diese Bedingungen hinzufügen, erhalten Sie ein logisches ODER.
Mit einem Beispiel ist es viel einfacher! Hier ist also die Aufschlüsselung der endgültigen Formel anhand Ihrer Früchte.
Hinweis: Zur Vereinfachung habe ich Tabellennamen verwendet Fruits, die die Verwendung von benannten Bereichen ermöglichen und die Lesbarkeit verbessern.
So funktioniert es jetzt. Ihre erste Bedingung ist eigentlich ein logisches ODER (Sie möchten entweder Äpfel oder Birnen, da eine Frucht nicht beides sein kann. Die Formel (1) besteht also aus 2 Mitgliedern oder 2 Bedingungen: Frucht = Apfel, Frucht = Birne.
(Fruits[Fruit]="Apple")kehrt zurück, TRUEwenn es ein Apfel ist, FALSEsonst. Wenn Sie dies zum 2. member ( (Fruits[Fruit]="Pear")) summieren, erhalten Sie ein Array von 0 und 1. Die Logik sieht folgendermaßen aus:
- Falsch + Falsch = 0
- Richtig + Falsch = 1
- True + True = 1
Dann SUMPRODUCTsummiert das alle Elemente dieses Arrays (0 und 1). Das ist im Grunde dasselbe wie das Zählen von Elementen. Das Ergebnis ist erwartungsgemäß 8 (Zelle D14).
Die 2. Bedingung (Farbe) funktioniert genauso. Wenn Sie eine dritte Farbe hinzufügen möchten (z. B. "orange"), fügen Sie einfach ein neues Mitglied in die Gleichung mit ein +.
Die 3. Bedingung ist einfacher, Sie haben nur 1 Kriterien. Ich habe das <>Zeichen absichtlich verwendet, um Ihnen zu zeigen, dass Sie auch Elemente ausschließen können. In der Formel (1) könnten Sie beispielsweise verwendet haben (Fruits[Fruit]<>"Banana").
Wichtig: Bitte beachten Sie, dass in a SUMPRODUCT, wenn es nur ein Element gibt (wie in der 3. Gleichung), das Ergebnis in eine Zahl umgewandelt werden muss. Denken Sie daran: Das SUMPRODUCTerstellt ein Array von TRUEund FALSE. Sie können dies leicht tun, indem Sie die N(...)Funktion verwenden oder alternativ schreiben --(...), was sich TRUEin 1 und FALSEin 0 verwandelt .
Nun, diese 3 Bedingungen funktionieren einzeln, aber wir möchten sie zu einer Formel zusammenfassen. Und da wir möchten, dass jede dieser drei Bedingungen wahr ist (Frucht, Farbe und Gift), müssen wir ein logisches UND erstellen. Dies kann auf dieselbe Weise wie bei unserem ODER getan werden, aber dieses Mal verwenden wir die Multiplikation ( *):
- False * False = 0
- True * False = 0
- True * True = 1
Für unsere 3 Bedingungen verwenden wir einfach die 3 Einzelformeln, die wir zuvor geschrieben haben, und fügen sie in eine ein SUMPRODUCT. Diese drei Elemente müssen durch Klammern gekapselt und durch a getrennt werden *(wir multiplizieren sie im Grunde).
Hier sind die endgültigen Formeln, die Sie ausprobieren können:
=SUMPRODUCT((Fruits[Fruit]="Apple")+(Fruits[Fruit]="Pear")) =SUMPRODUCT((Fruits[Color]="Red")+(Fruits[Color]="Green")) =SUMPRODUCT(N(Fruits[Poisonous]<>"Yes")) Und der letzte:
=SUMPRODUCT(((Fruits[Fruit]="Apple")+(Fruits[Fruit]="Pear"))*((Fruits[Color]="Red")+(Fruits[Color]="Green"))*(Fruits[Poisonous]<>"Yes")) Side Notes:
- Jede Bedingung, die Sie testen (z. B. Farbe = Rot), muss zwischen Klammern gekapselt sein:
(Fruits[Color]="Red") - Die Reihenfolge der Klammern ist sehr wichtig, wenn Sie ODER-Bedingungen haben. Zum Beispiel ist die Gleichung
(X and (Y or Z))nicht gleich((X and Y) or Z). - Sie können die klassischen Operatoren verwenden, um eine Bedingung zu testen: = für gleich, <> für verschieden,> und <für größer / kleiner als,> = und <= für größer / kleiner oder gleich.
- Wir haben das verwendet, um
SUMPRODUCTzu zählen, aber wir können es auch verwenden, um Dinge zusammenzufassen. Wenn eines der Glieder der Gleichung kein=Vorzeichen hat, werden die Werte berücksichtigt (siehe Beispiel unten, in dem die Spalte G summiert wird). - Die Kriterien
"Apple"können durch einen Verweis auf eine Zelle ersetzt werden, die selbst ein Dropdown-Menü sein kann. Es empfiehlt sich, Variablen zu verwenden, anstatt Text direkt in eine Formel zu schreiben. SUMPRODUCTkann eine ressourcenintensive Formel sein, da Multiplikationen und Summen erstellt werden ... Abhängig davon, wie viele Bedingungen Sie testen, wie groß der Datensatz ist und wie oft Sie a verwendenSUMPRODUCT. Bei einfacheren BedingungenSUM.IFSgeht das wohl schneller.SUMPRODUCTunterstützt auch die teilweise Textsuche (siehe unten):
