Office: Office 2007 - Summenprodukt oder Summewenn

Hallo Excelisten,
mein Problem ist wahrscheinlich total einfach zu lösen, aber ich komme nicht darauf. Hänge mal eine Beispieldatei ran.
Ich möchte im 3. Tabellenblatt Beträge aus 1. + 2. Tabellenblatt pro Jahr zusammenstellen. Meine Formelversuche funktionieren nicht. Wenn ich die Lösung für die Berechnung aus dem 1. Tabellenblatt hätte, (Spalte B im 3. Tb), würde ich das für's 2. TB wahrscheinlich selber hinbekommen, brauche nur einen Denkanstoss.

Vielen Dank schon mal vorweg
Thomas

Diese "Summenprodukt-Formel" funktioniert nicht:

=SUMMENPRODUKT((JAHR('1. Holzpreise'!$A$4:$A$110)=JAHR($A4))*('1. Holzpreise'!$F$4:$F$110))

Fehleranzeige ist: #WERT!

 

Hallo,

Du hast ja Excel2007: Dann kannst Du ja folgende Formeln verwenden (B2:B10)

=SUMMEWENNS('1. Holzpreise'!$F$4:$F$110;'1. Holzpreise'!$A$4:$A$110;">=" & $A2;'1. Holzpreise'!$A$4:$A$110;"<" & $A3)
=SUMMEWENNS('1. Holzpreise'!$F$4:$F$110;'1. Holzpreise'!$A$4:$A$110;">=" & $A3;'1. Holzpreise'!$A$4:$A$110;"<" & $A4)
=SUMMEWENNS('1. Holzpreise'!$F$4:$F$110;'1. Holzpreise'!$A$4:$A$110;">=" & $A4;'1. Holzpreise'!$A$4:$A$110;"<" & $A5)
=SUMMEWENNS('1. Holzpreise'!$F$4:$F$110;'1. Holzpreise'!$A$4:$A$110;">=" & $A5;'1. Holzpreise'!$A$4:$A$110;"<" & $A6)
=SUMMEWENNS('1. Holzpreise'!$F$4:$F$110;'1. Holzpreise'!$A$4:$A$110;">=" & $A6;'1. Holzpreise'!$A$4:$A$110;"<" & $A7)
=SUMMEWENNS('1. Holzpreise'!$F$4:$F$110;'1. Holzpreise'!$A$4:$A$110;">=" & $A7;'1. Holzpreise'!$A$4:$A$110;"<" & $A8)
=SUMMEWENNS('1. Holzpreise'!$F$4:$F$110;'1. Holzpreise'!$A$4:$A$110;">=" & $A8;'1. Holzpreise'!$A$4:$A$110;"<" & $A9)
=SUMMEWENNS('1. Holzpreise'!$F$4:$F$110;'1. Holzpreise'!$A$4:$A$110;">=" & $A9;'1. Holzpreise'!$A$4:$A$110;"<" & $A10)
=SUMMEWENNS('1. Holzpreise'!$F$4:$F$110;'1. Holzpreise'!$A$4:$A$110;">=" & $A10;'1. Holzpreise'!$A$4:$A$110;"<" & $A11)

Ergänzung:
Deine angeführte Formel funktioniert dann, wenn Du im Arbeitsblatt "1. Holzpreise" in Spalte F überall numerische Werte stehen hast. Tatsächlich hast nur in den ersten 6 Zeilen Zahlen stehen, in den restlichen (F10:F110) hast Du lauter "" (aufgrund der verwendeten Formel) stehen. Die SUMMENPRODUKT-Funktion will prinzipiell nur numerische Werte (im Gegensatz zur SUMME-Funktion) akzeptieren.
Wenn Du Deine Formeln in Spalte F wie folgt abänderst:
=WENN(B4=0;0;B4*D4+E4)
dann funktioniert Deine SUMMENPRODUKT-Formel.
Wenn Du die Spalte F mit folgendem Format versiehst:
_-* #.##0,00 €_-;-* #.##0,00 €_-;
hast Du den gleichen Effekt bezüglich Anzeige im Arbeitsblatt.
Spalte G müsstest Du dann auch noch formelmäßig anpassen, um den unschönen "#DIV/0"-Fehler zu beseitigen, der dann aufträte.

 

