Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1612to1616
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1612to1616
1612to1616
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zeilen zählen in denen mehrere Dinge stehen

Zeilen zählen in denen mehrere Dinge stehen
20.03.2018 09:14:23
Adrian
Guten Tag
Folgendes Problem: Ich habe eine grosse Liste wo ich herausfinden möchte in wievielen Zeilen ein gewisser Name und irgendein Name aus einer Liste ab Spalte C vorkommt.
Ich brauche also eine Formel, die jede Zeile ab Spalte D auf den Namen "John" absucht und absucht ob irgendein Name aus einer Liste ("AlleUser!A1:A1189") im selben Bereich vorkommt und +1 rechnet wenn beide Bedidinungen erfüllt werden. Also wenn John auf dieser Zeile ab Spalte D vorkommt und gleichzeitig ein Name aus der Liste ab Spalte D gefunden wurde. Und das für alle Zeilen (15 000+) macht.
Wäre es dann noch möglich, den Namen "John" aus der Liste der Namen automatisch auszuschliessen? Also "John" ist in Zelle A1 aber auch irgendwo in der Liste "AlleUser!A1:A1189". Ist es möglich, die zweite Bedingung so zu formulieren, dass der Inhalt der Zelle A1 innerhalb der Liste ignoriert wird?
Ich hoffe das ist verständlich und dass mir da jemand helfen kann :)
Vielen Dank im Voraus!

25
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zeilen zählen in denen mehrere Dinge stehen
20.03.2018 09:41:22
Daniel
Hi
kannst du mal eine abgespeckte Beispieldatei hochladen und dein manuell ausgezähltes Wunschergebnis für diese Beispieldatei nennen?
Deine Beschreibung ist für mich zumindest noch nicht ausreichend, um das Problem zu verstehen.
Gruß Daniel
AW: Zeilen zählen in denen mehrere Dinge stehen
20.03.2018 10:29:27
Adrian
Hallo
Vielen Dank für die raschen Antworten!
Ich habe mal eine Beispieldatei erstellt und hoffe das veranschaulicht das Ganze: https://www.herber.de/bbs/user/120538.xlsx
Mein Wunschergebnis wurde manuell auf dem Blatt "Auswertung" in Spalte C eingetragen.
Vielen Dank Werner für die Formel ich bin noch dabei diese zu testen.
Anzeige
AW: Zeilen zählen in denen mehrere Dinge stehen
20.03.2018 10:48:16
Daniel
Hi
tut mir leid, ich verstehe nicht was du genau zählen willst.
Gruß Daniel
AW: Zeilen zählen in denen mehrere Dinge stehen
20.03.2018 11:02:56
Adrian
Hallo Daniel
Kein Problem! Ich versuche es deutlicher zu machen:
In der Spalte "Gesamte Anzahl" zähle ich jede Zeile in der der entsprechende Name mind. einmal vorkommt. Also die Anzahl der Zeilen in der z.B. John vorkommt.
In der Spalte "Problematische Anzahl" möchte ich jetzt die Anzahl Zeilen zählen, in der John vorkommt und noch ein weiterer Name, der auf der Liste "AlleUser" steht. Ich möchte unterscheiden können, ob in einer Zeile nur John vorkommt oder John + noch Jemand.
Z.B. im Blatt "Rohdaten" Darf die Zeile 4 nicht gezählt werden, da nur John vorkommt. Die Zeile 2 sowie die Zeile 8 müssen jedoch gezählt werden, da in beiden John vorkommt und noch Bob ungeachtet der Reihenfolge der Namen etc.
Ich hoffe das macht es deutlicher.
Nichtdestotrotz vielen Dank fürs Reinschauen.
Beste Grüsse
Adrian
Anzeige
AW: Angaben sind nicht zwar nicht eindeutig ...
20.03.2018 09:44:33
...
Hallo Adrian,
... so nicht, auf was sich +1 beziehen und wie weit die Auswertung ab Spalte D gehen soll. Eine kleine Beispieldatei wäre sinnvoll gewesen. Ich hab aber mal meine Interpretation Deiner Angaben vorgenommen.
Ich werte bis Spalte Z aus und als erste Zeile die Zeile 2 in A2 und diese Formel kopiere nach unten.
Bei 15000 auszuwertenden Zeilen könnte jedoch mit folgender Formel in A2 der PC wohl ins "Schwitzen" kommen. Teste mal.
=WENN(ANZAHL2(D2:I2)=0;"";MAX(A$1:A1)+(SUMME(INDEX(ZÄHLENWENN(D2:Z22;AlleUser!A$1:A$1189);))>1)* _
(ZÄHLENWENN(D2:Z2;A$1)>0))

