Office: (Office 2010) Summenprodukt - mehrere Suchkriterien - Textteile- in einer Spalte zählen

Hallo,

ich habe folgendes Problem, ich möchte mehrer Suchkriterien - Textteile - in einer Spalte zählen bzw. addieren.

Da auch noch das nach Kalenderjahr Unterschieden werden muß verwende ich Summenprodukt.
Solange ich nur nach einem Textteil suche ist das kein Problem. Alle Versuche aber mehreren Textteile gleichzeitig suchen zu lassen, ohne die Formeln einfach hintereinander zu hängen, kommt immer #WERT als Ergebnis heraus.

Formel für einen Textteile:
=SUMMENPRODUKT(--(JAHR(Daten!$E$2:$E$100)=$C$3);--(Daten!$A$2:$A$100=$A4);--(ISTZAHL(SUCHEN($A$2;(Daten!$F$2:$F$100))));Daten!$B$2:$B$100)

Nicht akzeptabele Formel für mehreren Textteile (in diesem Fall drei Stück):
=SUMMENPRODUKT(--(JAHR(Daten!$E$2:$E$100)=$C$3);--(Daten!$A$2:$A$100=$A4);--(ISTZAHL(SUCHEN($A$2;(Daten!$F$2:$F$100))));Daten!$B$2:$B$100)+SUMMENPRODUKT(--(JAHR(Daten!$E$2:$E$100)=$C$3);--(Daten!$A$2:$A$100=$A4);--(ISTZAHL(SUCHEN($A$2;(Daten!$F$2:$F$100))));Daten!$B$2:$B$100)+SUMMENPRODUKT(--(JAHR(Daten!$E$2:$E$100)=$C$3);--(Daten!$A$2:$A$100=$A4);--(ISTZAHL(SUCHEN($A$2;(Daten!$F$2:$F$100))));Daten!$B$2:$B$100)


Einfach gesagt, diesen Teil der Formel für mehrere Suchbegriffe:
--(ISTZAHL(SUCHEN($A$2;(Daten!$F$2:$F$100))))

Bei 100.000 Datensätzen braucht Excel Stunden... , bitte nicht Pivot vorschlagen oder VBA

Kann man die Suchbegriffe irgendwie mit "ODER" Verknüpfen, ich bin mit meinem Latein am Ende.
Eine Testdatei zum ausprobieren findet Ihr im Anhang.

Schon mal Danke für eure Hilfe im Voraus

 

Hi,

kein Wunder, wenn man mit Summenprodukt arbeitet!

Code:

=SUMMENPRODUKT((JAHR(Daten!$E$2:$E$100)=C$34)*(Daten!$A$2:$A$100=$A36)*((ISTZAHL(SUCHEN($A$32;(Daten!$F$2:$F$100))))+(ISTZAHL(SUCHEN($A$33;(Daten!$F$2:$F$100))))+(ISTZAHL(SUCHEN($A$34;(Daten!$F$2:$F$100))))>0);Daten!$B$2:$B$100)

wesentlich ressourcenschonender wäre das:

Code:

=SUMMEWENNS(Daten!$B:$B;Daten!$A:$A;$A36;Daten!$E:$E;">=1.1."&C$34;Daten!$E:$E;"<=31.12."&C$34;Daten!$F:$F;"*"&$A$32&"*")+SUMMEWENNS(Daten!$B:$B;Daten!$A:$A;$A36;Daten!$E:$E;">=1.1."&C$34;Daten!$E:$E;"<=31.12."&C$34;Daten!$F:$F;"*"&$A$33&"*")+SUMMEWENNS(Daten!$B:$B;Daten!$A:$A;$A36;Daten!$E:$E;">=1.1."&C$34;Daten!$E:$E;"<=31.12."&C$34;Daten!$F:$F;"*"&$A$34&"*")

 

Hallo,

ein paar Hilfsspalten:

Arbeitsblatt mit dem Namen 'Daten'
*	A	B	C	D	E	F	G	H	I	J	K
1	Lieferantennr.	Menge	Preis	Wert	Datum	Bezeichnung	Jahr	Name	Alex	Hans	Peter
2	70021	1	1,00 €	1,00	02.01.2012	blablabla Alex blablabla	2012	Alex	1	0	0
3	70077	1	1,00 €	1,00	02.01.2012	blablablabla Alex blabla	2012	Alex	1	0	0
4	70504	1	1,00 €	1,00	02.01.2012	blabla Alex blablablabla	2012	Alex	1	0	0
5	70799	1	1,00 €	1,00	02.01.2012	blablabla Alex blablabla	2012	Alex	1	0	0
6	70829	1	1,00 €	1,00	02.01.2012	blablablabla Alex blabla	2012	Alex	1	0	0
7	70021	2	10,00 €	20,00	02.01.2013	blablabla Peter blablabla	2013	Peter	0	0	3

Zelle	Formel
G2	=JAHR(E2)
H2	=INDEX($I$1:$K$1;MAX($I2:$K2))
I2	=ISTZAHL(SUCHEN(I$1;$F2))*SPALTE(A$1)
J2	=ISTZAHL(SUCHEN(J$1;$F2))*SPALTE(B$1)
K2	=ISTZAHL(SUCHEN(K$1;$F2))*SPALTE(C$1)

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

und Du kannst das wunderbar mit einer Pivot auswerten (braucht keine Stunden mehr!)

Arbeitsblatt mit dem Namen 'Tabelle1'
*	A	B	C	D	E	F	G
1	Jahr	(Alle)	*	*	*	*	*
2	*	*	*	*	*	*	*
3	*	Spaltenbeschriftungen	*	*	*	*	*
4	*	Alex	*	Hans	*	Peter	*
5	Zeilenbeschriftungen	Stückzahl	Gesamtwert	Stückzahl	Gesamtwert	Stückzahl	Gesamtwert
6	70021	1	1	3	300	2	20
7	70077	1	1	3	300	2	20
8	70504	1	1	3	300	2	20
9	70799	1	1	3	300	2	20
10	70829	1	1	3	300	2	20

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

 

Hallo BoskoBiati,

klappt einwandfrei, vielen Dank.

Gruß Alex