Hallo Anton

vielen Dank für deine schnelle Antwort, funktioniert einwandfrei, habe die Formel auch schon für das 2. Tabellenblatt umgebaut, funktioniert natürlich ebenso.

Zusätzlichen Dank überhaupt an dieses hervorragende Forum, hier wird nicht lange gefaselt und geschwafelt. Insgesammt sehr professioneller, höflicher und respektvoller Umgangston.
Musste an dieser Stelle auch einfach mal gesagt werden!

Viele liebe Grüße und ein schönes WE
Thomas

 

Hallo!

Ganz leicht abgeändert funktioniert es übrigens auch mit SUMMENPRODUKT.

	A	B
1		Holzkosten
2	2012	- €
3	2013	800,00 €
4	2014	160,00 €

Zelle	Formel
B2	=SUMMENPRODUKT(N(JAHR('1. Holzpreise'!$A$4:$A$110)=JAHR($A2));'1. Holzpreise'!$F$4:$F$110)
B3	=SUMMENPRODUKT(N(JAHR('1. Holzpreise'!$A$4:$A$110)=JAHR($A3));'1. Holzpreise'!$F$4:$F$110)
B4	=SUMMENPRODUKT(N(JAHR('1. Holzpreise'!$A$4:$A$110)=JAHR($A4));'1. Holzpreise'!$F$4:$F$110)

 

Hallo Schatzi,

danke für den Hinweis - an die N()-Funktion hatte ich gar nicht gedacht.

 

hi, da braucht's auch nicht unbedingt N()..:

Arbeitsblatt mit dem Namen '3. Gesammtkosten'
	A	B
1		Holzkosten
2	2012	- €
3	2013	800,00 €
4	2014	160,00 €
5	2015	- €
6	2016	- €
7	2017	- €
8	2018	- €
9	2019	- €
10	2020	- €

Zelle	Formel
B2	=SUMMENPRODUKT(((JAHR('1. Holzpreise'!$A$4:$A$100)=JAHR(A2))*1);'1. Holzpreise'!$F$4:$F$100)

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

 

Hallo!

Aber N ist ein Zeichen kürzer als *1
:p:p:p

 

hi Schatzi, wo du recht hast, hast du recht... ;-))

 

Hallo Schatzi,

vielen Dank auch für deine Antwort.
Welche Bewandnis hat es mit dieser N()-Funktion, ausser das sie Formeln funktionieren lässt??
In einer meiner anderen Tabellen, aus der ich diese Formel habe, funktioniert sie ohne "N", obwohl die Bedingungen und Parameter fast gleich sind, in der Formel berechne ich lediglich nach "Monat" und nicht wie hier nach "Jahr".

Viele Grüsse
Thomas

 

hi Thomas, N() hat eine online-hilfe... ;-))

 

Hallo!

Die N-Funktion macht hier nichts anderes, als dass sie die boolschen Werte WAHR und FALSCH in die nummerischen Werte 1 und 0 umwandelt.
Das ist in diesem Fall nötig, da in meiner Formel die Multiplikation entfernt wurde, die diese Umwandlung sonst prinzipiell auch vorgenommen hätte.

Bei dir war weniger die Formel das Problem, sondern eher deine Daten, bzw. deine Nicht-Daten (Leerzellen). Ohne Leerzellen würde deine Formel auch wunderbar funktionieren (und das tut sie ja auch in anderen Tabellen, wie du sagst).
Das SUMMENPRODUKT in seiner ursprünglichen Form (dort werden die Bereiche durch Semikolon getrennt) ignoriert aber netterweise Leerzellen!

Ich hoffe, das war verständlich und daher auch nachvollziehbar, warum ich die Formel in dieser Art geändert habe.

PS: Die Idee von Hiob, einfach mit 1 zu multiplizieren, ist übrigens absolut gleichwertig! Und wenn du diese Formel nicht gerade schrilliardenfach in deiner Mappe verwendest, dann dürfte auch dieses eine popelige Mehrzeichen keine signifikanten Auswirkungen auf die Dateigröße haben...;)