Office: (Office 2007) SummeWenn mit mehreren Ausschlusskriterien

Hallo!

Mein Problem ist an sich mit der normalen "SummeWenn-Formel" lösbar.
Nur handelt es sich bei meiner Problematik um sehr viele Kriterien, die ich einzeln natürlich auflisten könnte, jedoch liebe jene wählen möchte, die NICHT enthalten sein sollen.

Wenn ich nur ein Kriterium ausschließen wollte, funktioniert das ganz einfach:
=summewenn(A1:A20;"Kriterium1";F1:F20)

Wie bringe ich 2 (Ausschluss-)Kriterien unter?

Könnt ihr mir helfen?

:)

 

Hallo Stefano20,

das gienge zum Beispiel mit SUMMENPRODUKT:

=SUMMENPRODUKT((A1:A20Kriterium1)*(A1:A20Kriterium2)*(F1:F20))

wobei Kriterium1 und Kriterium2 ein Bezug, eine Zahl, ein Text in Anführungszeichen oder sonst ein gültiger Excelausdruck sein können.

Gruß Ingolf

 

Hallo Ingolf!

Danke für deinen Hinweis.

In meinem Fall sieht die Formel so aus:
=SUMMENPRODUKT((C7:C40000"UKJ-KARD")*(C7:C40000"UKJ-KIT")*(AJ7:AJ40000))

Als Ergebnis habe ich jedoch die Fehlermeldung "WERT!".

- In Spalte C sind die Bezeichnungen, aus denen nach vielen Suchkriterien, jedoch NICHT nach UKJ-KARD & UKJ-KIT ausgewählt werden soll.
- In der Spalte AJ sind die Zahlenwerte, welche summiert werden sollen, wenn die genannten Kriterien NICHT in Spalte C stehen.

Was stimmt noch nicht?

 

Hallo Stefano,

ich dachte, die beiden Kriterien sollten eben gerade nicht in Spalte C stehen?

Gruß Ingolf

 

Richtig, wenn sie drinstehen, soll nicht gezählt werden.

Sorry, hatte mich zuvor verschrieben. (bin schon etwas wirr.)


(ich ändere meinen Beitrag zuvor)

 

Hallo Stefano,

dann sind in AJ7:AJ40000 Zellen enthalten, die keine Zahl, sondern einen Text enthalten. Das können auch Zahlen als Text sein - sieht aus wie eine Zahl, ist aber keine richtige Zahl, sondern eben Text. Eine solche Zahl als Text lässt sich in eine richtige Zahl umwandeln, indem man sie mit 1 multipliziert. Dazu schreibst du eine 1 in eine leere Zelle, die mit dem Zahlenformat Standard formatiert ist. Dann kopierst du die Zelle in die Zwischenablage. Nun markierst du die Spalte AJ und gehst über das Menü Bearbeiten | Inhalte einfügen. Im aufgehenden Dialogfenster aktivierst du die Optionen Werte und Multiplizieren und klickst auf OK.

Gruß Ingolf

 

Ingolf, ich danke dir!

Es funktioniert. Super! Ich freue mich.

(Ganz, ganz kleiner Beigeschmack: Ich habe jetzt viele Nullen in der Spalte AJ geschaffen, die ich zuvor mit Hilfe einer Formel aus optischen Gründen entfernt hatte. Das ist aber nebensächlich und für das Ergebnis nicht relevant.)

Eine Bonusfrage:

Was wäre, wenn ich die Kriterien nicht in einer Spalte, sondern als Tabellenkopf in einer Zeile hätte. Da funktioniert das SummenProdukt nicht, oder?

 

Hallo Stefano,

die Anzeige von Nullen kannst du entweder generell unterdrücken, indem du über Menü Extras | Optionen gehst und dort im Reiter Ansicht die Option Nullwerte deaktivierst. Oder du vergibst für die Spalte AJ ein benutzerdefiniertes Zahlenformat, das die Anzeige von Nullen nicht vorsieht (keine Formatierungsangaben hinter dem zweiten Semikolon).

Gruß Ingolf

 

Hallo Stefano,

ich habe die Ergänzung deines letzten Beitrages erst nach meiner Antwort gesehen. Du meinst, die Kriterien stehen in einer Zeile, die Werte dagegen weiterhin in einer Spalte? Mit einer kleinen Änderung geht auch das. Die Zeile mit den Kriterien muss natürlich genauso viele Zellen haben, wie die Spalte mit den Werten! Dann musst du nur entweder die Kriterienbezüge oder den Wertebezug mit der Funktion MTRANS transponieren. Hier mal ein Beispiel mit transponiertem Wertebezug:

=SUMMENPRODUKT((A1:E1"UKJ-KARD")*(A1:E1"UKJ-KIT")*(MTRANS(F1:F5)))

oder mit transponierten Kriterienbezügen:

=SUMMENPRODUKT((MTRANS(A1:E1)"UKJ-KARD")*(MTRANS(A1:E1)"UKJ-KIT)*(F1:F5))

Gruß Ingolf

 

Hallo Ingolf!

Hat alles prima geklappt.
Danke, danke.

Da ich jetzt so kompetente Hilfe an der Hand habe, bitte ich dich noch um eine weitere Lösung:

folgende Ausgangstabelle:

UKJ KIM ZR CUK
5 10 30 12
6 15 8 9
3 4 21 25


In F2 möchte ich nun z.B. die Summe aller Werte aus Zeile 2, außer den Wert in Spalte "B".
In F3 ebenso usw..
Bitte bedenke dabei, dass ich in der realen Datei sehr viele Spalten habe und nicht alle (außer "B") in einer Formel auflisten möchte, sondern eben nur diejenige, die ausgeklammert werden soll.

Kannst du mir ein weiteres Mal helfen?

 

Hallo Stefano,

die Formel in F2 kannst du einfach runterkopieren:

	A	B	C	D	E	F
1	UKJ	KIM	ZR	CUK
2	5	10	30	12		47
3	6	15	8	9		23
4	3	4	21	25		49

F2 : =SUMMENPRODUKT(($A:$D<>"KIM")*($A2:$D2))

Gruß Ingolf

 

Ja, funktioniert.

Und wenn ich Spalte B (KIM) und Spalte D (CUK) auschließen möchte?

Dann ist aber Schluss..

OOOps, Ingolf, du brauchst nicht antworten. Das dürfte ja wie oben beschrieben funktionieren.


Ich danke dir für deine Hilfe!