Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1204to1208
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Summenprodukt trotz #BEZUG bzw ISTFEHLER

Summenprodukt trotz #BEZUG bzw ISTFEHLER
karl
Hallo Excel Profis,
ich verwende die Summenprodukt-Formel um Summenwerte nach Monaten, und anderen Kriterien zu kumulieren:
'SUMMENPRODUKT(($H$7:$AQ$7<=$B$4)*($H$8:$AQ$8="PLAN")*($H9:$AQ9))
Jedoch haben Monate, die noch in der Zukunft liegen, keine Werte (#BEZUG im Tabellenblatt)
Gibt es eine Möglichkeit die Formel trotzdem zu verwenden, zB in Abwandlung mit ISTFEHLER oder ähnlichem?
Wäre euch super dankbar für einen Tipp, da ich trotz Recherchen nix finde.
Grüße
Karl
Fehler vorher vermeiden?
08.03.2011 11:28:55
Erich
Hi Karl,
mit SUMMENPRODUKT geht es kaum, mit Matrixformel-SUMME eher.
Um eine Formel schreiben zu können, müsste man wohl noch wissen,
in welchem/n Bereich/en die Fehlerwerte stehen können.
Aber:
Wäre es nicht einfacher, die Fehler vorher schon zu vermeiden?
Man könnte z. B. für Monate, die noch in der Zukunft liegen, die Zahl 0 ausrechnen.
"Jedoch haben Monate, die noch in der Zukunft liegen, keine Werte (#BEZUG im Tabellenblatt)"
ist nicht ganz korrekt: Auch ein Fehler ist ein Formelwert.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: Fehler vorher vermeiden?
08.03.2011 12:33:34
karl
die fehlerwerte stehen in den Monatsspalten. Genauer: Es gibt noch keine Werte für zukünftige Monate, da in diesen Bereichen wiederrum auf andere Dateien verknüpft wird.
Anzeige
AW: Summenprodukt trotz #BEZUG bzw ISTFEHLER
08.03.2011 11:30:28
Rudi
Hallo,
wie kommen die #Bezug zustande? Durch INDIREKT?
=Wenn(Istfehler(Indirekt(...));0;indirekt(...))
Gruß
Rudi
AW: Summenprodukt trotz #BEZUG bzw ISTFEHLER
08.03.2011 12:17:58
karl
Der #Bezug kommt dadurch zustande, dass sich die Monatsspalten ($H9:$AQ9) wiederrum auf anderen Dateien verlinken bzw. dann für die Monate in der Zukunft noch keine Dateien/Daten vorhanden sind.
Ist also indirekt oder?
wie muss die formel dann lauten?
=Wenn(Istfehler(Indirekt(SUMMENPRODUKT(..?..));0;indirekt(...))
AW: Summenprodukt trotz #BEZUG bzw ISTFEHLER
08.03.2011 12:24:25
mpb
Hallo Karl,
versuche mal folgende Matrixformel:
=SUMMENPRODUKT(WENN(ISTFEHLER($H$7:$AQ$7<=$B$4);0;($H$7:$AQ$7<=$B$4) *WENN(ISTFEHLER($H$8:$AQ$8="PLAN");0;($H$8:$AQ$8="PLAN"))*WENN(ISTFEHLER($H9:$AQ9);0;($H9:$AQ9))))
Eingabe nicht mit ENTER, sondern mit STRG-SHIFT-ENTER abschließen.
Gruß
Martin
Anzeige
AW: Summenprodukt trotz #BEZUG bzw ISTFEHLER
08.03.2011 12:33:59
karl
funktioniert leider nicht :(
AW: Summenprodukt trotz #BEZUG bzw ISTFEHLER
08.03.2011 12:55:16
mpb
Hallo Karl,
bei mir schon, siehe Beispieldatei.
https://www.herber.de/bbs/user/73886.xls
Entweder hast Du die Formel falsch eingebaut oder das Problem ist anders, als Du es geschildert hast.
Lade im Zweifel mal Deine Datei hoch.
Gruß
Martin
AW: Summenprodukt trotz #BEZUG bzw ISTFEHLER
08.03.2011 12:43:07
Reinhard
Hallo Karl,
Beipielmappe?
Gruß
Reinhard
AW: BSP Datei
08.03.2011 13:47:45
Jörn
Hallo Karl,
so?
=SUMME(WENN(ISTFEHLER(B8:AK8);0;((REST(SPALTE(B8:AK8);3)=1)*($B$6:$AK$6<=$G$2)*(B8:AK8))))
StrgShiftEnter
Gruß
Jörn
Anzeige
hilft nicht vile weiter
08.03.2011 13:50:29
Erich
Hi Karl (dies ist eine Anrede zund lässt den Beitrag freundlicher erscheinen),
woher die #BEZUG!-Werte in H8 usw. kommen, sieht man an dieser Mappe nicht.
Könnte man die nicht vermeiden?
Was man sieht, sind irre viele Namen. Werden die benutzt/gebraucht?
Dabei sind auch Namen wir jahr, monat. Diese Begriffe sind aber Namen von Excelfunktionen
und sollten nicht für Namensdefinitionen verwendet werden.
Kleines Schmankerl: Für G2 geht einfacher H2:
 EFGH
2Vormonat=28.02.201128.02.2011

Formeln der Tabelle
ZelleFormel
G2=DATUM(JAHR(HEUTE()); MONAT(HEUTE())+0;1)-1
H2=HEUTE()-TAG(HEUTE())
Namen in Formeln
ZelleNameBezieht sich auf
G2jahr='\\emea.zf-world.com\frd\berichte_2002\mr_mit sachs\[ok_ZF_Übersicht_Plan Ist_UB_Monat.xls]Monat mit Vorjahr Sachs (manu)'!$A$9
G2monat='\\emea.zf-world.com\frd\berichte_2002\mr_mit sachs\[ok_ZF_Übersicht_Plan Ist_UB_Monat.xls]Monat mit Vorjahr Sachs (manu)'!$A$11
G2Vorjahr='\\emea.zf-world.com\frd\berichte_2002\mr_mit sachs\[ok_ZF_Übersicht_Plan Ist_UB_Monat.xls]Monat mit Vorjahr Sachs (manu)'!$A$7
Namen verstehen

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: BSP Datei
08.03.2011 13:55:07
mpb
Hallo Karl,
in A8 die Matrixformel
=SUMMENPRODUKT(WENN(ISTFEHLER($B$6:$AK$6<=$G$2);0;($B$6:$AK$6<=$G$2) *WENN(ISTFEHLER($B$7:$AK$7="Differenz");0;($B$7:$AK$7="Differenz"))*WENN(ISTFEHLER($B8:$AK8);0; ($B8:$AK8))))
und die Eingabe nicht mit ENTER, sondern mit der Tastenkombination STRG-SHIFT-ENTER abschließen. Ergebnis: 147,00.
Die o.g. Formel ist die, die ich schonmal gepostet hatte, nun auf die Beispieldatei angepasst.
Noch 2 allgemeine Hinweise:
Wenn etwas (vermeintlich) nicht funktioniert, wäre eine Erläuterung des "Fehlers" hilfreich.
Hier im Forum sind Anrede und Grußformel üblich.
Gruß
Martin
Anzeige
AW: Nachtrag
08.03.2011 14:01:17
mpb
Hallo Karl,
da der #BEZUG!-Fehler ja nur beim 3. Faktor vorkommt, reicht auch die Matrixformel
=SUMMENPRODUKT(($B$6:$AK$6<=$G$2)*($B$7:$AK$7="Differenz")*WENN(ISTFEHLER($B8:$AK8);0;($B8:$AK8)))
Gruß
Martin
ohne Matrixformel...
08.03.2011 15:57:29
silex1
Hallo,
da nur Monate in der Zukunft den #BEZUG! Fehler bringen, würde auch ne "normale" SUMMENPRODUKT-Formel funktionieren, die das Ende selbst bestimmt.
 ABCDEFGHI
1         
2    Vormonat=28.02.2011  
3         
4         
5         
6KumuliertJan. 11Jan. 11Jan. 11Feb. 11Feb. 11Feb. 11Mrz. 11Mrz. 11
7DifferenzErtragAufwandDifferenzErtragAufwandDifferenzErtragAufwand
8147,0095,0040,00135,0010,002,0012,00#BEZUG!#BEZUG!
9102,0055,0030,0085,0015,002,0017,00#BEZUG!#BEZUG!
10685,0066,0020,0086,0099,00500,00599,00#BEZUG!#BEZUG!

ZelleFormel
A8=SUMMENPRODUKT(($B$6:INDEX($B$6:$AK$6;;VERGLEICH(9^9;$B8:$AK8))=$G$2)*($B$7:INDEX($B$7:$AK$7;;VERGLEICH(9^9;$B8:$AK8))="Differenz")*($B8:INDEX($B8:$AK8;;VERGLEICH(9^9;$B8:$AK8))))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

in der Formel bitte <=$G$2 in der ersten Bedingung abändern!
VG, Rene
Anzeige

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige