Thema: Datenüberprüfung mit mehreren Kriterien möglich?

Hallo, da ich zu keiner zufriedenstellenden Lösung über die Suche im Internet finde, frage ich hier nach.

In der Spalte J, Zeile 5 bis 26 sollen maximal 3 Zellen ausgefüllt werden können mit den Zahlen 1 bis 3. Jede Zahl darf nur einmal vergeben werden.

Mein bisheriger Ansatz ist:
Datenüberprüfung --> Zulassen: Liste --> Quelle: =WENN(ANZAHL2(P$5:P$26)<=2;$AA$5:$AA$7;"")
$AA$5:$AA$7 ist eine Dropdownliste mit den Werten 1, 2, 3

Hierüber begrenze ich die Anzahl der ausgefüllten Zellen auf drei.
Wie bekomme ich es hin, dass jede Zahl von 1 bis 3 nur einmal vergeben werden kann?

Hi herber_Axel

Markiere die Zellen J5 bis J26.
Gehe zu Daten > Datenüberprüfung.
Wähle im Dialogfeld unter Zulassen die Option Benutzerdefiniert.
Gebe folgende Formel in das Feld Formel ein:

=UND(ANZAHLWENN($J$5:$J$26;1)<=1; ANZAHLWENN($J$5:$J$26;2)<=1; ANZAHLWENN($J$5:$J$26;3)<=1)

Diese Formel überprüft, ob die Zahlen 1, 2 und 3 jeweils nur einmal in dem Bereich J5 bis J26 vorkommen.

Gruss

Raimund

Hallo Raimund,

vielen Dank für deine Antwort. Dein Vorschlag führte zu einer Fehlermeldung, es hat ein wenig gedauert das zu verstehen. Die von dir eingebaute Funktion

ANZAHLWENN

gibt es nicht. Es klappt mit der Funktion

ZÄHLENWENN

Jedoch wird mit deinem Vorschlag das zweite Kriterium, dass nur drei Zellen in der Spalte ausgefüllt werden dürfen, nicht umgesetzt. Und man kann auch Zahlen eintragen, die größer als 3 sind.

Vielleicht hatte ich mich zu unkonkret ausgedrückt. Folgendes möchte ich umsetzen:
In der Spalte J, Zeile 5 bis 26 dürfen insgesamt nur drei Zellen ausgefüllt werden. Inhalt dürfen nur die Zahlen 1, 2 oder 3 sein. Jede Zahl darf nur einmal eingetragen werden.

Okay, ich selbst wäre nie drauf gekommen, dafür reicht mein Excel-Verständnis nicht aus. Ich habe Chat-GPT gefragt und diese Formel erhalten:

=UND(ODER(J5=1;J5=2;J5=3);ZÄHLENWENN($J$5:$J$26;J5)<=1;ZÄHLENWENN($J$5:$J$26;">0")<=3)

Auf diese Weise funktioniert es. Die mit ausgegebene Erklärung:

Aufbau der Formel
Die Formel besteht aus drei Bedingungen, die alle erfüllt sein müssen (UND(...)):

1️⃣ Erlaubt nur die Zahlen 1, 2 oder 3:
ODER(J5=1;J5=2;J5=3)
Diese Bedingung sorgt dafür, dass nur die Zahlen 1, 2 oder 3 in J5:J26 eingetragen werden können.
Andere Zahlen oder Buchstaben werden nicht akzeptiert.

2️⃣ Jede Zahl darf nur einmal vorkommen:
ZÄHLENWENN($J$5:$J$26;J5)<=1
ZÄHLENWENN($J$5:$J$26;J5) zählt, wie oft der Wert in der Spalte vorkommt.
<=1 stellt sicher, dass jede Zahl (1, 2 oder 3) nur einmal vorkommt.

3️⃣ Maximal drei Zellen dürfen belegt sein:
ZÄHLENWENN($J$5:$J$26;">0")<=3
ZÄHLENWENN($J$5:$J$26;">0") zählt, wie viele Zellen einen Wert größer als 0 haben (also 1, 2 oder 3).
<=3 begrenzt die Anzahl der belegten Zellen auf maximal drei.

Hallo Axel,

... und zwar einfach so:

=(MAX(ZÄHLENWENN(J$5:J$26;ZEILE(J$1:J$3)))=1)*((J5=1)+(J5=2)+(J5=3))

Auf die Erklärung der Formel kann ich doch sicherlich verzichten.

Die Formel würde noch etwas einfacher/kürzer, wenn man (evtl. in neuesten XL-Versionen; ich hab nur eine noch ältere als Deine XL2019, die aber auch schon etwas "in die Jahre gekommen ist") in der Datengültigkeit endlich auch Matrixkonstanten einsetzen könnte (was zumindest in meiner XL-Version) eben nicht möglich ist. Dann könnte anstelle ZEILE(J$1:J$3) einfach nur {1;2;3} eingesetzt werden.

Gruß Werner
.. , - ...
aus C the unseen

Hallo Werner, herzlichen Dank für die Formel!

Grüße nach Chemnitz, der Kulturhauptstadt Europas 2025!