In einer Liste stehen Artikelnummern und Mengen, und gesucht ist die größte Menge für den Artikel 4711. MAX allein hilft nicht, denn es sucht über alle Artikel.
Die Kurzlösung
Artikelnummern in A5:A100, Mengen in B5:B100, gesuchter Artikel in D6:
=MAXWENNS(B5:B100;A5:A100;D6)
Für den kleinsten Wert entsprechend =MINWENNS(B5:B100;A5:A100;D6). Weitere Bedingungen hängen Sie paarweise an, etwa für Artikel und Lager:
=MAXWENNS(B5:B100;A5:A100;D6;C5:C100;"Nürnberg")
MAXWENNS und MINWENNS gibt es seit Excel 2019 und in Microsoft 365.
Für Excel 2016 und älter
=AGGREGAT(14;6;B5:B100/(A5:A100=D6);1)
AGGREGAT mit der Funktion 14 (KGRÖSSTE) ignoriert dank der Option 6 Fehlerwerte. Der Trick: Die Division durch die Bedingung ergibt für passende Zeilen die Menge geteilt durch WAHR, also die Menge selbst, für alle anderen Zeilen eine Division durch FALSCH und damit den Fehler #DIV/0!. Übrig bleiben nur die passenden Werte. Für das Minimum verwenden Sie die Funktion 15 (KKLEINSTE). Die Formel braucht keine Eingabe mit Strg+Umschalt+Eingabe.
Die Falle bei MAX(WENN)
Oft wird diese Matrixformel empfohlen:
=MAX(WENN(A5:A100=D6;B5:B100;0))
Das ;0 am Ende setzt für alle anderen Artikel eine 0 ein. Sind die Werte des gesuchten Artikels alle negativ, etwa Abweichungen oder Verluste, gewinnt diese 0. Beispiel mit den Werten -3, -5 und -1 für Artikel 4711:
| Formel | Ergebnis |
|---|---|
=MAX(WENN(A5:A10=D6;B5:B10;0)) | 0, falsch |
=MAX(WENN(A5:A10=D6;B5:B10)) | -1 |
=MAXWENNS(B5:B10;A5:A10;D6) | -1 |
Ohne das ;0 liefert WENN für die anderen Zeilen FALSCH, und MAX übergeht Wahrheitswerte. In Excel 2016 und älter muss die Formel mit Strg+Umschalt+Eingabe abgeschlossen werden.
Typische Fehler
- 0 statt Fehler: Gibt es den gesuchten Artikel gar nicht, liefert MAXWENNS 0 und keinen Fehler. Prüfen Sie bei Bedarf mit
ZÄHLENWENN(A5:A100;D6)=0. - Zahl als Text: Steht die Artikelnummer in der Liste als Text und in D6 als Zahl, findet MAXWENNS nichts. Achten Sie auf einheitliche Formate.
- Unterschiedlich große Bereiche: Bei MAXWENNS müssen Wert- und Kriterienbereich gleich groß sein, sonst meldet Excel #WERT!.
Passende Beiträge
Lernen oder machen lassen?
Excel-Formeln, die im Alltag sitzen.
In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.
