Microsoft Excel

Herbers Excel/VBA-Archiv

Zwei SUMMEWENN funktionen verknüpfen

Betrifft: Zwei SUMMEWENN funktionen verknüpfen von: Juan
Geschrieben am: 07.08.2008 07:57:55

Guten Morgen zusammen

Leider habe ich schon wieder ein Problem und hoffe ihr könnt bzw. wollt mir auch diesesmal helfen :)

Ist es überhaupt möglich zwei SUMMEWENN funktionen miteinander zu verknüpfen? also in der form?

= SUMMEWENN(Data!A:A;K3;Summewenn(Data!AJ:AJ;Verkäuferstatistik!A4;Data!Z:Z))

Also meine Idee ist:

Ich habe Zwei Tabellenblätter, "Data" und "Verkäuferstatistik". Ich möchte jetzt in ein 3. Tabellenblatt verschiedene auswertungen mache. D.h. er soll ins Tabellenblatt "Data" und in der Spalte "A" die Einträge mit K3 in der 3. Tabelle vergleichen UND er soll auch noch in "Verkäuferstatistik" die Werte mit "A4" der 3. Tabelle verlgeichen und erst dann die Summe der Beträge in "Data" "Z" machen.

Excel gibt mir an, dass die Formel falsch sei, wie muss ich die richtig schreiben?

Ich hoffe Ihr versteht was ich meine..

Liebe Grüsse Juan

  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: Worti
Geschrieben am: 07.08.2008 08:09:24

Hallo Juan,
das geht mit SUMMENPRODUKT:

=SUMMENPRODUKT((Data!A1:A10=Tabelle3!K3)*(Statistik!A1:A10=Tabelle3!A4)*(Data!Z1:Z10))




Gruß
Worti


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: Juan
Geschrieben am: 07.08.2008 08:25:37

Guten Morgen Worti

Erstmal herzlichen Dank für deine schnelle Antwort.

Ich muss mich entschudligen, meine Beschreibung war fehlerhaft, ich habe nicht 3 Tabellen sondern nur 2, weiss nicht wiso ich auf 3 gekommen bin.

Ich habe deine Formel nun so auf meine Tabellen angepasst, aber es gibt die Fehlermelung #Zahl aus..?

=SUMMENPRODUKT((Data!A:A=K3)*(Data!AJ:AJ=A4)*(Data!Z:Z))

Sorry nochmal für die Umtriebe...

Gruss Juan


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: David
Geschrieben am: 07.08.2008 08:32:08

Der Fehler entsteht, wenn in dem auszuwertenden Bereich keine Zahlen vorkommen. Da du die kompletten Spalten als Bereich angibst, vermute ich, dass du die Überschriften mit einbezogen hast.

Ich bin mir auch nicht ganz sicher, ob Summenprodukt überhaupt mit kompletten Spalten arbeitet, aber auch hinsichtlich der Performance würde ich dir empfehlen, den Bereich genauer zu definieren. Anstatt z. Bsp. "Data!A:A" besser "Data!A2:A1000", wobei du die letzte Zeile durchaus 'großzügig' dimensionieren kannst; wenn die nicht alle ausgefüllt sind, sondern nur '0' oder 'leer' enthalten, ist das kein Problem.

Gruß

David


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: Worti
Geschrieben am: 07.08.2008 08:33:45

Hallo nochmal,
Summenprodukt verkraftet ganze Spalten nicht, du mußt leider schreiben: A1:A65535

Worti


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: Juan
Geschrieben am: 07.08.2008 08:42:56

Hallo David und Worti

Danke für die Antworten.

Ich habe jetzt die Formel so geändert:

=SUMMENPRODUKT((Data!A6:A53=K3)*(Data!AJ6:AJ53=A4)*(Data!Z6:Z53))

Obwohl in Data!Z6:Z53 Beträge zum summieren vorhanden sind, resultiert immer noch der Fehler #Zahl

Ich habe die Bereiche extra auch klein gewählt und sie enthalten nun keine Überschriften mehr.

An was kann das noch liegen?

Grüsse Juan


  

Betrifft: Lad die Mappe doch mal hoch oT von: Worti
Geschrieben am: 07.08.2008 08:45:10




  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: David
Geschrieben am: 07.08.2008 08:46:48

