Excel Hilfestellung für eine Formel (evt. Index / Vergleich / Zählen / Anzahl)?

... komplette Frage anzeigen

6 Antworten

Kürzer bekomme ich das nicht. Die Länge von max 1000 Zeilen musst Du prüfen, und ob die Überschriften in der ersten Zeile von A bis Z stehen.
Statt ABC und XYZ fest in die Formel zu schreiben, kannst natürlich auch auf eine Zelle verweisen.




=WENN(SUMMENPRODUKT(((BEREICH.VERSCHIEBEN((
Daten!A2:A1000);0;VERGLEICH("ABC";Daten!A1:Z1;0)-1)=B1)(BEREICH.VERSCHIEBEN((Daten!A2:A1000);0;VERGLEICH("XYZ";Daten!A1:Z1;0)-1)<>"")))=0;"";SUMMENPRODUKT(((BEREICH.VERSCHIEBEN((Daten!A2:A1000);0;VERGLEICH("ABC";Daten!A1:Z1;0)-1)=B1)(BEREICH.VERSCHIEBEN((Daten!A2:A1000);0;VERGLEICH("XYZ";Daten!A1:Z1;0)-1)<>""))))


Summenprodukt ermittelt die beiden Bedingungen:
Spalte mit ABC muss den Wert aus B1 enthalten
Spalte mit XYZ nicht leer sein

Weil Du anscheinend nicht fest hinterlegen kannst, wo man die beiden Spalten findest, kommt beides Mal bereich.verschieben ins Spiel. Mit Vergleich wird die Überschrift gesucht und die Spalte A entsprechend weit verschoben.

Für die Anforderung, dass 0 nicht erscheinen kann, fällt mir in diesem Fall nichts eleganteres ein als die Anzahl in der Wenn-Abfrage zwei Mal durchzuführen, also zunächst für die Prüfung ob 0 rauskommt (dann kein Ergebnis) und wenn >0 dann dieses Ergebnis anzuzeigen. Dadurch wird das ganze halt recht lang.


Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Ninombre
14.10.2016, 10:32

ich hasse den GF Editor: Hier noch mal in kopierbarer Form:

=WENN(SUMMENPRODUKT(((BEREICH.VERSCHIEBEN((Daten!A2:A1000);0;VERGLEICH("ABC";Daten!A1:Z1;0)-1)=B1)*(BEREICH.VERSCHIEBEN((Daten!A2:A1000);0;VERGLEICH("XYZ";Daten!A1:Z1;0)-1)<>"")))=0;"";SUMMENPRODUKT(((BEREICH.VERSCHIEBEN((Daten!A2:A1000);0;VERGLEICH("ABC";Daten!A1:Z1;0)-1)=B1)*(BEREICH.VERSCHIEBEN((Daten!A2:A1000);0;VERGLEICH("XYZ";Daten!A1:Z1;0)-1)<>""))))
2

Da sich deine Tabelle Daten ja immer wieder erneuert aber wenn ich das richtig verstanden habe immer die gleiche Aufmachung hat, sprich es sind immer die Spalten A und B belegt (weil die Überschriften ja gleich bleiben), würde ich das Thema Hilfspalte in Tabellenblatt Auswertung nutzen.

Wenn das für dich eine Option wäre, setze mal in Zelle C1 im Tabellenblatt Auswertung folgende Formel ein und ziehe sie runter. Da ich nicht weiß wieviele Vorgänge du da immer so drin hast musst du sie so weit ziehen wie du auch Vorgänge im Tabellenblatt Daten hast.

=WENN(UND(Daten!A:A=Auswertung!$B$1;Daten!B:B>0);1;0)

Hier wird jetzt in jeder Zeile eine 1 auftauchen wo auch im Tabellenblatt Daten in Spalte A der Suchbegriff aus Zelle B1 gefunden wird (zb.DM) und die Zelle in Vorgang nicht leer ist.

