Die Auf­gabe ist klar be­schrie­ben: Aus ei­nem de­fi­nier­ten Bere­ich sol­len nur die pos­i­tiv­en Zah­len ad­diert, sum­miert wer­den. Ne­ben (prinzip­iell) drei Formel-Lösun­gen wird hier auch eine beispiel­hafte VBA-Lö­sung (wenn auch nicht die ein­zig mög­li­che) vor­ge­stellt.

Alle gemacht­en An­ga­ben be­zie­hen sich auf die­ses Arbeits­blatt, welch­es Sie her­un­ter la­den. Die Map­pe ist be­wusst im Binär­for­mat gespe­ichert, weil so die Da­tei­grö­ße verklein­ert wird und die Da­tei i.d.R. auch nicht in Fremd­pro­gram­men wie OO oder LO ver­wen­det wer­den kann. Let­zteres ist we­gen des Ma­kros (UDF) und der AG­GRE­GAT()-Funk­tion ge­sche­hen; bei­de sind nicht in je­dem Fall kom­pat­i­bel.

▲ nach oben …

Drei Be­rei­che (plus ei­ner au­ßer Kon­kur­renz)

Im Arbeits­blatt erken­nen Sie drei Bere­iche, wel­che die gle­ichen Zah­len 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 wur­de als Lis­te/Intel­li­gente Ta­bel­le for­matiert. Gle­ich­es trifft für Bere­ich 3 zu, der aber we­gen des einge­set­zten Fil­ters unter­halb der beste­hen­den Dat­en ge­schrie­ben wur­de. Der Vor­teil der Intel­li­gen­ten Ta­bel­le(n): Sie sind dy­na­misch, auch wenn es mehr oder we­ni­ger Zei­len wer­den, die For­mel wird sich im­mer auf die derzeit­ige Grö­ße der Lis­te be­zie­hen.

Als Mus­ter für wirk­lich gro­ße Daten­men­gen ist der Bere­ich I1:I11 als Daten­bank an­ge­legt. Dazu ge­hört auch ein Kri­te­rien-Bere­ich in K1:K2. Die Ge­schwin­dig­keit ist da­bei ein­fach nur Top. Die bei­den zusam­men gehöri­gen Tei­le des DB-Bere­ichs habe ich mit zwei gel­blichen Tö­nen hin­ter­legt.

▲ nach oben …

Die For­meln in den Zel­len

C2: =SUMMEWENN(A2:A11;">0";A2:A11)
Die „of­fi­zi­el­le” Lang­form der SUM­ME­WENN()-Funk­tion. Im Prin­zip: Durch­suche den Bere­ich A2:A11 nach Wer­ten grö­ßer als 0 und ad­die­re die Wer­te aus den gefun­de­nen Zei­len (wie­der­um) aus dem Bere­ich A2:A11. Hin­weis: Sie hät­ten aus den Bere­ich A1:A11 schrei­ben 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ül­tig­keit.

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 ei­ni­ges lang­sa­mer als die Funk­tio­nen SUM­ME­WENN() bzw. SUM­ME­WENNS().

C5: {=SUMME(WENN(A2:A11>0;A2:A11))}
Eine Ma­trix-For­mel. Die ge­schweif­ten Klam­mern dür­fen nicht ein­ge­ge­ben wer­den, Sie ge­ben die For­mel ohne {} ein und schlie­ßen die Ein­ga­be unbe­d­ingt mit der Tas­tenkom­bi­na­tion StrgShiftEin­ga­be ab. Da­durch wird die For­mel zu ein­er Ma­trix-For­mel und die ge­schweif­ten Klam­mern wer­den automa­tisch geset­zt. Wird ger­ne von erfahre­nen Usern einge­set­zt und ge­hört zu den mit­tel schnel­len Vari­anten.

G2: =SUMMEWENN(PlusMinus[Liste];">0";PlusMinus[Liste])
Alle For­meln in Spal­te G be­zie­hen sich auf die Lis­te in Spal­te E; wenn nicht an­ders an­ge­ge­ben, wur­de der Teil inner­halb der Klam­mern durch Klick­en einge­fügt (sie­he auch die Hin­weise). – Hin­weis: Den Ein­trag über­tra­gen Sie, in­dem Sie et­was ober­halb der Über­schrift zei­gen, bis der Maus­cur­sor ein dick­er Pfeil ist und dann Klick­en:

Aus­wahl der Lis­ten-Ein­träge

Im Prin­zip ist die­se For­mel gle­ich aufge­baut wie jene in A2.

