Excel komplizierte Auswertung, Profi benötigt

[-Slayer-]

Hallo Zusammen und Entschuldigung für den etwas nichtssagenden Titel.
Ich habe echt Probleme mein Problem prägnant zusammenzufassen. Das äußert sich auch darin, dass ich nicht mal weiß was ich googlen soll.

Ich habe folgendes Problem, bzw. möchte folgendes auswerten:

ID	Sparte
1	a
2	b
3	c
1	b
1	a
3	a

Ich habe eine Spalte mit einer ID und eine Spalte mit nennen es wir einfach mal "Sparte". Jetzt möchte ich z. B. die Anzahl der "a" mit unterschiedlicher ID. In diesem Beispiel müsste also herauskommen a=2. "a" gibt es zwar 3 mal in der Tabelle aber nur mit zwei unterschiedlichen IDs. "b" wäre hier z. B. auch 2 und "c" ist 1.

Ich habe jetzt schon einiges Probiert z. B. Pivottabelle (muss zugeben damit hatte ich bisher noch nicht so viel gearbeitet und übersehe vielleicht was), XVERWEIS, ZÄHLENWENN(S), etc.

Ich bekomme es aber nicht hin, eine Formel zu bauen, welche mir mein gewünschtes Ergebnis liefert. Erschwerend kommt hinzu, dass ich gerne so wenig wie möglich die Formel manuell anpassen möchte. Also z. B. eine Formel wo ich manuell die ID eintragen muss wäre absolut schlecht. Dann lieber Sparte, das sind nicht so viele unterschiedliche Datensätze. Aber am liebsten hätte ich es natürlich so automatisch wie möglich.

Ich hoffe hier gibt es ein paar Excelprofis, welche mir weiterhelfen können.

Ich bedanke mich schon einmal im Voraus für eure Mühen

PS: An die Mods, wenn der Titel nicht passt ändere ich ihn gerne nochmal ab, aber ich weiß wirklich nicht wie ich mein Problem kurz zusammenfassen kann. Also Sorry nochmal.

 

[Amaoto]

Klingt ein bisschen nach COUNTIF, bzw. COUNTIFS.
Schau mal, ob eins der Beispiele dir weiterhilft:
https://superuser.com/questions/403251/count-value-pairs-in-excel

 

[floq0r]

ez

 

[steve1da]

Hola,
@floq0r: wozu die Hilfsspalte? Der Vorschlag ist mMn falsch. Richtig wäre:
https://www.herber.de/excelformeln/src/call.pl?idx=334
Gruß,
steve1da

 

[floq0r]

Ja, so ginge es dann auch:
{=SUMME(([ID]=[@ID])*([Sparte]=[@Sparte]))}

 

[steve1da]

Eben nicht. Der TE möchte die Anzahl unterschiedlicher IDs zu einer Sparte. Bei Sparte B sind es 2 unterschiedliche und deine Formel ergibt das nicht.

 

[-Slayer-]

Vielen Dank schonmal alle für eure Hilfe. Ich probiere gerade alles nachzuvollziehen und parallel eure Vorschläge direkt umzusetzen. Das dauert etwas, bitte seht es mir nach wenn ich nicht sofort antworte.

@steve1da :

Hola,
Richtig wäre: https://www.herber.de/excelformeln/src/call.pl?idx=334
Gruß,
steve1da

Also ich habe mir jetzt die Formeln angeschaut und blicke noch nicht ganz durch.

Bei der ersten Formel: =SUMMENPRODUKT((VERGLEICH(A1:A10&B1:B10;A1:A10&B1:B10;0)=ZEILE(X1:X10))*(A1:A10=4)*(B1:B10<>"")) verstehe ich nicht ganz woher das "X1:X10" kommt.

Die zweiten Formel: {=SUMME(WENN((A1:A10=4)*(B1:B10<>"");1/ZÄHLENWENNS(B1:B10;B1:B10;A1:A10;4)))} habe ich einmal für meine Hilfstabelle umgeschrieben in:
{=SUMME(WENN((F2:F7=1)*(G2:G7<>"");1/ZÄHLENWENNS(G2:G7;G2:G7;F2:F7;1)))}.
Dann kommt für "a" bzw. "1" 2 heraus, was stimmt.

