SUMMENPRODUKT mit leeren Zellen

[LuckyJoe]

Hallo zusammen,

ich nutze in einem Tabellenblatt eine Formel, um bestimmte "Status-Zustände" innerhalb eines benannten Bereiches zu zählen. Bei der Formel ist es mir wichtig, dass die Anzahl auch dann stimmt, wenn nur gefilterte Werte angezeigt werden. Das bekomme ich sehr gut mit den nachfolgenden Formeln dargestellt. Wie muss die Formel in "B21" lauten, wenn ich nur die leeren Zellen zählen möchte?

A B 1 Status 2 S 3 S 4 5 V 6 S 7 8 S 9 S 10 V 11 V 12 S 13 S 14 S 15 S 16 S 17 S 18 19 Status "S" 11 20 Status "V" 3 21 Status "" 0
Name Bezug Status =Tabelle1!$A$2:$A$17	Zelle Formel B19 =SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("A" & ZEILE(Status)))*(Status="S")) B20 =SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("A" & ZEILE(Status)))*(Status="V")) B21 =SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("A" & ZEILE(Status)))*(Status=""))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Klaus-Dieter]

Hallo Joe,

warum so ein Aufwand? So geht es:
Tabelle2

	A	B
1	Status
2	S
3	S
4
5	V
6	S
7
8	S
9	S
10	V
11	V
12	S
13	S
14	S
15	S
16	S
17	S
18
19	Status "S"	11
20	Status "V"	3
21	Status ""	2

Formeln der Tabelle

Zelle Formel B19 =SUMMENPRODUKT((A$2:A$17="S")*1) B20 =SUMMENPRODUKT((A$2:A$17="V")*1) B21 =SUMMENPRODUKT((A$2:A$17="")*1)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[LCohen]

ungetestet (Vorschlag, weiß nicht, ob's geht):

B21: =SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("A" & ZEILE(Status)))*(0&Status="0"))

[shift-del]

Moin

Mit TEILERGEBNIS(3;...) kann das nicht funktionieren weil das für die Funktion ANZAHL2() steht.
Versuch mal ANZAHL2() auf ein leere Zelle anzuwenden.
Eine Websuche hat ergeben dass es mit einer zusätzlichen Spalte funktioniert, die in jeder Zeile einen Wert hat.
Beispiel.

[LuckyJoe]

Hallo,

@Klaus-Dieter: Danke, die Formel ist mir bekannt, aber die berücksichtigt halt nicht gefilterte Werte: würde ich z.B. nach "S" filtern, müsste bei Status = "" 0 herauskommen.

@LCohen: Danke auch dir, aber das funktioniert nicht, auch wenn ich die Formel ändere auf
B21: =SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("A" & ZEILE(Status)))*(0 & Status=""))

[LCohen]

Diese Formel habe ich Dir nie gegeben. Schreibst Du Tipps etwa ab (statt zu kopieren)? - Meine Formel ist aber trotzdem falsch.

[WillWissen]

Moin Jörg,

ich bin mir jetzt nicht ganz sicher, ob ich dich richtig verstanden habe; leere Zellen kannst du doch mit ANZAHLLEEREZELLEN() ermitteln. Es werden auch die leeren Zellen (alle!) eines Bereichs angezeigt, wenn dieser gefiltert ist.

Bereich ungefiltert:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
1	Überschrift	3
2	S
3	V
4
5	V
6	V
7	S
8	S
9
10	V
11	S
12
13	V
14	S
15	S
16	V

Zelle	Formel
B1	=ANZAHLLEEREZELLEN(A1:A16)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Bereich nach V gefiltert:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
1	Überschrift	3
3	V
5	V
6	V
10	V
13	V
16	V
17

Zelle	Formel
B1	=ANZAHLLEEREZELLEN(A1:A16)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[LCohen]

Klaus-Dieter und WillWissen: Es geht um Nichtberücksichtigung ausgeblendeter Zeilen.

[LuckyJoe]

Hallo,

@LCohen: ich hatte deine Formel kopiert und getestet: funktioniert nicht, was ich dir mit dem Satz "Danke auch dir, aber das funktioniert nicht" mitgeteilt habe.
Daraufhin habe ich deine Formel abgeändert (weil in "Status" keine "0" vorkommen kann) und dir mitgeteilt "auch wenn ich die Formel ändere auf ..." und dann o.g. Formel eingestellt.

@shift-del: Danke dir, das klappt wie in deinem verlinkten Beispiel gezeigt wie gewünscht! Da in meiner Tabelle andere Spalten vorhanden sind, die immer gefüllt sind, kann ich die Formel entsprechend anpassen. Damit habe ich für dieses Thema die gewünschte Lösung.

@Günter: Danke auch dir, aber bei der Filterung der Tabelle will ich gerade zählen, wieviel leere Zellen (in den gefilterten Daten) noch vorhanden sind (die Filterung erfolgt in einer anderen Spalte).

[LCohen]

@LCohen: ich hatte deine Formel kopiert und getestet: funktioniert nicht, was ich dir mit dem Satz "Danke auch dir, aber das funktioniert nicht" mitgeteilt habe.
Daraufhin habe ich deine Formel abgeändert (weil in "Status" keine "0" vorkommen kann) und dir mitgeteilt "auch wenn ich die Formel ändere auf ..." und dann o.g. Formel eingestellt.

Meine Antwort ist falsch; das habe ich hinterher geschrieben. Ich hatte aber vorher auch geschrieben: Ungetestet.

Wenn ich aber (0&Status="0") schreibe, dann ist das Ernst. Denn dann wird "0" statt "0S" oder "0V" ausgewertet, und Du kommst um Leerzellen herum. Hat hier leider nicht funktioniert - ich war zu faul, es selbst zu testen. Außerdem: shift-del's richtiger Hinweis mit der Funktion.