Wie kann ich in Excel eine Formel =SUMME(ZÄHLENWENN( nach rechts in die Spalten kopieren und Excel in den Suchkriterien eine Zeile nach unten geht?
Hallo liebe Excel Experten,
Ich habe eine Tabelle von $B$1:$G$1000 jetzt möchte ich die Formel =SUMME(ZÄHLENWENN( anwenden.
Die erste Spalte H Zelle 1 wäre dann {=SUMME(ZÄHLENWENN($B$1:$G$1;$B1:$G1))}. dann kopiere ich das bis H1000
Spalte I Zelle 1 gleicher Bereich aber Suchkriterien $B2:$G2.
Wer könnte mir die Formel so umbauen das Excel beim kopieren nach rechts in Spalte I,J,K,L usw. in den Suchkriterien automatisch eine Zelle nach unten geht? Ist das überhaupt möglich oder kann man diese Aufgabe auch irgendwie anders lösen?
Für eure Hilfe wäre ich natürlich wieder überaus dankbar.
MfG Uwe
3 Antworten
B1:G1 Deine Prüfzahlen
B2:G1000 Deine Datensätze
H1: {=SUMME(ZÄHLENWENN($B2:$G1000;$B$1:$G$1))}
Runterziehen jetzt hast du die Anzahl der jeweiligen Treffer pro Zeile
Dann kannst du diese neue Spalte über Zählenwenn abfragen oder direkt über
{=WENN(SUMME(ZÄHLENWENN($B2:$G2;$B$1:$G$1))=5;"5er";"")}
eigentlich ja, jetzt ist ja die Frage mit der Abfrage von B2:G3 bis B1000:G1000 ohne dafür 1000 Spalten machen zu müssen :-)
Wieso 1000 Spalten? In Spalte H stehen doch jetzt jeweils die Anzahl Treffer aus Zeile 1. 3 heisst 3 zahlen stimmen überein usw. Wozu willst du jetzt noch 1000 Spalten füllen und mit was?
ich kann ja nur als Leihe denken, und die 1000 Spalten sind nicht das Problem, aber wie kopiert man nach rechts in die nächste Spalte bleibt dabei immer im gleichen Bereich und rutscht aber dabei automatisch in den Suchkriterien eine Zeile nach unten
@augsburgchris , Hallo, vielleicht könntest du dir mein Problem nochmal anschauen, ich habe mich eventuell wieder mal schlecht ausgedrückt, die Formel die wir haben passt für B1:G1 und zählt die Summe von allen den Zahlenreihen B2:G1000 wenn ich die Formel runter kopiere bis 1000 richtig? Aber was ist mit B2:G:2 das wäre eine neue Spalte kopiert bis 1000, richtig?
Was ich suche sind alle Zahlenreihen die untereinander 5 gemeinsame Zahlen haben.
Könnte man da nicht eventuell ein Makro schreiben, weil es am Ende ja doch 1000 Spalten ergibt.
Könnte ein Makro überhaupt so eine Aufgabe erledigen?
Wäre wirklich super von dir wenn du es dir noch mal anschauen würdest!
Danke
Ich hatte auch überlegt die 6 Zahlen in ihre 6 5er aufzuteilen und dann mit ZÄHLENWENNS zu vergleichen
Mir erschließt sich noch nicht, warum du das komplette Array abfragst.
Wenn in den Zellen B1:G1 die Zahlen 1;2;1;3;1;4 stehen dann ist dein Ergebnis 12
Nämlich 3+1+3+1+3+1
Diesen Sinn hab ich noch nicht ganz verstanden.
In welcher Zelle steht dein Ergebnis. Versuche mal völlig ohne Formeln zu beschreiben was du eigentlich vorhast. Screenshot wäre hilfreich um den Aufbau der Tabelle zu verstehen.
also eigentlich möchte ich ja heraus bekommen wie oft von Zeile 1 , also 6 Zahlen in 6 Zellen B:G , wieviele Zeilen es von 2:999 gibt in denen 5 Zahlen mit denen aus Zeile 1 übereinstimmen
Sprich du willst wissen wieviele 5er in einer Liste von Lottofeldern enthalten sind.
Ja so kann man es auch ausdrücken, ich habe es jetzt mal provisorisch so gelöst das ich die 6 Zahlen in 6 5er aufgeteilt habe und dann mit ZÄHLENWENNS aber mir wäre ehrlich gesagt eine Spalte mit einem Ergebnis lieber
Mir erschließt sich erst einmal nicht was du überhaupt machen möchtest. Was ist denn dein Suchkriterium in der Funktion "ZÄHLENWENN"? Wieso ist das Suchkriterium gleich dem Suchfeld? Was willst du erreichen?
Ich möchte alle Zeilen untereinander Zählen, natürlich kommt dann in jeder Spalte einmal eine Überschneidung wie im Beispiel B1:G1 die Summe ist dann natürlich 6.
Die Aufgabe besteht darin Bereich 1 also B1:G1 mit allen Bereichen von B2:G2 bis hin zu B1000:G1000 zu vergleichen also die Summe der Zahlen die übereinstimmen.
Mit der Funktion Zählenwenn wird das so aber nicht fuktionieren können, das diese ein einziges Suchmerkmal benötigt, nicht aber einen Bereich. Ich kann also in einem Bereich nach bestimmten Merkmalen suchen und dann zählen. Zum Beispiel kannst du im Bereich B1:G100 nach dem Eintrag "0" suchen. Als Ergebnis wird die Anzahl der Suchergebnisse gegeben. Du willst aber den Inhalt eines Bereiches (Zeile) in einem ganzen Feld suchen und dann zählen, das wird so nichts. So wie du es zählen willst ist es mit einfachen Mitteln nicht mehr realisierbar meiner Meinung nach. Hier müsstest du mit mehreren verschachtelten Wenn/und/oder - Verknüpfungen arbeiten. Vielleicht auch mit entspredchenden Hilfsspalten. Wenn du allerdings ein wenig VBA kannst, also die Makroprogrammierung in Excel nutzen kannst dann hast du viel mehr Möglichkeiten.
Also {=SUMME(ZÄHLENWENN($B1:$G$1;($B1:$G1))} ist das Ergebnis =6
Suchkriterien ohne $ bei den Zahlen kann ich also bis 1000 kopieren dabei bleibt B1:G1 als fester Bereich und das Suchkriterien zählt bis 1000.
jetzt müsste ich, um in der nächsten Spalte B2:G2 als festen Bereich zu bestimmen Zeile 1 {=SUMME(ZÄHLENWENN($B$2:$G$2;$B1:$G1))} einsetzen, aber das müsste ich dann in 1000 Spalten manuell umschreiben, also den festen Bereich immer in jeder Spalte um 1 tiefer setzen, viel viel Arbeit.
Makros kann ich leider nicht schreiben. Aber das was ich eigentlich suche in der Tabelle B1:G1000 ist, und wenn da jemand ein Makro hätte, sind alle Zeilen die untereinander mindestens 5 gemeinsame Zahlen haben
Ok danke das probiere ich gleich mal aus