ISTZAHL in SUMMENPRODUKT-Formel funktioniert nicht

[Frager]

Liebe Excel-Experten
 
Ich komme mit meinem Excel-Problem einfach nicht weiter.
Ich möchte einen Altersdurchschnitt aus einer Liste in einem Tabellenblatt errechnen.
Die Liste besteht aus zwei Spalten:
A: Alter in JJ (Spaltentitel); Zelleneinträge von 0 bis 67 in den Zellen 3 bis 70.
B: Anzahl Personen gleichen Alters (Spaltentitel); Zellenwerte werden durch die Formel =WENN(ZÄHLENWENN('Team -Daten'!$AC$5:$AC$80,A43)<1,#NV,ZÄHLENWENN('Team -Daten'!$AC$5:$AC$80,A3)) aus dem Tabellenblatt „Team-Daten“ generiert. Den Eintrag #NV für 0 Personen brauche ich für die nachfolgende Grafik. Die Linie soll nicht ständig auf 0 absacken.
In C71 berechne ich die Anzahl Personen: =SUMMEWENNS(B3:B70,B3:B70,"<>#NV").
In C72 berechne ich das Durchschnittsalter mit der Formel: =SUMMENPRODUKT((A3:A70)*(ISTZAHL(B3:B70)))/C71.
Und hier beginnen auch die Probleme: die Formel in C72 nimmt für „ISTZAHL“ jeweils immer eine 1, auch wenn an entsprechenden Stellen höhere Zahlen stehen. Das ergibt dann natürlich ein falsches Durchschnittsalter.
Das Problem könnte ich durch eine zusätzliche Spalte C lösen, in der die Zeilenprodukte von A und B stehen und die dann am Schluss summiert werden. Doch ich denke, es muss eine elegantere Lösung geben, indem man die bestehende Formel C72 entsprechend anpasst.
:22:
 
Für Euren Effort bedanke ich mich jetzt schon ganz herzlich.
 
Peter, der Frager

[schauan]

Hallöchen,

im Prinzip so:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
3	1	2	5
4	2	3	1,6
5	3	#NV

Zelle	Formel
C3	=SUMMEWENNS(B3:B70;B3:B70;"<>#NV")
C4	{=SUMMENPRODUKT((A3:A70)*(WENN(ISTZAHL(B3:B70);B3:B70;0)))/C3}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

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

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

[neopa]

abschließenHallo Peter,

Deine Auswertung bedarf weder SUMMEWENNS() noch SUMMENPRODUKT() .
Den prinzipiell richtigen Ansatz mit WENN(), hat ja Dir André schon aufgezeigt. Er hat dabei jedoch Deine beide oben Funktionen übernommen, die hier aber nicht wirklich notwendig sind.

Außerdem kannst Du beide Formelteile in einer Zelle verbinden und zwar so:

Code:

{=SUMME(WENN(ISTZAHL(B3:B70);A3:A70*B3:B70))/SUMMEWENN(B3:B70;"<>#NV";B3:B70)}

Die geschweiften Klammern {} nicht eingegeben sondern Formeleingabe mit [Strg]+[Shift]+[Enter] abschließen.


Ergänzend hierzu:
Aus Deinem Schreiben entnehme ich, dass Du Deine Datentabelle in A3:B70 wahrscheinlich zu einer "intelligenten" Tabelle formatierst hast.  Eine erste derartig formatierte Tabelle in einer Arbeitsmappe benennt Excel automatisch zu "Tabelle1" (bitte nicht verwechseln mit einem Tabellenblattnamen gleichen Namens), eine evtl. zweite in "Tabelle2" ...
Empfehlenswert ist es jedoch, den/die von Excel automatisch erzeugten Tabellennamen im Namensmanager in einen "sprechenden" zu ändern; hier z.B. in "Altersgruppendaten" oder einfacher und kürzer z.B. in "Daten"). In nachfolgender analoger Formel hab ich den Namen "Tabelle1" jedoch so belassen, wie automatisch von Excel als erste "intelligenten" Tabelle in meiner Beispielmappe erzeugt.

Dafür würde obige Formel wie folgt lauten:

Code:

{=SUMME(WENN(ISTZAHL(Tabelle1[Anzahl Personen gleichen Alters]);Tabelle1[Alter in JJ]*
Tabelle1[Anzahl Personen gleichen Alters]))/SUMMEWENN(Tabelle1[Anzahl Personen gleichen Alters];"<>#NV";
Tabelle1[Anzahl Personen gleichen Alters])}

Hieran erkennst Du aber auch, dass lange Überschriftnamen sich nicht unbedingt nur vorteilhaft auf die Lesbarkeit einer Formel auswirken können. Aber unabhängig davon, bleibt der entscheidende Vorteil einer "intelligenten" Tabelle, dass Du in einer solchen weitere Daten ergänzen kannst, ohne dass Du die Formel entsprechend anpassen musst. Denn diese passt sich automatisch an einen verkürzten oder erweiterten auszuwertenden Datenbestand an.

[Frager]

Hallo Schauan und Werner

Vielen Dank für Euren Einsatz.
Bei Schauans Version kam eine 0.0 raus. Dort scheint ein ähnliches Problem zu stecken, wie in meiner Version (Zellformatierungen?). Aber eine Super-Darstellung! Danke.
Werners Version führte direkt zum richtigen Ergebnis. Ich übernahm den ersten Code. Den zweiten mit der intelligenten Tabelle liess ich bewusst beiseite in Besorgnis darum, dass diese meinen IQ konkurrenzieren könnte und das Zepter in der Anwendung übernimmt :16: .

Gäbe es solche Foren mit diesen hilfsbereiten Fachkräften nicht, ich weiss nicht, mit wieviel monetärem Kapital, Ärger und Zeitverlust dies die Wirtschaft belasten würde!
Nochmals vielen herzlichen Dank an Euch!

:18: 

Peter, der Frager