Die Hilfsspalte C kannst du dann einfach ausblenden lassen.

In der Auswertung machst du jetzt in Zelle A1 wo du die Anzahl ausgegeben haben willst folgende Formel:

=WENN(SUMME(C:C)=0;"";SUMME(C:C))

Dann wird dort auch nur eine Zahl angezeigt wenn auch ein Treffen kommt, sonst bleibt die Zelle leer.

Hilft dir das so weiter?

Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Susino
14.10.2016, 11:25

Danke für den Vorschlag. Leider bleibt Spalte A nicht Spalte A ;) - sondern kann sich beim Aktualisieren auf einmal in Spalte D verwandeln - nur die Spaltenüberschriften ändern sich nicht --> Spaltenüberschrift "Vorgänge" in Spalte A befindet sich nach Aktualisierung in Spalte D mit der Spaltenüberschrift "Vorgänge"

0
Kommentar von schmiddi1967
14.10.2016, 11:47

Ah ok dann habe ich es doch falsch verstanden. :))

0

Wenn Du akzeptieren kannst, dass das Ergebnis nicht in Zeile 1 steht (würde wahrscheinlich Zirkelbezug ergeben, hab letzteres nicht getestet), dann

=ANZAHL2(INDIREKT(ADRESSE(2;VERGLEICH("Spaltenüberschrift";1:1;0))&":"&ADRESSE(999;VERGLEICH("Spaltenüberschrift";1:1;0))))

Die Unterdrückung der 0-Darstellung machst du am besten per Bendef Format. (Strg+1, Zahlen, Bendef: 0;-0;"")

Sollte jedoch die Null wegen einer Formel stören oder das Ergebnis unbedingt in Zeile 1 stehen müssen, würde es um einiges komplizierter, das führe ich aber nur auf Nachfrage aus.

Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Iamiam
14.10.2016, 12:01

War gar nicht so kompliziert,

habs jetzt doch noch schnell gemacht für Formel in A1 und Unterdrückung 0, stattdessen ""

=WENNFEHLER(1/(1/ANZAHL2(INDIREKT(ADRESSE(2;VERGLEICH("Spaltenü'schrift";B1:Z1;0)+1)&":"&ADRESSE(999;VERGLEICH("Spaltenü'schrift";B1:Z1;0)+1))));"")

NB: das wird von zB Isttext(A1) mit WAHR beantwortet, also nicht als Zahl oder echtes Leer gewertet: auch istleer(A1) liefert FALSCH, nur =A1="" liefert WAHR!

Es gibt (gab bisher, xl2010) ja kein WennNull(). Aber 1/0 ergibt Fehler, und das nochmals al 1/ wieder den ursprünglichen Wert (aber ggf eben Fehler), so dass man WennFehler() zum Test auf 0 (und dann alternatives Zeichen) einsetzen kann, ohne lange Formeln wiederholen zu müssen.

1

Also, deine Erklärung macht mich echt fertig :D

Ich versteh leider echt nichts von dem was du willst, nur ein paar Fetzen machen für mich Sinn...

Kannst du keine abgeänderte Beispieldatei mit hoch laden und den Link dazu posten?

Wäre glaub ich einfacher  (Am besten in die Datei nochmal kurz schreiben, was du wohin haben möchtest unter welchen Bedingungen)

Grüße,

iMPerFekTioN

Antwort bewerten Vielen Dank für Deine Bewertung

Hi,

versuch mal die Funktion "Zählenwenn"

müsste wenn ich das richtig verstehe so aussehen:

=ZÄHLENWENN(Tabellenblatt2!A1:A5;Tabellenblatt1A1)

Gruss

Yankee

Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Iamiam
14.10.2016, 12:12

ja, wenn man die Spalte vorher schon wüsste...

0

Ergänzung Bild

Antwort bewerten Vielen Dank für Deine Bewertung

Was möchtest Du wissen?