Verwendung von SUMMIFS mit mehreren AND OR-Bedingungen
Ich möchte eine prägnante Excel-Formel erstellen, die eine Spalte basierend auf einer Reihe von AND-Bedingungen plus einer Reihe von OR-Bedingungen SUMMIERT.
Meine Excel-Tabelle enthält die folgenden Daten und ich habe definierte Namen für die Spalten verwendet.
- Quote_Value (Arbeitsblatt! $ A: $ A) enthält einen Buchhaltungswert.
- Days_To_Close (Arbeitsblatt! $ B: $ B) enthält eine Formel, die zu einer Zahl führt.
- Verkäufer (Arbeitsblatt! $ C: $ C) enthält Text und ist ein Name.
- Quote_Month (Arbeitsblatt! $ D: $ D) enthält eine Formel (= TEXT (Arbeitsblatt! $ E: $ E, "mmm-yy")), um eine Datums- / Uhrzeitnummer aus einer anderen Spalte in einen textbasierten Monat zu konvertieren Referenz.
Ich möchte SUM Quote_Value, wenn Salesman gleich JBLoggs und Days_To_Close ist gleich oder kleiner als 90 und Quote_Month ist gleich einem der folgenden (13. Oktober, 13. November oder 13. Dezember).
Im Moment habe ich das zur Arbeit, aber es beinhaltet eine Menge Wiederholungen, von denen ich glaube, dass ich sie nicht brauche.
%Vor%Was ich gerne machen würde, ist etwas mehr wie das Folgende, aber ich kann nicht die richtige Syntax ausarbeiten:
%Vor%Diese Formel ist kein Fehler, sie gibt nur einen 0-Wert zurück. Wenn ich die Daten manuell überprüfe, ist das nicht korrekt. Ich habe sogar versucht, mit TRIM (Quote_Month) sicherzustellen, dass Leerzeichen nicht in die Daten hineingeschlichen sind, aber die Tatsache, dass meine erweiterte SUM-Formel funktioniert, zeigt an, dass die Daten in Ordnung sind und dass es sich um ein Syntaxproblem handelt. Kann mich jemand in die richtige Richtung lenken?
7 Antworten
Sie können SUMIFS wie folgt verwenden
=SUM(SUMIFS(Quote_Value,Salesman,"JBloggs",Days_To_Close,"<=90",Quote_Month,{"Oct-13","Nov-13","Dec-13"}))
Die SUMIFS -Funktion gibt ein "Array" von 3 Werten zurück (jeweils ein Gesamtwert für "Okt-13", "Nov-13" und "Dez-13"), also benötigen Sie SUM , um das zu addieren Array und geben Ihnen das Endergebnis.
Seien Sie vorsichtig mit dieser Syntax, Sie können höchstens zwei Kriterien innerhalb der Formel mit "OR" Bedingungen haben ... und wenn es zwei gibt, dann müssen Sie die Kriterien mit
Wenn Sie mehr benötigen, können Sie SUMPRODUCT mit MATCH , z. in deinem Fall
=SUMPRODUCT(Quote_Value,(Salesman="JBloggs")*(Days_To_Close<=90)*ISNUMBER(MATCH(Quote_Month,{"Oct-13","Nov-13","Dec-13"},0)))
In dieser Version können Sie eine beliebige Anzahl von "ODER" -Kriterien hinzufügen, indem Sie ISNUMBER/MATCH
Sie können DSUM verwenden, das flexibler ist. Wenn Sie beispielsweise den Namen Salesman oder den Angebotsmonat ändern möchten, müssen Sie die Formel nicht ändern, sondern nur einige Kriterienzellen. Bitte beachten Sie den Link unten für Details ... Auch die Kriterien können Formel aus anderen Blättern kopiert werden
Sie könnten in der Quellspalte auf das tatsächliche Datum / die tatsächliche Uhrzeit für Quote_Month verweisen, dann könnten Sie Ihre OR in ein paar AND s umwandeln, so etwas wie (das Datum in etwas auswählen, das ich gewählt habe) um Quote_Date ) aufzurufen
(Ich habe die interessanten Bedingungen nach vorne verschoben).
Dieser Ansatz funktioniert hier, weil die "ODER" -Bedingung tatsächlich einen Datumsbereich angibt - in anderen Fällen möglicherweise nicht.
Quote_Month (Arbeitsblatt! $ D: $ D) enthält eine Formel (= TEXT (Arbeitsblatt! $ E: $ E, "mmm-yy")), um eine Datums- / Uhrzeitnummer aus einer anderen Spalte in einen textbasierten Monat zu konvertieren Referenz.
Sie können OR verwenden, indem Sie + in Sumproduct hinzufügen. Siehe dies
=SUMPRODUCT((Quote_Value)*(Salesman="JBloggs")*(Days_To_Close<=90)*((Quote_Month="Cond1")+(Quote_Month="Cond2")+(Quote_Month="Cond3")))
Bildschirmfoto
Geschwindigkeit
SUMPRODUCT ist schneller als SUM -Arrays, d.h. mit {} -Arrays in der Funktion SUM . SUMIFS ist 30% schneller als SUMPRODUCT .
{SUM(SUMIFS({}))} vs SUMPRODUCT(SUMIFS({})) funktioniert gut, aber SUMPRODUCT fühlt sich etwas einfacher zu schreiben, ohne dass CTRL-SHIFT-ENTER die {} erstellt.
Präferenz
Ich persönlich bevorzuge das Schreiben von SUMPRODUCT(--(ISNUMBER(MATCH(...)))) über SUMPRODUCT(SUMIFS({})) für mehrere Kriterien.
Wenn Sie jedoch ein Dropdown-Menü haben, in dem Sie bestimmte Merkmale oder alle auswählen möchten, ist SUMPRODUCT(SUMIFS()) die einzige Möglichkeit. (Wie bei der Auswahl von "Alle" sollte der Wert in "& lt; & gt;" + "Welches Wort auch immer Sie wollen, solange es nicht Teil der spezifischen Merkmale ist" eingegeben werden.
Um die Formel zu arbeiten, platzieren Sie den Cursor innerhalb der Formel und drücken Sie Strg + Shift + Enter und dann wird es funktionieren!
Tags und Links excel conditional sum sumifs