Die Auf­gabe ist klar beschrieben: Aus einem definierten Bere­ich sollen nur die pos­i­tiv­en Zahlen addiert, sum­miert wer­den. Neben (prinzip­iell) drei Formel-Lösun­gen wird hier auch eine beispiel­hafte VBA-Lösung (wenn auch nicht die einzig mögliche) vorgestellt.

Alle gemacht­en Angaben beziehen sich auf dieses Arbeits­blatt, welch­es Sie herunter laden. Die Mappe ist bewusst im Binär­for­mat gespe­ichert, weil so die Dateigröße verklein­ert wird und die Datei i.d.R. auch nicht in Fremd­pro­gram­men wie OO oder LO ver­wen­det wer­den kann. Let­zteres ist wegen des Makros (UDF) und der AGGREGAT()-Funk­tion geschehen; bei­de sind nicht in jedem Fall kom­pat­i­bel.

▲ nach oben …

Drei Bereiche (plus einer außer Konkurrenz)

Im Arbeits­blatt erken­nen Sie drei Bere­iche, welche die gle­ichen Zahlen enthal­ten aber den­noch unter­schiedlich aufge­baut sind. Bere­ich 1: A1:A11, Bere­ich 2: E1:E11, Bere­ich 3: A15:A25. – Bere­ich 1 ist ein ganz „nor­maler” Bere­ich, Bere­ich 2 wurde als Liste/Intel­li­gente Tabelle for­matiert. Gle­ich­es trifft für Bere­ich 3 zu, der aber wegen des einge­set­zten Fil­ters unter­halb der beste­hen­den Dat­en geschrieben wurde. Der Vorteil der Intel­li­gen­ten Tabelle(n): Sie sind dynamisch, auch wenn es mehr oder weniger Zeilen wer­den, die Formel wird sich immer auf die derzeit­ige Größe der Liste beziehen.

Als Muster für wirk­lich große Daten­men­gen ist der Bere­ich I1:I11 als Daten­bank angelegt. Dazu gehört auch ein Kri­te­rien-Bere­ich in K1:K2. Die Geschwindigkeit ist dabei ein­fach nur Top. Die bei­den zusam­men gehöri­gen Teile des DB-Bere­ichs habe ich mit zwei gel­blichen Tönen hin­ter­legt.

▲ nach oben …

Die Formeln in den Zellen

C2: =SUMMEWENN(A2:A11;">0";A2:A11)
Die „offizielle” Lang­form der SUMMEWENN()-Funk­tion. Im Prinzip: Durch­suche den Bere­ich A2:A11 nach Werten größer als 0 und addiere die Werte aus den gefun­de­nen Zeilen (wiederum) aus dem Bere­ich A2:A11. Hin­weis: Sie hät­ten aus den Bere­ich A1:A11 schreiben kön­nen, der Text in A1 wäre dann ignori­ert wor­den.

C3: =SUMMEWENN(A2:A11;">0")
Die Kurz­form der Funk­tion reicht hier vol­lkom­men aus, da der zu sum­mierende Bere­ich iden­tisch ist mit dem zu durch­suchen­den Bere­ich. Der Hin­weis hierüber hat gle­icher­maßen Gültigkeit.

C4: =SUMMENPRODUKT((A2:A11>0)*A2:A11)
Eine dur­chaus inter­es­sante Vari­ante. Ins­beson­dere bei „reich­lich” Ver­gle­ich­skri­te­rien sin­nvoll. Aber bei wirk­lich großen Daten­men­gen ist sie einiges langsamer als die Funk­tio­nen SUMMEWENN() bzw. SUMMEWENNS().

C5: {=SUMME(WENN(A2:A11>0;A2:A11))}
Eine Matrix-Formel. Die geschweiften Klam­mern dür­fen nicht eingegeben wer­den, Sie geben die Formel ohne {} ein und schließen die Eingabe unbe­d­ingt mit der Tas­tenkom­bi­na­tion StrgShiftEingabe ab. Dadurch wird die Formel zu ein­er Matrix-Formel und die geschweiften Klam­mern wer­den automa­tisch geset­zt. Wird gerne von erfahre­nen Usern einge­set­zt und gehört zu den mit­tel schnellen Vari­anten.

G2: =SUMMEWENN(PlusMinus[Liste];">0";PlusMinus[Liste])
Alle Formeln in Spalte G beziehen sich auf die Liste in Spalte E; wenn nicht anders angegeben, wurde der Teil inner­halb der Klam­mern durch Klick­en einge­fügt (siehe auch die Hin­weise). – Hin­weis: Den Ein­trag über­tra­gen Sie, indem Sie etwas ober­halb der Über­schrift zeigen, bis der Maus­cur­sor ein dick­er Pfeil ist und dann Klick­en:

Auswahl der Lis­ten-Ein­träge

Im Prinzip ist diese Formel gle­ich aufge­baut wie jene in A2.

G3: =SUMMEWENN(PlusMinus[Liste];">0")
Auch hier gilt: Ein Pen­dant zu A3, jedoch mit Bezug auf die Adresse der Intel­li­gen­ten Tabelle.

