Anzahl Zahlen absteigend je ID

[joshua]

Hallo Community!

Ich habe folgende Datensätze

111 - 5
111 - 5
111 - 5
111 - 3
112 - 4
112 - 4
122 - 3
122 - 2
123 - 5
123 - 4
123 - 2
123 - 3

Also Spalte [Id] & [Key]

habe dann dann in Zelle H2 ein Paratemter, wie "5".
Nun möchte ich wissen, wie viele Zahlen (Ohne Duplikate) die ID 111 inklusive der 5 besitzt, jedoch ohne Unterbrechung. 
Bei 111 wäre das also = 1
Bei 112 wäre das 0, da 5 nicht enthalten ist
Bei 123 wäre das 4; da 5,4,3,2

Also wie viele Zahlen , absteigend vom Paramter, besitzt die jeweilige ID?
Wie wäre das mithilfe einer Funktion zu identifzieren?

Gruß

[LCohen]

Darf ich korrigieren?

Bei 111 wäre das also = 5
Bei 112 wäre das 0, da 5 nicht enthalten ist
Bei 123 wäre das 4; da 5,4,3.

müsste richtig heißen:

Bei 111 wäre das also 4 (oder 2 verschiedene)
Bei 112 wäre das 0, da 5 nicht enthalten ist
Bei 122 wäre das 0, da 5 nicht enthalten ist
Bei 123 wäre das 4; da 5,4,3,2.

[joshua]

Entschuldige du hast recht!
Nur beim Ersten nicht.

Bei 111 wäre das also 1, da 5 enthalten. 3 darf nicht gezählt werden, da die 4 fehlt

[neopa]

Hallo joshua,

wenn Deine Daten zumindest in der ID Spalte nach dieser sortiert sind, dann als Matrixfunktion(alität)sformel (die keines spez. Formelabschluss wie eine klassische Matrixformel benötigt) z.B. wie nachfolgend in E2 aufgezeigt. (Besser jedoch die Daten für ID und Key gleich als "intelligente" Tabelle formatieren. Dann könnte die Formelauswertung in D2 und E2 gleich für diese umdefiniert werden und beide Formeln würde sich dann auch automatisch an eine evtl. Datenerweiterung anpassen). Di e beiden Formeln müssten natürlich nach wie vor entsprechend weit nach unten kopiert werden. 

Arbeitsblatt mit dem Namen 'AGGR_181215_1'
	A	B	C	D	E	F	G	H
1	ID	Key		ID	Anz.			Vorgabe
2	111	5		111	1			5
3	111	5		112	0
4	111	5		122	0
5	111	3		123	4
6	112	4
7	112	4
8	122	3
9	122	2
10	123	5
11	123	4
12	123	2
13	123	3
14	123	4
15	123	2
16	123	3
17	123	5
18

Zelle	Formel
D2	=WENNFEHLER(AGGREGAT(15;6;A$2:A$99/(A$2:A$99>--WECHSELN(D1;"ID";0));1);"")
E2	=WENN(D2="";"";VERGLEICH(0;INDEX(ZÄHLENWENN(INDEX(B:B;VERGLEICH(D2;A:A;0)):INDEX(B:B;VERGLEICH(D2;A:A;0)+ZÄHLENWENN(A:A;D2)-1);H$2+1-ZEILE(A$1:INDEX(A:A;H$2))););0)-1)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[neopa]

Hallo joshua,

ein Nachtrag zu meinem Formevorschlag in E2 wäre noch wichtig. Sollten alle Zahlen <=H2  in Spalte B  vorhanden sein, dann Bedarf die Formel noch einer Ergänzung mit WENNFEHLER(). Also so:

=WENN(D2="";"";WENNFEHLER(VERGLEICH(0;INDEX(ZÄHLENWENN(INDEX(B:B;VERGLEICH(D2;A:A;0)):INDEX(B:B;VERGLEICH(D2;A:A;0)+ZÄHLENWENN(A:A;D2)-1);H$2+1-ZEILE(A$1:INDEX(A:A;H$2))););0)-1;H$2))

Etwas kürzer (das heißt aber nicht, dass es nicht evtl. noch kürzer geht) bzw. mit einer Funktion weniger wird die Formel so:

=WENN(D2="";"";WENNFEHLER(VERGLEICH(0;INDEX(ZÄHLENWENN(INDEX(B:B;VERGLEICH(D2;A:A;0)):INDEX(B:B;VERWEIS(9;1/(A$1:A$99=D2);ZEILE(A:A)));H$2+1-ZEILE(A$1:INDEX(A:A;H$2))););0)-1;H$2))

[joshua]

Vielen Dank!