#DIV/0! bei 1/ZÄHLENWENN abfangen

[Henning M]

Hallo zusammen.

In einer Tabelle stehen in Spalte 1 Werte, die einmalig oder mehrmals vorkommen. Zu diesen Werten können sich in Spalte 2 und 3 Markierungen befinden.

	D	E	F
288	Spalte1	Spalte2	Spalte3
289	a	x
290	a	x	x
291	a		x
292	b	x
293	b		x
294	c
295	d	x
296	d	x
297	e
298	f	x
299	f	x
300	f
301			x

	D	E	F
303	6	4	#DIV/0!

(ignoriert erst einmal D303)

Zelle	Formel
E303	=SUMMENPRODUKT(FILTER(1/ZÄHLENWENNS(Tabelle1[Spalte1];Tabelle1[Spalte1];Tabelle1[Spalte2];Tabelle1[Spalte2]);TEILERGEBNIS(3;INDIREKT(WECHSELN(ADRESSE(1;SPALTE(Tabelle1[Spalte2]);4);"1";"")&ZEILE(Tabelle1[Spalte2])));0))
F303	=SUMMENPRODUKT(FILTER(1/ZÄHLENWENNS(Tabelle1[Spalte1];Tabelle1[Spalte1];Tabelle1[Spalte3];Tabelle1[Spalte3]);TEILERGEBNIS(3;INDIREKT(WECHSELN(ADRESSE(1;SPALTE(Tabelle1[Spalte3]);4);"1";"")&ZEILE(Tabelle1[Spalte3])));0))

Gezählt werden soll (in E303 bzw. F303), bei wievielen Werten der Spalte 1 sich Markierungen jeweils in Spalte 2 und 3 befinden. Hier im Beispiel: In Spalte 2 sind (beliebig viele) Markierungen bei a, b, d und f vorhanden, demnach 4 Fälle; in Spalte 3 sind (beliebig viele) Markierungen bei a und b vorhanden, demnach 2 Fälle. Also egal, wieviele Markierungen ein Wert hat, es zählt höchstens einmal je Spalte.
Die Ergebnisse sollten filtersensitiv sein (ausgeblendete Zellen nicht mitzählen). Das funktioniert.
Außerdem sollen Zeilen ohne Wert in Spalte 1 ignoriert werden. Das kann ich Excel bisher nicht abringen. Da 1/ZÄHLENWENNS die leere Zelle D301 nicht mag (verständlicherweise), beschwert sich Excel.

Mit WENN kann ich leere Zellen wie hier D301 übergehen, bekomme es aber nicht passend in die Formeln hinein.
Hat jemand eine Idee (ohne Pivot und VBA)?

Nun zu D303: Zusätzlich wäre es noch ganz nett, die Summe der Fälle aus Spalte 2 und 3 (nicht die Summe der Markierungen) anzuzeigen. Freilich geht das auch mit =SUMME(E303;F303), ich möchte das aber gerne direkt ohne Weiterverwertung von Zwischenergebnissen haben. Auch da scheitert es noch. Mit AGGREGAT habe ich schon herumprobiert. Nun ja, ich bin kein Profi …

[LCohen]

D303[:F303]: =ZEILEN(EINDEUTIG(FILTER($D289:$D301;D289:D301<>"")))

[Henning M]

Danke schön.
Funktioniert aber leider nicht, denn damit wird in F303 „3“ angezeigt. Soll ist „2“: Wenn Spalte 1 leer ist, soll die Zeile ignoriert werden. Filter und ausgeblendete Zeilen werden damit leider auch mitgezählt.
War vielleicht etwas umständlich von mir formuliert. Ich schreibe nochmal alles neu und hoffe, es ist diesmal nicht so umständlich:

	D	E	F
288	Spalte1	Spalte2	Spalte3
289	a	x
290	a	x
291	a		x
292	b	x
293	b		x
294	c
295	d	x
296	d	x
297	e
298	f	x
299	f	x
300	f
301			x

	D	E	F
303		4	2

E303 (Ergebnis im Bsp: 4):

• Wenn Buchstabe in Spalte 1 und x in Spalte 2, dann zählen.
  
• Diese Kombination nur einmalig zählen, auch wenn sie mehrfach vorkommt.
  
• Wenn Spalte 1 leer, dann die Zeile ignorieren (selbst wenn x in Spalte 2).
  
• Wenn die Zeile nicht sichtbar ist (wegen Filter oder „Zeile ausblenden“), dann die Zeile ignorieren (also nach dem Prinzip AGGREGAT1/3/5/7).
  

F303 (Ergebnis im Bsp: 2):

• Wenn Buchstabe in Spalte 1 und x in Spalte 3, dann zählen.
  
• Diese Kombination nur einmalig zählen, auch wenn sie mehrfach vorkommt.
  
• Wenn Spalte 1 leer, dann die Zeile ignorieren (selbst wenn x in Spalte 3).
  