Mache ich jetzt für "b" bzw. "2" folgenden Code:
{=SUMME(WENN((F2:F7=2)*(G2:G7<>"");1/ZÄHLENWENNS(G2:G7;G2:G7;F2:F7;2)))} kommt 1 heraus, was ja falsch ist.

Was mache ich falsch?


edit: Laut dem Link ist die Formel für "Es sollen alle unterschiedlichen, nichtleeren Zellen in Spalte B gezählt werden, in denen gleichzeitig in Spalte A eine 4 steht." gedacht.

Ich glaube ich brauche es aber genau andersherum: Es sollen alle unterschiedlichen, nicht leeren Zellen in Spalte A (Duplikate nur einmal zählen) gezählt werden, in denen gleichzeitig in Spalte B ein "a" steht.

 

[floq0r]

Eine andere Lösung mit PivotTable, etwas ugly aber sollte korrekt sein

 

[steve1da]

Zeile(X1:X10) ist die Angabe, wie groß der Bereich ist, der ausgewertet werden soll. Von X1:X10 sind es 10 Zeilen. Dein Bereich ist aber zB A2:A7, das wären 6 Zeilen. Dann würde man schreiben:
Zeile(X1:X6)
oder
Zeile(X2:X7)-1
Ob da jetzt X1:X10 oder K1:K10 oder A1:A10 steht ist völlig egal, dient wie gesagt nur als Zähler.
Bei dir wäre korrekt (für den Fall das "a", "b" und "c" in D2, D3, D4 stünden):

=SUMMENPRODUKT((VERGLEICH($B$2:$B$7&$A$2:$A$7;$B$2:$B$7&$A$2:$A$7;0)=ZEILE($B$2:$B$7)-1)*($B$2:$B$7=D2)*($A$2:$A$7<>""))

Ansonsten halt:

=SUMMENPRODUKT((VERGLEICH($B$2:$B$7&$A$2:$A$7;$B$2:$B$7&$A$2:$A$7;0)=ZEILE($B$2:$B$7)-1)*($B$2:$B$7="a")*($A$2:$A$7<>""))

Bei deinen anderen Versuchen: du kannst nicht einfach 1 für "a" nehmen und 2 für "b". Das sind völlig unterschiedliche Werte die in völlig unterschiedlichen Spalten stehen. Im Beispiel wird nach der Zahl 4 geschaut und das muss entweder "a" oder "b" oder "c" sein.

 

[XCatenaccioX]

Versuch' doch mal so:

1) Spalte "Zähler" ergänzen mit Einsen:

ID	Sparte	Zähler
1	a	1
2	b	1
3	c	1
1	b	1
1	a	1
3	a	1

2) Von oben links "ID" bis unten rechts "Zähler" mit der letzten "1" markieren und auf "Einfügen / PivotTabelle / Aus Tabelle bzw. Bereich" klicken.

3) In Pivot-Tabelle: "Filter" und "Spalten" leer lassen; bei "Zeilen" die "ID" (1. Stelle) und "Sparte" (2. Stelle) auswählen, bei "Werten" dann die "Summe von Zähler".

Ergebnis (habe nur andere Tabellenköpfe; Z für Zahl, B für Buchstabe u. a.):

In Spalte B stehen dann die ausgezählten, gesuchten Werte. Nicht schön, aber schnell und formellos. Mit mehr Pivot-Kenntnis vielleicht sogar tauglich formatierbar.

 

[steve1da]

@XCatenaccioX
Dein Ergebnis für A ist 3, richtig wäre 2.

 

[XCatenaccioX]

Sorry, stimmt. Auch die Zählerspalte wäre gar nicht erforderlich, es würde "Anzahl von B" bzw. "Anzahl von Sparte" genügen unten rechts in der Pivot. Hilft nicht weiter.

Ergänzung (6. September 2022)

In Spalte C:

In Spalte D einfach die Einsen, in Spalte E die Sparten (beides handschriftlich).

In Spalte F:

Auch nicht schön, diesmal richtig und ohne Pivot ^^

 

[-Slayer-]

@steve1da : VIELEN DANK! Deine Formel hat wunderbar funktioniert!
@floq0r : VIELEN DANK! Deine Pivotabelle konnte ich für eine andere Auswertung nutzen.

Vielen Dank an alle die mir geholfen haben. Ihr habt mir Stunden an Arbeit erspart!