G3: =SUMMEWENN(PlusMinus[Liste];">0")
Auch hier gilt: Ein Pen­dant zu A3, je­doch mit Be­zug auf die Adres­se der Intel­li­gen­ten Ta­bel­le.

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

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

C16: =AGGREGAT(9;5;PlusMinus_2[Liste 2])
Da ein geset­zter Fil­ter ja auch die Zei­len der da­ne­ben liegen­den Spal­ten aus­blendet, habe ich die­se Lis­te nach un­ten ver­set­zt. Der Bequem­lichkeit hal­ber ist hier auch eine Intel­li­gente Ta­bel­le ver­wen­det wor­den, ein „nor­maler” Fil­ter hät­te es auch ge­tan.

Die AG­GRE­GAT()-Funk­tion gibt es erst seit der Excel-Ver­sion 2010. Die Beschäf­ti­gung da­mit ist aber ge­wiss loh­nens­wert, in die­sem Bei­spiel wer­den durch ver­schiedene Ar­gu­men­te/Pa­ra­me­ter nur sicht­bare Zel­len berück­sichtigt. Hier be­deu­tet das: Die Funk­tion (hier: 9) ste­ht für die SUM­ME(), die Op­ti­on 5 be­sagt, dass aus­ge­blendete Zei­len nicht berück­sichtigt wer­den und das Ar­ray schlie­ß­lich ist der auszuw­er­tende Bere­ich, hier als Bere­ich­sname der Lis­te.

Vor­ab zum The­ma „Daten­bank”: Um mehr Klar­heit in die Aus­sage der For­mel 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 not­wen­dig, hil­ft aber ge­wiss bei der Inter­pre­ta­tion der For­meln.

M2: =DBSUMME(Datenbank;"Zahlen";K1:K2)
Im Bere­ich Daten­bank soll in der Spal­te mit der Über­schrift „Zah­len” eine Sum­me ge­bil­det wer­den. Als Kri­teri­um gilt das, was im Bere­ich K1:K2 (ge­nau­er ge­sagt: in K2) an­ge­ge­ben ist. – Ma­chen Sie sich ger­ne mit dem The­ma Daten­bank­funk­tio­nen ver­traut, es lohnt sich ge­wiss, wenn Sie zig tau­sen­de von Zel­len aus­wer­ten wol­len.

M3: =DBSUMME(Datenbank;I1;Kriterien)
Prak­tisch wie vor; was an­ders 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) (ge­hört al­les in 1 Zei­le)
Vor­weg ange­merkt: Die For­mel an sich ist rich­tig, nur die Nebenbe­din­gun­gen stim­men nicht. Dar­um ist das Ergeb­nis falsch. – Das ers­te Argu­ment PlusMinus[[#Alle];[Liste]] über­tra­gen Sie am ein­fach­sten, wenn Sie in die Über­schrift der Lis­te Klick­en (also E1) und dann StrgShift↓ betäti­gen. Für das zwei­te Argu­ment PlusMinus[[#Kopfzeilen];[Liste]] ein­fach nur ein Klick in E1 und für das let­zte Argu­ment schrei­ben Sie den Bere­ich per Hand oder sie mar­kie­ren ihn und dann wird automa­tisch der Bere­ich­sname über­nom­men.

So weit, so gut. Das natür­lich fal­sche Ergeb­nis kommt da­durch 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 an­de­ren Wor­ten: In der Daten­bank muss eine Spal­te mit jen­er Über­schrift exis­tie­ren, wel­che ex­akt der Über­schrift der Kri­te­rien ent­spricht. Än­dern Sie E1 in Zah­len und das Ergeb­nis stimmt. Oder Sie leg­en ei­nen zweit­en Kri­te­rien-Bere­ich mit der Über­schrift Lis­te an; der even­tu­ell ver­wen­dete Bere­ich­sname muss sich natür­lich vom bish­eri­gen unter­schei­den.

▲ nach oben …

Ei­ge­ne Funk­ti­on (UDF) per VBA

Falls Sie dar­auf an­ge­wie­sen sind, solch eine bed­ingte Sum­mierung in ei­nem Ma­kro anzuwen­den, genü­gen we­ni­ge Zei­len 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

Zu­ge­ge­ben, es ist nicht die schnell­ste Vari­ante, aber als Grund­ver­sion reicht es vol­lkom­men aus. Wie üb­lich 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 Ih­nen der Bei­trag ge­fal­len?
Er­leich­tert die­ser Bei­trag Ihre Ar­beit?

Dann wür­de ich mich über ei­nen Bei­trag Ihrer­seits z.B. 1,00 € freu­en … (← Klick mich!)