G4: =SUMMENPRODUKT((PlusMinus[Liste]>0)*PlusMinus[Liste])
Nicht wirk­lich erwäh­nenswert, siehe auch A4.

G5: {=SUMME(WENN(PlusMinus[Liste]>0;PlusMinus[Liste]))} 
Auch die Matrix-Formel funk­tion­iert in den Lis­ten…

C16: =AGGREGAT(9;5;PlusMinus_2[Liste 2])
Da ein geset­zter Fil­ter ja auch die Zeilen der daneben liegen­den Spal­ten aus­blendet, habe ich diese Liste nach unten ver­set­zt. Der Bequem­lichkeit hal­ber ist hier auch eine Intel­li­gente Tabelle ver­wen­det wor­den, ein „nor­maler” Fil­ter hätte es auch getan.

Die AGGREGAT()-Funk­tion gibt es erst seit der Excel-Ver­sion 2010. Die Beschäf­ti­gung damit ist aber gewiss lohnenswert, in diesem Beispiel wer­den durch ver­schiedene Argumente/Parameter nur sicht­bare Zellen berück­sichtigt. Hier bedeutet das: Die Funk­tion (hier: 9) ste­ht für die SUMME(), die Option 5 besagt, dass aus­ge­blendete Zeilen nicht berück­sichtigt wer­den und das Array schließlich ist der auszuw­er­tende Bere­ich, hier als Bere­ich­sname der Liste.

Vor­ab zum The­ma „Daten­bank”: Um mehr Klarheit in die Aus­sage der Formel zu brin­gen, habe ich I1:I11 mit dem Bere­ich­sna­men Daten­bank und K1:K2 mit Kri­te­rien verse­hen. Das ist nicht zwin­gend notwendig, hil­ft aber gewiss bei der Inter­pre­ta­tion der Formeln.

M2: =DBSUMME(Datenbank;"Zahlen";K1:K2)
Im Bere­ich Daten­bank soll in der Spalte mit der Über­schrift „Zahlen” eine Summe gebildet wer­den. Als Kri­teri­um gilt das, was im Bere­ich K1:K2 (genauer gesagt: in K2) angegeben ist. – Machen Sie sich gerne mit dem The­ma Daten­bank­funk­tio­nen ver­traut, es lohnt sich gewiss, wenn Sie zig tausende von Zellen auswerten wollen.

M3: =DBSUMME(Datenbank;I1;Kriterien)
Prak­tisch wie vor; was anders ist: Statt der Über­schrift als Text ist hier eine Zel­ladresse ver­wen­det wor­den und bei den Kri­te­rien auch der Bere­ich­sname.

M4: =DBSUMME(PlusMinus[[#Alle];[Liste]];PlusMinus[[#Kopfzeilen];[Liste]];Kriterien) (gehört alles in 1 Zeile)
Vor­weg ange­merkt: Die Formel an sich ist richtig, nur die Nebenbe­din­gun­gen stim­men nicht. Darum ist das Ergeb­nis falsch. – Das erste Argu­ment PlusMinus[[#Alle];[Liste]] über­tra­gen Sie am ein­fach­sten, wenn Sie in die Über­schrift der Liste Klick­en (also E1) und dann StrgShift↓ betäti­gen. Für das zweite Argu­ment PlusMinus[[#Kopfzeilen];[Liste]] ein­fach nur ein Klick in E1 und für das let­zte Argu­ment schreiben Sie den Bere­ich per Hand oder sie markieren ihn und dann wird automa­tisch der Bere­ich­sname über­nom­men.

So weit, so gut. Das natür­lich falsche Ergeb­nis kommt dadurch zus­tande, dass die Über­schrift des Kri­te­rien-Bere­ichs nicht mit der Über­schrift des auszuw­er­tenden Daten­bank-Bere­ichs übere­in­stimmt. Mit anderen Worten: In der Daten­bank muss eine Spalte mit jen­er Über­schrift existieren, welche exakt der Über­schrift der Kri­te­rien entspricht. Ändern Sie E1 in Zahlen und das Ergeb­nis stimmt. Oder Sie leg­en einen zweit­en Kri­te­rien-Bere­ich mit der Über­schrift Liste an; der eventuell ver­wen­dete Bere­ich­sname muss sich natür­lich vom bish­eri­gen unter­schei­den.

▲ nach oben …

Eigene Funktion (UDF) per VBA

Falls Sie darauf angewiesen sind, solch eine bed­ingte Sum­mierung in einem Makro anzuwen­den, genü­gen wenige Zeilen Code, um das Ziel zu erre­ichen:

Function NurPositiveZahlen(rng As Range) As Double
   Dim c As Range, Rc As Double
   For Each c In rng
      If IsNumeric(c) And c > 0 Then Rc = Rc + c
   Next c
   NurPositiveZahlen = Rc
End Function

Zugegeben, es ist nicht die schnell­ste Vari­ante, aber als Grund­ver­sion reicht es vol­lkom­men aus. Wie üblich muss die UDF in ein Mod­ul einge­bun­den wer­den, mehr dazu kön­nen Sie hier nach­le­sen.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 1,00 € freuen … (← Klick mich!)