Office: Probleme mit SUMMENPRODUKT

Hallo,
ich habe ein Problem mit einer Formel in der ich SUMMENPRODUKT verwende.
Es geht darum, dass ich unter drei verschiedenen Bedingungen einen Wert addieren möchte.
In Spalte B ist ein Datum enthalten, das allerdings als Text formatiert ist und daher nicht automatisch als solches erkannt wird. In Spalte C ist der zu durchsuchende Text enthalten und der zu addierende Betrag steht in Spalte H.
Die drei Bedingungen sind,
1. dass in Spalte C ein bestimmter Textteil enthalten sein muss
2. dass das Datum nicht größer als ...
3. dass das Datum nicht kleiner als ... sein darf.

Mit folgender Formel erreiche ich im Prinzip das gewünschte Ergebnis:
=SUMMENPRODUKT((ISTZAHL(SUCHEN("SUCHTEXT";C2:C20)))*((DATWERT(B2:B20))=DATWERT("01.02.2010"))*(H2:H20))

Allerdings werden hierbei nur die ersten 20 Zeilen der Tabelle durchsucht.
Da sich die länge der Tabelle ständig verändert und ich nicht immer die Formel anpassen möchte, suche ich nach einer anderen Lösung. Wenn ich nun die Zeilenangabe von 20 z.B. auf 200 erhöhe, bekomme ich #WERT als Ergebnis, sobald eine Leerzeile durchsucht wird.
Dafür dürfte die DATWERT-Funktion verantwortlich sein, die bei leeren Feldern kein korrektes Ergebnis liefert.

Hat jemand eine Idee, wie ich das abfangen kann?

Ich hoffe, ich habe das Problem aussagekräftig beschrieben und danke schon mal im voraus für hilfreiche Antworten.

Gruß
Tinky*Winky

:)

 

Hallo Tinky*Winky,

mit der Matrixformel =MAX(WENN(Tabelle1!C2:C200"";ZEILE(Tabelle1!C2:C200);0)) kannst du die letzte belegte Zeile in Spalte C ermitteln. Definiere für diese Formel einen Namen und verwende diesen mit Hilfe der INDIREKT-Funktion in deiner Formel an Stelle der Zeilennummer für die unterste Zeile.

Gruß Ingolf

 

Hallo,

zu dynamischen Bereichen unter Verwendung von Namen ist hier eine gute Anleitung:
http://www.ms-office-forum.net/forum...d.php?t=148491
Anstelle der Bereiche dann die Namen verwenden.

Alternativ:
Verwenden einer Formel mit analogem Aufbau:
{=SUMME(WENN(NICHT(ISTLEER(A2:A8));(DATWERT(A2:A8)>HEUTE())*1;0))}
(Es handelt sich um eine Matrixfunktion. Die geschweiften Klammern also nicht mit eingeben, sondern die Funktion mit Strg+Shift+Enter abschließen.

Grüße
EarlFred

 

Moin,

oder vielleicht so:
Tabelle1

	A
2	46

Zelle	Formel
A2	{=SUMME((ISTZAHL(SUCHEN("SUCHTEXT";C2:C999))*((B2:B999)*1>=DATWERT("02.01.2010"))*((B2:B999)*1("05.02.2010"))*((H2:H999))))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Tabelle1

	B	C	H
2	02.02.2010	hallo	1
3	02.02.2010	suchtext	2
4	02.02.2010	huhu	3
5	02.02.2010	hallo	4
6	03.02.2010	suchtext	5
7	02.02.2010	huhu	6
8	02.02.2010	hallo	7
9	03.02.2010	suchtext	8
10	02.02.2010	huhu	9
11	02.02.2010	hallo	10
12	10.02.2010	suchtext	11
13	02.02.2010	huhu	12
14	02.02.2010	hallo	13
15	02.02.2010	suchtext	14
16	25.04.2010	huhu	15
17	02.02.2010	hallo	16
18	02.02.2010	suchtext	17
19	02.02.2010	huhu	18
20	02.02.2010	hallo	19

Excel Tabellen im Web darstellen >>

 

Vielen Dank für Eure schnelle Hilfe.

@mücke: genau das funktioniert bei mir nicht, weil das Datum als Text erfasst/formatiert wurde und damit die DATWERT-Funktion kein Ergebnis liefert.

@EarlFred: das mit der Benennung von dynamischen Bereichen war mir ´ne Nummer zu hoch. Trotzdem Danke.

Ich habe die Lösung dank der Hilfe von IngGi gefunden und die letzte Zeile über die Matrix-Funktion ermittelt und dann mit der INDIREKT-Funktion gearbeitet. Hat super funktioniert. Danke.

 

Hallo Tinky*Winky,

hast du meine Formel auch getestet, bzw. den Matrix-Hinweis beachtet?!
Die Datumsangaben sind als TEXT hinterlegt.

 

Hallo,

eine Amateurformel hätte ich auch noch:

Code: