Office: (Office 2010) Zählen - WENN/Dann in Zeilen und Spalten kombinieren

In der Spalte steht zb

Beispiel 01
Beispiel 01a
Beispiel 01b
Beispiel 01c
Beispiel 02
Beispiel 03

Mit "Beispiel 01*" könnte ich alle vier Varianten mit einbeziehen. So ging es zumindes bisher. ´Wenn ich das jetzt eingebe kommen leere Zellen bzw #NV (habe ich über eine zusätzlich wenn-Fkt. angegeben).

Gibt es btw noch eine Möglichkeit das ganze mittels der Mittelwertfunktion zu kombinieren?
Das es mir alle Werte in den Zellen mii den entsprechenden Kritereien zusammen addiert - dort stehen verschiedene werte zwischen 0 und 100 drin - und den Durchschnitt daraus bildet? Habe aktuell die MIttelwertswenns()-Fkt. Mit Summenprodukt zählt es mir aktuell ja die Anzahl aller betreffenden Zellen zusammen. Wenn ich das durch die Anzahl dividiere hilft mir das nicht wirklich.

 

Hola,

zur ersten Frage:

Code:

=SUMMENPRODUKT((Daten_komplett.xlsx!$A$1:$JJ$1="BB04_01")*(Daten_komplett.xlsx!$A$2:$JJ$1000=1)*(ISTZAHL(SUCHEN($B$4;Daten_komplett.xlsx!$P$2:$P$1000))))

Zur zweiten Frage:

welche Werte sollen denn genau addiert werden? Man müsste dann die obige Formel um den Bereich erweitern, der addiert werden soll und das dann durch die obige Formel teilen.

Gruß,
stee1da

 

Hallo Steve,

die Formel passt perfekt. Danke. Musste erst mal google zu der Funktion ISTZAHL befragen, weil Excel mir keine Antwort dazu liefern konnte.

IN der Spalte würden nur Zahlen stehen. Meist zwischen 1 und 100 aus denen ich den Mittelwert berechnen will, die die jeweilige Variante - Beitrag #16 -ausgewählt haben. Ich habe etwas hin und her probiert mit Anzahl, Summe etc. komme aber auf keinen grünen Zweig

Code:

=(SUMMENPRODUKT((Daten_komplett.xlsx!$A$1:$IU$1="VA08_01")*(SUMME(Daten_komplett.xlsx!$A$2:$IU$1000))*(ISTZAHL(SUCHEN($B$4;Daten_komplett.xlsx!$P$2:$P$1000)))))/(SUMMENPRODUKT((Daten_komplett.xlsx!$A$1:$IU$1="VA08_01")*(Daten_komplett.xlsx!$P$2:$P$1000=$B$4)))

Leider passt das überhaupt nicht :D
ich habe hier eine Änderung in der Spaltenbezeichnung und in den Größe der Tabelle, im Vergleich zu den von oben verwendeten Daten. Kann ich das überhaupt so aufsummieren, denn ich habe ja summe und summenprodukt innerhalb ein und derselben Rechenoperation. Ist ja wie doppelt-gemobbelt?!

 

Hallo,

ich würde hier mit Zählenwenn arbeiten und die Überschrift der zu verwendenden Spalte als Suchkriterium verwenden:

Code:

=ZÄHLENWENN(INDEX(Daten_komplett.xlsx!$1:$1048576;;VERGLEICH(A2;Daten_komplett.xlsx!$1:$1;0));B4)

In A2 steht das Suchkriterium, könnte man als DropDown-Liste hinterlegen. Dann könnte man mit Wildcards als Suchkriterium arbeiten:

Code:

=ZÄHLENWENN(INDEX(Daten_komplett.xlsx!$1:$1048576;;VERGLEICH(A2;Daten_komplett.xlsx!$1:$1;0));B4&"*")

Da ginge auch der Mittelwert:

Code:

{=Mittelwert(wenn(INDEX(Daten_komplett.xlsx!$1:$5000;;VERGLEICH(A2;Daten_komplett.xlsx!$1:$1;0))=B4);Deine Zählspalte (hab ich nicht gefunden))}

 

Hi Edgar,
danke für deinen Vorschlag. Mit der Zählenwenn-Funktion habe ich bereits gearbeitet. Hatte aber das Problem, dass ab und an eine neue Spalte in der Datei, in der die ganzen Werte stehen, hinzukam und dadurch die ganzen Bezüge falsch wurden. Ich habe dann alle Spaltenbezüge überprüfen müssen. Aus diesem Grund habe ich nach einer Alternative gesucht dass ich den Namen der Spalte nach der gesucht werden soll festlege und nicht Spalte selbst. Der steve hatte mir hierfur die Verwendung des Summenprodukts vorgeschlagen. Das funktioniert auch soweit alles wie gewünscht
.
Für die Berechnung des Mittelwertes, habe ich die Mittelwernns-Funktion genommen um die entsprechenden Bezüge zuordnen zu können. Problem auch hier, dass die Spalte festgelegt wird und nciht der Name in der dann gesucht werden soll.

Mit deiner Formel komme ich leider nicht ganz zurecht

A2 ist das Suchkriterium - aktuell ist mein Suchkriterium die Spalte mit einer gewissen Bezeichnung. In dem vorgehenden Post von mir war es z.B. die Spalte mit dem Namen "VA08_01". Ausgehend davon, möchte ich alle Werte nehmen bei denen in der Spalte P:P der Bezug von B4 vorkommt. Die Spalte P:P bleibt glücklicherweise immer konstant. Ist das Suchkriterium jetzt die Spalte in der ich nach den Mittelwerten suche oder die Spalte P:P in der in nach der Bezeichnung in Zelle B4 suche, welches als Kriterium für das heranziehen des Wertes für den Mittelwert dient?
Wenn ich den Befehl VERGLEICH betrachte, muss ich da für die Matrix nicht auch einen größeren Bereich anlegen? Statt VERGLEICH("VA08_01";Daten_komplett.xlsx!$1:$1;0) eher VERGLEICH("VA08_01";Daten_komplett.xlsx!$1:$1000;0), da ich nach dem Suchkriterium "VA08_01" in der ersten Zeile über alle/ viele (1000) Spalten suchen muss? Wo aber lege ich dann fest welche Spalte - in dem Falle ja P:P - nach dem Kriterium in Zelle B4 durchsucht wird?
Und die Zählspalte am Ende ist dann die Spalte, in der sich die Werte für die Berechnung des Mittelwertes befinden, also die mit der Bezeichnung "VA08_01, oder?

Code:

=Mittelwert(Wenn(INDEX(Daten_komplett.xlsx!$1:$5000;1;VERGLEICH("VA08_01";Daten_komplett.xlsx!$1:$1;0))=B4); Daten_komplett.xlsx!$A$1:$IU$1="VA08_01")

Einfach ausgedrückt will ich excel befehlen "Suche in der Zeile 1 nach der Bezeichnung "VA08_01" und bilde mir aus allen Werten, die in der Spalte P:P das Kriterium aus Zelle B4 haben/ erfüllen, den Mittelwert"

Sorry für dne langen Text, aber beim probieren und auseinandersetzen kamen ein paar Fragen auf ;)

Danke und viele Grüße,