Ohne die Daten zu kennen, lässt sich das nicht klären. Die Formel sieht "optisch" zumindest richtig aus. Wähle mal den Bereich noch etwas kleiner, so daß ca. 10 Zeilen umfasst werden. Anschließend schaust du dir die einzelnen Berechnungsschritte mal mit der Formelauswertung an (Extras - Formelüberwachtung - Formelauswertung). Dann kannst du normalerweise recht gut erkennen, welcher Wert oder welche Spalte den Fehler produzierst.

Wenn dir das nicht hilft, bleibt dir wohl wirklich nur das hochladen der Mappe.

Gruß

David


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: mpb
Geschrieben am: 07.08.2008 08:56:47

Hallo Juan,

prüfe mal, ob im Bereich Z6:Z53 wirklich nur Zahlen stehen, z.B. indem Du in eine Hilfsspalte (z.B. AA) in AA6 eingibst

=ISTZAHL(Z6) und die Formel nach unten ziehst. Falls hier irgendwo das Ergebnis FALSCH kommt, hast die das Problem verursachende Zelle gefunden.

Sollten Texte in Spalte Z vorkommen, die wie Zahlen "aussehen", kannst Du auch folgende Lösung versuchen:

=SUMMENPRODUKT((Data!A6:A53=K3)*(Data!AJ6:AJ53=A4)*(1*(Data!Z6:Z53)))

Schau Dir auch noch meinen anderen Hinweis zu den Tabellenblättern an.

Gruß
Martin


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: mpb
Geschrieben am: 07.08.2008 08:52:11

Hallo Juan,

in der Formel beziehst Du Dich jetzt aber nur noch auf eine Tabelle (Data), die Tabelle "Verkäuferstatistik" wird überhaupt nicht mehr berücksichtigt. Absicht, Fehler oder mangelhafte Problembeschreibung?

Gruß
Martin


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: Juan
Geschrieben am: 07.08.2008 09:01:33

Hallo nochmals

Also ich habe die Datei jetzt hochgeladen, sorry hat ein bisschen gedauert.

https://www.herber.de/bbs/user/54393.xls

Ich hoffe das hilft euch (bzw. mir) weiter :)

Danke für eure Bemühungen

@ Martin: Die Tabelle "Verkäuferstatistik" ist die Tabelle in der ich die Formel haben möchte, kann ich da nicht einfach auf die Zelle klicken, mit der er die Daten aus "Data" vergleichen soll? oder habe ich mich in meinen erklärungen wieder unklar ausgedrückt,sorry falls das der fall ist und danke für deine Antwort!!

Grüsse Juan


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: David
Geschrieben am: 07.08.2008 09:10:20

Folgende Fehler:

Spalte AI enthält #Zahl-Fehler, somit wird jede sich darauf beziehende Formel einen Formel ergeben. Der Fehler in Spalte AI entsteht durch das fehlende Datum in Spalte AC, warum, hab ich mir jetzt nicht näher angeschaut.

Anschließend entsteht ein #WERT-Fehler, da in der Spalte Z nicht alle Preise gepflegt sind.

Außerdem muss in deiner Summenprodukt-Formel der Wert K3 fixiert werden, wenn du die Formel runterziehst, steht in der nächsten Zeile als Bezug K4, das passt nicht. Schreibe vor die 3 ein $-Zeichen, ebenso solltest du bei A4 vor das A ein $-Zeichen setzen, falls du die Formel später mal woanders hinkopieren solltest.

Gruß

David


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: David
Geschrieben am: 07.08.2008 09:11:46

...somit wird jede sich darauf beziehende Formel einen Formel ergeben...

ich meinte natürlich:

somit wird jede sich darauf beziehende Formel einen Fehler ergeben


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: Juan
Geschrieben am: 07.08.2008 09:19:07

Ach soo, ja natürlich...

Mein Problem ist also, dass der Fehler #Zahl raus muss. Ich kann die anderen zellen in die später mal ein datum kommt nicht füllen mit daten, dann wäre die auswertung verfälscht...gibt es da eine andere möglichkeit dem excel zu sagen, wenn der fehler #ZAHL steht, soll er es ignorieren oder so in der form?

Grüsse Juan


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: mpb
Geschrieben am: 07.08.2008 09:54:04

Hallo Juan,