Eine zweite Bedingung ist durch die Formel gleich mit berücksichtigt.
Gruß Werner
.. , - ...
Anzeige
AW: Angaben sind nicht zwar nicht eindeutig ...
20.03.2018 10:47:18
Adrian
Hallo Werner
Vielen Dank nochmal für die Formel. Leider funktioniert diese nicht für meine Zwecke, da ich das Ergebnis nicht in der grossen Liste (die mit 15000+ Zeilen) benötige. Ich habe nun eine kleine Beispieldatei hochgeladen, die das Ganze veranschaulichen soll: https://www.herber.de/bbs/user/120538.xlsx
Das Ergebnis soll schlussendlich in einem eigenen Blatt stehen und für jeden Namen ausgerechnet werden.
Beste Grüsse
Adrian
AW: für Jack gibt es aber ein anderes Ergebnis ...
20.03.2018 11:15:23
...
Hallo Adrian,
... in C2: 

=WENN(A2=0;"";SUMME(INDEX((ZÄHLENWENN(BEREICH.VERSCHIEBEN(Rohdaten!B$2:GR$2;ZEILE(A$1:A$200)
-1;;);A2)>0)-(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Rohdaten!C$2:GR$2;ZEILE(A$1:A$200)-1;;);"*")=0)
*(Rohdaten!B$2:B$201=A2);)))
Gruß Werner
.. , - ...
Anzeige
AW: für Jack gibt es aber ein anderes Ergebnis ...
20.03.2018 11:20:42
Adrian
Wow das ist ja fantastisch! Ja ich habe mich blöderweise verzählt. Deine Daten stimmen. Unglaublich wie schnell du das gelöst hast. Ich werde das noch auf meine Daten anwenden aber es scheint perfekt zu funktionieren.
Vielen Dank
AW: für Jack gibt es aber ein anderes Ergebnis ...
20.03.2018 11:28:12
Adrian
Wow das ist ja fantastisch! Ja ich habe mich blöderweise verzählt. Deine Daten stimmen. Unglaublich wie schnell du das gelöst hast. Ich werde das noch auf meine Daten anwenden aber es scheint perfekt zu funktionieren.
Vielen Dank
AW: freut mich! owT ... und oT: @ Luschi ...
20.03.2018 12:45:17
...
Hallo Luschi,
... wie Du hier sehen kannst, nutze ich schon auch manchmal die Funktion BEREICH.VERSCHIEBEN().
Allerdings ist diese Formel hier wohl meine erste wirklich eigene BEREICH.VERSCHIEBEN()-Formel-Kreation.
Gruß Werner
.. , - ...
Anzeige
AW: freut mich! owT ... und oT: @ Luschi ...
20.03.2018 14:06:24
Adrian
Hallo
Ich habe noch ein bisschen damit rumprobiert und etwas entdeckt. Wenn ich einen Namen in die Rohdaten hinzufüge, der nicht in der Liste AlleUser ist wird er trotzdem miteinbezogen: https://www.herber.de/bbs/user/120546.xlsx
Leider funktioniert es doch nicht ganz wie es müsste so wies scheint. Namen die nicht in der Liste sind sollten ignoriert werden idealerweise.
Beste Grüsse
Adrian
AW: dann wohl mit einer Hilfsspalte ...
20.03.2018 16:43:51
...
Hallo Adrian,
... in Rohdaten GS2: =SUMME(INDEX(ZÄHLENWENN(C2:GR2;Auswertung!A$2:A$9);)) und diese nach unten kopieren.
Und in E2:
=SUMME(INDEX((ZÄHLENWENN(BEREICH.VERSCHIEBEN(Rohdaten!C$2:GR$2;ZEILE(A$1:A$200)-1;;);A2)>0)
*(Rohdaten!B$2:B$201A2)+(Rohdaten!B$2:B$201=A2)*(Rohdaten!GS$2:GS$201>0);))
Gruß Werner
.. , - ...
Anzeige
AW: dann wohl mit einer Hilfsspalte ...
21.03.2018 15:49:46
Adrian
Hallo Werner
Nochmals tausend Dank für die tolle Hilfe! Jetzt scheint alles so zu funktionieren wie ich es mir vorgestellt hatte.
Beste Grüsse
Adrian
AW: prima, freut mich auch owT
22.03.2018 08:34:21
...
Gruß Werner
.. , - ...
AW: prima, freut mich auch owT
22.03.2018 11:05:23
Adrian
Hallo nochmals...
Ich habe leider noch ein Problem entdeckt. Ich habe die Beispieldatei angepasst um das Problem aufzuzeigen: https://www.herber.de/bbs/user/120600.xlsx
Es ist nun doch das Gleiche Problem, das dazu führte das Jack falsch gezählt wurde (Ich hatte mich ursprünglich doch nicht verzählt). Es wird nämlich ein Wert zuviel gezählt. Ich habe es noch in der Beispieldatei mit dem Namen Werner versucht deutlich zu machen. Kann diese Fehlzählung vermieden werden?
Beste Grüsse
Adrian
Anzeige
AW: das sind neue Bedingungen ...
22.03.2018 12:28:39
...
Hallo Adrian,
... wenn ich Deine neue Datei richtig interpretiere dürfen die Namen in C:GR auch nur gezählt werden, wenn in Spalte B ein Name steht. Oder? Gibt es noch weitere neue Bedingungen?
Gruß Werner
.. , - ...
AW: das sind neue Bedingungen ...
22.03.2018 13:04:28
Adrian
Hallo Werner
Mhm, nein. Ich meinte das so: Im Blatt "Auswertung" sollte das Ergebnis für Werner 0 sein, da er ja im Blatt "Rohdaten" jeweils alleine ist (mit Ausnahme von Klaus, der aber nicht gezählt werden darf). Das Problem, das ich aber ursprünglich vergessen hatte, ist, dass Werner manchmal in Spalte B nicht steht dafür in Spalte C oder D etc. Problematisch wird es zudem, glaube ich, wenn z.B. Werner in Spalte C und dann noch Jack in Spalte D steht dafür nichts in Spalte B. Dort müsste es trotzdem +1 für Werner zählen. In der Formel in der Hilfsspalte müsste also die Bedingung hinzugefügt werden, dass der gesuchte Name von der Namensliste (Auswertung!A$2:A$9) ignoriert wird. Wenn ich das richtig verstehe...
Die Bedingung ist: Ich möchte alle Zeilen zählen in denen ein gewisser Name vorkommt und mind. einer der Namen aus der Namensliste.
Ich hoffe das hilft.
Beste Grüsse
Adrian
Anzeige
AW: kann ich so nicht ganz nachvollziehen ...
22.03.2018 13:56:20
...
Hallo Adrian,
... wenn ich Deine neue Datei und Angaben auswerte erhalte ich für John, Jack, Jones und Werner Deine Vorgabewerte aber für Bob nur 5 und Daniel nur 2.
Prüfe also noch mal und zeige mir für die beiden durch farbiges Anlegen der Zellen auf, welche Du gezählt haben willst.
Gruß Werner
.. , - ...
AW: erklärungsbedürftig ...
22.03.2018 14:52:53
...
Hallo Adrian,
... wieso zählen Daniel und Bob Zeile 18 obwohl in B18 kein Name steht? Das war doch eine Bedingung oder hab ich das falsch aufgefasst? Und wenn dann so wie Du schreibst, warum zählt diese Zeile nicht für Jack? Die beiden Jacks in Zeile 11 sollen nicht zählen? Weil kein anderer in der Zeile gelistet ist?
Wieso das dann so auch ist, gäbe es insgesamt nur einen Jack der zählt und nicht zwei.
Gruß Werner
.. , - ...
Anzeige
AW: erklärungsbedürftig ...
22.03.2018 15:03:21
Adrian
Hallo Werner
Ja genau, es ist keine Bedingung, dass in Spalte B bei "Rohdaten" etwas stehen muss. Zeile 18 zählt für Jack auch. Genau, Zeile 11 zählt nicht für Jack weil kein anderer in der Zeile gelistet ist. Trotzdem gäbe es für Jack 2 Zählungen aufgrund von Zeile 9 und Zeile 18.
Beste Grüsse
Adrian
AW: nun ist es eindeutig ...
22.03.2018 17:25:24
...
Hallo Adrian,
... in Rohdaten!GS2 folgende neue Hilfsspalten-Formel:
=N(SUMME(INDEX(N(ZÄHLENWENN(B2:I2;Auswertung!A$2:A$9)>0);))>1)
und in Auswertung!E2: 
=SUMME(INDEX((ZÄHLENWENN(BEREICH.VERSCHIEBEN(Rohdaten!B$2:NX$2;ZEILE(A$1:A$200)-1;;);A2)>0) *Rohdaten!GS$2:GS$201;))
Gruß Werner
.. , - ...
AW: nun ist es eindeutig ...
26.03.2018 12:54:49
Adrian
Hallo Werner
Vielen Dank wiedermal :) Ich habe bereits damit rumgespielt und getestet und jetzt scheint es wirklich tadellos zu funktionieren.
Sorry, dass das Erklären nicht so ganz geklappt hat aber nochmals vielen Dank für deine Zeit und Geduld.
Falls du noch Lust hast, hätte ich noch eine weitere Herausforderung. Das wäre jetzt aber nur noch nice to have und nicht mehr unbedingt nötig. Ausgangslage wieder dieselbe aber neu wäre, dass die Zeile nur gezählt werden darf, falls der Name1 und ein weiterer Name2 aus einer bestimmten Liste (Tabellenblatt "SpezialUser") vorhanden sind (Name1 ist nicht unbedingt in der Liste vorhanden). Habe die Beispieldatei angepasst um es zu veranschaulichen: https://www.herber.de/bbs/user/120676.xlsx
Also nur noch zählen wenn in der Zeile der Name A2 + irgendein Name aus "SpezialUser" ist.
Hoffe das ist eindeutig :)
Beste Grüsse
Adrian
AW: so wird es eine andere Auswertung ...
26.03.2018 17:11:19
...
Hallo Adrian,
... bezogen auf Deine Beispieldaten bis Spalte H folgende Hilfsspalten, in Rohdaten I2:
=(SUMME(INDEX(N(ZÄHLENWENN(B2:H2;SpezialUser!A$1:A$9)>0);))>0)+(SUMME(INDEX(ZÄHLENWENN(B2:H2; SpezialUser!A$1:A$9);))>1)
und dann in Auswertung E2:
=SUMME(INDEX((ZÄHLENWENN(BEREICH.VERSCHIEBEN(Rohdaten!B$2:H$2;ZEILE(A$1:A$35)-1;;);A2)>0)*
(WENN(ZÄHLENWENN(SpezialUser!A$1:A$9;A2)=0;INDEX(Rohdaten!I$2:I$36>0;);INDEX(Rohdaten!I$2:I$36=2;)));))
Gruß Werner
.. , - ...
AW: so wird es eine andere Auswertung ...
27.03.2018 14:25:43
Adrian
Hallo Werner
Ja genau, aber diese zusätzliche Auswertung hilft uns eine weitere sehr nützliche Perspektive auf unseren Datensalat zu werfen. Vielen Dank, hat perfekt funktioniert!
Beste Grüsse
Adrian

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige