Anzahl/Zählenwenn mit Gruppiertem Kriterium

[Schnattie]

Hallo liebe Excel Profis,
seit Tagen versuche ich eine Formel zu basteln, jedoch bin ich nun mit meinem Excel-Latein am Ende.

Erklärung meines Problems:
Berechnung Anzahl der Mitglieder sowie Summe der Güter nach bestimmten Kriterien; unterschiedliche Datentabellen
Ich möchte im extra Auswertungsblatt die Anzahl der Mitglieder sowie die entsprechende Summe an produzierten Gütern ermitteln.
Datenherkunft sind jeweils versch. csv, die in einer Arbeitsmappe mit versch. Arbeitsblättern zusammengefasst sind und wo die Daten in dem Arbeitsblatt "Auswertung" analysiert werden.
Nun fehlen mir noch drei Berechnungen, die jeweils unterschiedliche Filter-/Suchkriterien haben, jedoch nicht alle auf einen spezifischen Zellverweis
auswertbar sind. Zudem enthält das Blatt "building" zwar die Gebäude von den Mitgliedern sowie das Gebäude-Zeitalter, jedoch nicht das Mitglieder-Zeitalter.

Optimalerweise will ich in den Datentabellen keine zusätzlichen Spalten einfügen. Vielleicht geht es auch ohne? Zur Not muss ich vielleicht in die Tabelle "building" mit XVERWEIS das jeweilige Zeitalter des Mitgliedes hinzufügen. Denn beide Zeitalter (also Gebäude-Zeitalter und Mitgliederzeitalter) sind Teil der Suchkriterien der gesuchten Berechnungen.

Das klingt jetzt vielleicht sehr komplex, ist aber recht überschaubar und wird hoffentlich mit meiner Beispiel-Datei verständlich.

Ich habe im Blatt "Auswertung" die Spalten, wo die Formeln noch fehlen, gelb markiert. Darunter findet ihr zu Vergleichszwecken die Soll-Ergebnisse, die ich manuell eingetippt habe. Weiter gibt es natürlich beide Datenblätter "member" und "building". Zusätzlich habe ich noch ein extra Blatt hinzugefügt, welches eine intelligente Tabelle enthält, die beide Datenblätter zusammenfasst sowie eine Ergebniszeile beinhaltet. Daneben hatte ich für die zweite der drei Spalten schon rumgetestet.

Ich hoffe, ihr könnt mir helfen bei den Berechnungen, die jeweils richtigen Funktionen zu finden??


  Beispiel_member-era_build-era_TestDaten.xlsx (Größe: 32,13 KB / Downloads: 8)

hoffnungsvolle Grüße sendet Euch 
Schnattie

[shift-del]

Moin

Möchtest du das Wunschergebnis in E26 überdenken?

[Schnattie]

danke Detlef für den Hinweis. Ja, das hat nicht gestimmt. Genau deshalb will ich ja Funktionen rechnen lassen   
Hast Du Ideen für die Funktionen, die es lösen?

Anbei die korrigierte Beispieldatei. ich hoffe, es haben sich sonst keine weiteren Fehler eingeschlichen.


  Beispiel_member-era_build-era_TestDaten_korrektur.xlsx (Größe: 32,23 KB / Downloads: 14)

[shift-del]

Mein Vorschlag.

Auswertung

	E	F	G
2	Anz member build-era	Anz member b.-era=m._era	SUM Goods b.-era=m._era
3	0	0	0

Formeln der Tabelle

Zelle Formel E3 =SUMME(WENN(WENNFEHLER(EINDEUTIG(FILTER(building!C:C;building!B:B=B3));0)=0;0;1)) F3 =SUMME(WENN(WENNFEHLER(EINDEUTIG(FILTER(building!C:C;(building!B:B=B3)*(building!E:E=B3)));0)=0;0;1)) G3 =SUMME(WENNFEHLER(FILTER(building!D:D;(building!B:B=B3)*(building!E:E=B3));0))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

building

	E
1	member_era
2	Jahrhundertwende

Formeln der Tabelle

Zelle Formel E2 =XVERWEIS(C2;member!A:A;member!B:B)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[Schnattie]

vielen Dank Detlef, das hat mir sehr geholfen.

Wie ich sehe, war ich mit meinen Versuchen im Blatt Testformeln schon recht nah dran. Ganz verstehe ich es noch nicht, doch ich ahne, warum meine Versuche teils Fehlerwerte ausgespuckt haben. 
Liegt es möglicherweise daran, dass der Teil =EINDEUTIG(FILTER(...)) einen ganzen Array zurückgibt und die SUMME alle Einzelergebnisse des gesamten Array addiert, während andere Funktionen diesen Array gar nicht auswerten können und deshalb Fehlerwerte ausspucken?? 

... würde mich ja fachlich schon interessieren, warum ich mit ANZAHL(), ANZAHL2() und ZÄHLENWENN() gescheitert bin ...

Vielen Vielen Dank für Deinen Lösungsvorschlag.

Nun kann ich an der Originaltabelle mit den Auswertungen weiterarbeiten ...

[RPP63]

Moin!
Anzahl und Anzahl2 müssten funktionieren:
=ANZAHL(SEQUENZ(10)) → 10
=ANZAHL2(ZEICHEN(SEQUENZ(15;;65))) → 15
Summe sowieso:
=SUMME(SEQUENZ(10;;15;7)) → 465

ZÄHLENWENN() hingegen erwartet zwingend einen Zellbereich, da funktionieren keine Vektoren.

Gruß Ralf

[Schnattie]

stimmt ..... UND .... Anzahl/Anzahl2 zählt ja leere Inhalte anders (Ergebnis somit nie 0), insofern ist es dann doch logisch, dass es nur mit Summe funktioniert hat.

Vielen Dank an Euch

[shift-del]

Ganz verstehe ich es noch nicht, doch ich ahne, warum meine Versuche teils Fehlerwerte ausgespuckt haben. 

Ich habe mir deine Formeln nicht angesehen.
Aber das wesentliche Problem ist FILTER(). Denn das gibt immer mindestens einen Wert zurück (ggf. der Fehler #CALC!). Das Ergebnis 0 ist also mit ANZAHL2(EINDEUTIG(FILTER())) nicht möglich.
Man muß also um diese Einschräkung rum programmieren.