Summe der kleinsten oder untersten N Werte basierend auf Kriterien in Excel
In einem vorherigen Tutorial haben wir erläutert , wie die kleinsten n Werte in einem Datenbereich summiert werden. In diesem Artikel gehen wir einen Schritt weiter und zeigen Ihnen eine fortgeschrittene Methode: das Summieren der niedrigsten n Werte basierend auf einem oder mehreren Kriterien in Excel.
Summe der kleinsten oder untersten N Werte basierend auf Kriterien in Excel
Angenommen, ich habe einen Datenbereich wie im folgenden Screenshot dargestellt. Nun möchte ich die Summe der drei niedrigsten Bestellungen für das Produkt „Apple“ ermitteln.
Um in Excel die kleinsten n Werte in einem Bereich unter Berücksichtigung bestimmter Kriterien zu summieren, erstellen Sie eine Arrayformel mithilfe der Funktionen SUMME, KLEINSTE und WENN. Die allgemeine Syntax lautet:
Array formula, should press Ctrl + Shift + Enter keys together.
- range=criteria: Der Zellbereich, der mit dem spezifischen Kriterium abgeglichen werden soll;
- values: Die Liste, die die untersten n Werte enthält, die Sie summieren möchten;
- N: Der n-kleinste Wert.
Um das oben genannte Problem zu lösen, wenden Sie bitte die folgende Matrixformel in eine leere Zelle an:
Drücken Sie anschließend gleichzeitig die Tasten Strg + Umschalt + Enter, um das korrekte Ergebnis wie im folgenden Screenshot dargestellt zu erhalten:
Erläuterung der Formel:
=SUMME(KLEINSTE(WENN(($A$2:$A$14=D2); $B$2:$B$14);{1;2;3}))
- WENN(($A$2:$A$14=D2); $B$2:$B$14): Wenn das Produkt im Bereich A2:A14 „Apple“ ist, gibt die Formel die entsprechende Zahl aus der Bestellliste (B2:B14) zurück; ist das Produkt nicht „Apple“, wird FALSCH angezeigt. Das Ergebnis sieht dann wie folgt aus: {800;FALSCH;FALSCH;FALSCH;1000;230;FALSCH;FALSCH;1600;FALSCH;900;FALSCH;500}.
- KLEINSTE(WENN(($A$2:$A$14=D2); $B$2:$B$14);{1,2,3}): Diese KLEINSTE-Funktion ignoriert die FALSCH-Werte und gibt die drei kleinsten Werte im Array zurück – das Ergebnis lautet daher: {230,500,800}.
- SUMME(KLEINSTE(WENN(($A$2:$A$14=D2); $B$2:$B$14);{1,2,3}))=SUMME({230,500,800}): Schließlich summiert die SUMME-Funktion die Zahlen im Array – und liefert das Endergebnis: 1530.
Tipp: Umgang mit zwei oder mehr Bedingungen:
Wenn Sie die untersten n Werte basierend auf zwei oder mehr Kriterien summieren müssen, fügen Sie innerhalb der WENN-Funktion einfach weitere Bereiche und Kriterien mithilfe des *-Zeichens hinzu, wie folgt:
Array formula, should press Ctrl + Shift + Enter keys together.
- Range1=criteria1: Der erste Zellbereich, der mit dem ersten Kriterium abgeglichen werden soll;
- Range2=criteria2: Der zweite Zellbereich, der mit dem zweiten Kriterium abgeglichen werden soll;
- Range3=criteria3: Der dritte Zellbereich, der mit dem dritten Kriterium abgeglichen werden soll;
- values: Die Liste, die die untersten n Werte enthält, die Sie summieren möchten;
- N: Der n-te kleinste Wert.
Beispielsweise möchten Sie die untersten 3 Bestellungen des Produkts „Apple“, die von Kerry verkauft wurden, summieren. Verwenden Sie dazu bitte die folgende Formel:
Drücken Sie anschließend gleichzeitig die Tasten Strg + Umschalt + Enter, um das gewünschte Ergebnis zu erhalten:
Verwendete verwandte Funktion:
- SUMME:
- Die SUMME-Funktion addiert Werte – einzeln, als Zellbezüge, als Bereiche oder in jeder beliebigen Kombination daraus.
- KLEINSTE:
- Die Excel-Funktion KLEINSTE gibt den numerischen Wert zurück, der an einer bestimmten Position in einer nach aufsteigenden Werten sortierten Liste steht.
- WENN:
- Die WENN-Funktion prüft eine bestimmte Bedingung und gibt den von Ihnen festgelegten Wert für WAHR bzw. FALSCH zurück.
Weitere Artikel:
- Summe der kleinsten oder untersten N Werte
- In Excel lässt sich ein Zellbereich mithilfe der SUMME-Funktion kinderleicht summieren. Gelegentlich müssen Sie jedoch die kleinsten – also untersten – 3, 5 oder n Zahlen innerhalb eines Datenbereichs addieren, wie im folgenden Screenshot veranschaulicht. In solchen Fällen unterstützen Sie die Funktionen SUMMENPRODUKT und KLEINSTE dabei, diese Aufgabe in Excel elegant zu lösen.
- Zwischensummen für Rechnungsbeträge nach Alter in Excel
- Das Summieren der Rechnungsbeträge nach Alter – wie im folgenden Screenshot dargestellt – ist eine häufige Aufgabe in Excel. In diesem Tutorial erfahren Sie, wie Sie mit der klassischen SUMMEWENN-Funktion ganz einfach Zwischensummen für Rechnungsbeträge nach Alter erstellen.
- Summe aller Zahlzellen unter Ignorierung von Fehlern
- Beim Summieren eines Zahlenbereichs, der Fehlerwerte enthält, versagt die herkömmliche SUMME-Funktion. Um ausschließlich Zahlen zu addieren und dabei Fehlerwerte zu ignorieren, helfen Ihnen die AGGREGAT-Funktion oder eine Kombination aus SUMME und WENNFEHLER-Funktion weiter.
Die besten Office-Produktivitätswerkzeuge
Kutools für Excel – Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen und stellt sicher, dass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab – Aktiviert tabbasiertes Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- Mit einem Klick zwischen Dutzenden geöffneter Dokumente wechseln!
- Reduziert täglich Hunderte Mausklicks für Sie – verabschieden Sie sich von der Maus-Hand.
- Steigert Ihre Produktivität um 50 %, wenn Sie mehrere Dokumente gleichzeitig anzeigen und bearbeiten.
- Bringt effiziente Tabs in Office (inklusive Excel) – genau wie bei Chrome, Edge und Firefox.