wenn die Fehlermeldung nicht unterdrückt werden kann, musst Du statt Summenprodukt eine Matrixformel anwenden, innerhalb derer die Fehlermeldung abgeprüft werden muss. Folgende Formel in K4 und dann nach unten und rechts kopieren:

=SUMME((Data!$A$6:$A$53=K$3)*(WENN(ISTFEHLER(Data!$AI$6:$AI$53=$A4);0;Data!$AI$6:$AI$53=$A4)) *(WENN(ISTFEHLER(Data!$AI$6:$AI$53);0;Data!$Z$6:$Z$53)))

Eingabe mit STRG-SHIFT-ENTER und nicht mit ENTER abschließen.

Gruß
Martin


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: mpb
Geschrieben am: 07.08.2008 09:32:48

Hallo Juan,

in Ergänzung zu Davids Erläuterungen:

Die Bereiche, die Du in DATA ansprichst, müssen auch absolut adressiert werden. Die Formel in K4 sollte lauten:

=SUMMENPRODUKT((Data!$A$6:$A$53=K$3)*(Data!$AI$6:$AI$53=$A4)*(Data!$Z$6:$Z$53))

damit Du sie nach unten, links uns rechts kopieren kannst.

Generell solltest Du Deine (Test?)-Datei so aufbauen, dass die Formeln auch sinnvolle Ergebnisse liefern können. In Spalte A hast Du z.B. nur die Zeilen 6-8 befüllt, während in den anderen relevanten Spalten in diesen Zeilen überhaupt nicht steht. Wie soll dann bei einer bedingten Summierung ein sinnvoller Wert herauskommen?

Ich habe Deine Beispieldatei mal ergänzt und hochgeladen:

https://www.herber.de/bbs/user/54397.xls


Gruß
Martin


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: David
Geschrieben am: 07.08.2008 09:17:49

Der Bereich zum Suchen muss natürlich auch fest definiert sein, damit sich die Bezüge nicht verschieben. Schlußendlich führt die Formel immer zum Ergebnis 0, da in Spalte A bei Data! so gut wie keine Werte drinstehen und somit nichts summiert wird.

Folgende korrigierte Tabelle von mir:

https://www.herber.de/bbs/user/54395.xls

Schau mal, ob du damit was anfangen kannst.

Gruß

David


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: Juan
Geschrieben am: 07.08.2008 09:21:22

Poow David das ist genau das was ich gesucht hatte!!!!!

Ich danke dir und natürlich auch allen anderen, die mir geholfen haben 10000 fach!!!!

Ich bin wirklich sehr froh, einfach ein hammer forum (bzw. hammer leute, die hier ihre hilfestellung leisten)!!!

Liebe Grüsse an alle!!


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: Hanz P.
Geschrieben am: 07.08.2008 09:35:56

Moin David,
hab den Thread mitgelesen und hab da mal ne Frage an dich:
wou sind die -- in der Formel gut? ohne Funzt es nicht.

Hanz


  

Betrifft: AW: Zwei SUMMEWENN funktionen verknüpfen von: David
Geschrieben am: 07.08.2008 09:48:20

Die normale Summenprodukt-Formel verarbeitet normalerweise nur Zahlen. Wenn du aber Text vergleichen willst, um bestimmte Kriterien erfüllen zu lassen, bevor die Summe gezogen wird, versagt die Funktion. Der Vergleich von z. Bsp. Auto = Auto liefert zwar WAHR, aber "nur" in Textform. Damit Excel "gezwungen" wird, diesen Text in eine Zahl (WAHR=1, FALSCH=0) umzuwandeln, stellst du ein doppeltes Minuszeichen davor (-- ergibt +). Alternativ könnte man auch *1 oder +0 rechnen lassen, aber so gehts wahrscheinlich am einfachsten.
Im übrigen habe ich diese Weisheiten auch erst in diesem Forum kennengelernt. Ich habe da mal einen interessanten Link bekommen:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Ist allerdings in Englisch, da wird diese Formel bis ins kleinste Detail erläutert. Wie vorhin, Juan schon empfohlen, würde ich mir da einfach ein simples Beispiel basteln mit wenigen Daten und über die Formelauswertung mal schauen, welche Teilschritte denn da von Excel berechnet werden.

Gruß

David


 

Beiträge aus den Excel-Beispielen zum Thema "Zwei SUMMEWENN funktionen verknüpfen"