• Wenn die Zeile nicht sichtbar ist (wegen Filter oder „Zeile ausblenden“), dann die Zeile ignorieren (also nach dem Prinzip AGGREGAT1/3/5/7).
  

Und D303 war von mir falsch formuliert. Es soll nicht die Summe von E303 und F303 sein, sondern D303 soll die Spalten 2 und 3 als einen Kriterienbereich ansehen. Es ist sozusagen eine Zusammenfassung.
D303 (Ergebnis im Bsp: 4, nicht 6):

• Wenn Buchstabe in Spalte 1 und x in Spalte 2 und/oder Spalte 3, dann zählen.
  
• Diese Kombination nur einmalig zählen, auch wenn sie mehrfach vorkommt.
  
• Wenn Spalte 1 leer, dann die Zeile ignorieren, auch wenn in Spalte 2 und/oder Spalte 3 eine Markierung vorhanden ist.
  
• Wenn die Zeile nicht sichtbar ist (wegen Filter oder „Zeile ausblenden“), dann die Zeile ignorieren (also nach dem Prinzip AGGREGAT1/3/5/7).
  

[LCohen]

Befasse Dich mit dem Formulieren von mehreren Kriterien: UND = *, ODER = +

Das ist bei FILTER nicht anders als bei SUMMENPRODUKT.

Weiter kaue ich Dir das nicht vor.

[Luschi]

Hallo Henning,

hier mal eine Excel Power Query Lösung von mir.

Gruß von Luschi
aus klein-Paris

[BoskoBiati]

Hi,

oder vielleicht so:

Code:

=ANZAHL2(EINDEUTIG(FILTER($D$288:$D$300;($D$288:$D$300<>"")*(E288:E300="x"))))

[Henning M]

Hallo, Luschi und Edgar.

Danke für Eure Antworten.
Joa, Power Query – da fragen mich meine Kollegen: „Kann man das essen?“

Durch LCohens Klaps auf den Hinterkopf funktioniert diese Formel:
E303: =ZEILEN(EINDEUTIG(FILTER(Tabelle1[[Spalte1]:[Spalte2]];(Tabelle1[Spalte1]<>"")*(Tabelle1[Spalte2]<>"");0)))

Damit werden aber nun wieder ausgeblendete Zellen mitgezählt. Es müßte also irgendwie AGGREGAT oder TEILERGEBNIS damit verbunden werden. Das habe ich bisher nicht geschafft.
Ich habe mal eine Beispieldatei angehangen. Vielleicht mag ja mal jemand probieren.

[LCohen]

Das ist ein Bruch zwischen zwei Welten. So etwas kommt mir nicht in mein Blatt.

Statt dass ich manuell ausblende, kann ich das besser mit einer Ankreuzspalte und die einfach in =FILTER() als Kriterium übernehmen. 
Seitdem es FILTER (u.a.) gibt, ist die Krücke AGGREGAT endlich gegenstandslos. 
TEILERGEBNIS nimmt man schon deshalb nicht, weil es auf bestimmte Funktionen beschränkt ist.

[Henning M]

Mit „ausblenden“ meine ich vor allem das Filtern. Das ganze Ding ist ja als Tabelle formatiert. Die Tabelle ist viel größer als hier gezeigt. Sie hat viel mehr Einträge und hat noch andere Spalten, in denen bestimmte Eigenschaften der Objekte eingetragen sind. Diese Eigenschaftsspalten müssen nicht in Formeln berücksichtigt werden, aber die Filter muß man nutzen können. Wenn Filter angewendet werden, dann kommt es zu ausgeblendeten Zeilen. Die Formelergebnisse sollen dann nur die angezeigten Zeilen berücksichtigen. Die Ergebniszeile reicht dafür nicht, weil die sich nicht auf die x-Spalten bezieht (also die Spalten, die hier in der Beispieltabelle gezeigt sind). Die Ergebniszeile taugt auch nicht als Zwischenschritt, weil die Doppelten damit nicht berücksichtigt werden können. Mit einer Ankreuzspalte wird es zur Friemelei, z. B. alle Objekte mit Eigenschaft XYZ herauszufiltern.

[LCohen]

Ein manuelles Ausblenden (z.B. für Angebote, Rechnungen oder Berichte) ist nicht reproduzierbar, außerdem höchst gefährlich bei Verträgen. Damit kommst Du in manchen Bereichen sogar gesetzlichen Pflichten nicht nach, auf jeden Fall aber interner Dokumentation (falls Du nicht gerade von jedem gültigen und versendeten Zustand vorher ein PDF anfertigst). Besser ist es, die Zeilen systematisch zu behandeln, also typisiert zu filtern.

Bsp.: Ich habe eine Jahresabschlussdatei gebaut in Excel. Mit Autofilter blende ich z.B. 3 verschiedene Informationen ein, nämlich a) Erläuterungsteil mit Überschriftenhierarchie ohne Summen außer unmittelbar, b) nur dessen unmittelbaren Positionen mit Wert und c) eine Bilanz/GuV mit Überschriftenhierarchie und deren hierarchischen Summen.