SVERWEIS / SUMMEWENN mit mehreren Suchkriterien
Sie kennen sicher den SVerweis (unsere Lektion hier) und auch wahrscheinlich auch die Limitierungen, die diese Funktion mit sich bringt.
Zum Einen kann man nicht von rechts nach links Werte suchen (unsere Alternative mit Index/Vergleich finden Sie hier). Ein weiteres Problem tritt auf, wenn Sie mehrere Suchkriterien haben. Hier zeigen wir Ihnen ein paar Lösungen zu diesem SVerweis Problem.
Unser Problem
In der Liste oben möchten wir zum Beispiel den Umsatz den Thomas in der Nord Region mit dem Iphone im Januar erzielt hat ermitteln. 20.000 Euro waren es, aber wir können wir dies nun mit Excel machen? Wie erwähnt kann der SVerweis nur maximal ein Suchkriterium suchen, es gibt aber ein paar Tricks dies zu umgehen.
SVerweis mit Hilfsspalte
Die neue Spalte richten wir immer ganz links ein (SVerweis Suchkriterien müssen links sein). Die einzelnen Zellen / Kriterien werden einfach mit dem &-Zeichen verbunden (siehe oben). Um jetzt wie im Beispiel den Umsatz den Thomas in der Nord Region mit dem Iphone im Januar erzielt hat zu errechnen, einfach den normal SVerweis verwenden:
=SVERWEIS(„ThomasNordIphoneJanuar“;B2:G8;6;FALSCH)
Um nicht jedesmal diese lange Kriterium manuell einzugeben, könnte man auch eine Zelle speziell zur Kriterien Eingabe erstellen. Noch praktischer geht das mit Dropdown Feldern für jedes Kriterium (siehe unten):
Mehrere Kriterien mit SUMMEWENNS
Wie schon behandelt im Beitrag zum Thema SUMMEWENN, SUMMEWENNS bietet Ihnen die Möglichkeit bis zu 29 Kriterien anzugeben. In unserem Beispiel würde das dann so aussehen:
=SUMMEWENNS(G2:G8;C2:C8;“Thomas“;D2:D8;“Nord“;E2:E8;“Iphone“;F2:F8;“Januar“)
Das Ergebnis bleibt dasselbe, 20.000 Euro.
Profi Tipp: SUMMENPRODUKT
Eine weitere Möglichkeit unser Problem zu lösen, bietet die SUMMENPRODUKT Funktion. Etwas komplizierter im Aufbau, aber extrem nützlich um Daten aus mehreren Spalten zu summieren. Und da diese Funktion auch Kriterien zulässt, eignet sie sich gut für unser Beispiel.
=SUMMENPRODUKT(G2:G8;(C2:C8=“Thomas“)*(D2:D8=“Nord“)*(E2:E8=“Iphone“)*(F2:F8=“Januar“))
G2:G8 (wie bei SUMMEWENNS) gibt den Bereich der zu ermittelnden Daten an, die vier Kriterien werden dann in Klammern gesetzt und mit dem Multiplikationszeichen verbunden.
5 Comments
Frage oder Kommentar zu diesem Artikel?
Populäre Einträge
Tipps & Artikel auf Facebook
Neueste Einträge
- Zellen über Schaltflächen einfärben, wenn die bedingte Formatierung nicht möglich ist
- KFZ-Modell durch Listenfeldern auswählen (Excel Übung mit Download)
- Pivot-Tabelle mit mehreren Konsolidierungsbereichen erstellen [mit Download]
- Excel-Shortcuts: Die 5 besten Tastenkombinationen die Sie noch nicht kennen
- Rechnen mit Bezügen – Ergebnisse gleichzeitig berechnen [mit Download]
- Excel für Berechnungen einstellen – Rundungsfehler vermeiden [Schnelltipp]
- SVERWEIS, WENN, VERKETTEN – Adressen in einer Rechnung auslesen [mit Download]
- Digitale Würfel durch bedingte Formatierung in Excel erstellen
- Excel Kalender: Wochenenden einfärben
- SUMMEWENN Funktion – Beispiel mit Dropdown-Liste
- Wie kann ich den Durchschnitt der Top 5 Werte einer Liste berechnen?
- Zahlen besser formatieren mit diesen 4 Profi Tipps
Danke für den Artikel. Aber wie ist es wenn ich Zahlen in der Tabelle habe ? Bei mir wird das dann verrechnet. ( Ich möchte nur SVerweis benutzen )
Hallo Dean,
was meinst du mit „es wird das dann verrechnet“? Bekommst du ein falsches Ergebnis? Und mit welcher Formel rechnest du?
Besser ist es, wenn du dich mit deiner Frage direkt ins Forum wendest. Dort hast du auch die Möglichkeit, eine kleine Beispieltabelle hochzuladen.
Gruß Andreas
Ich bekomme keine bestätigungsemail 🙁 Ihr müsst dies nicht veröffentlichen, aber villeicht schaut ihr mal. ich warte jetzt seit 2 stunden
Hallo Dean,
ich kann leider keinen Eintrag von dir finden, der auf eine Bestätigungsmail hinweist. Es geht aus deinem Beitrag auch nicht hervor, worum es sich handelt.
Falls es um einen Forumsbeitrag geht, auch da ist keiner von dir enthalten. Falls du doch einen geschrieben haben solltest, hast du eventuell vergessen auf senden zu drücken.
Veraltete Browser können Anzeigefehler bzw. Sendefehler verursachen.
Gruß Andreas
Hallo Andreas,
ich glaube eher, dass Dean keine Bestätigungsmail zum Anmelden im Forum gekriegt hat.
Nur als kleine Stütze, denke nämlich, dass er das ursprünglich meinte..
Mit bestem Gruß,
Mike