Summe mit Bedingungen

 Es führen viele Wege nach Rom … 

Beispielsweise nur positive Zahlen summieren

Die Aufgabe ist klar beschrieben: Aus einem definierten Bereich sollen nur die positiven Zahlen addiert, summiert werden. Neben (prinzipiell) drei Formel-Lösungen wird hier auch eine beispielhafte VBA-Lösung (wenn auch nicht die einzig mögliche) vorgestellt.

Alle gemachten Angaben beziehen sich auf dieses Arbeitsblatt, welches Sie herunter laden. Die Mappe ist bewusst im Binärformat gespeichert, weil so die Dateigröße verkleinert wird und die Datei i.d.R. auch nicht in Fremdprogrammen wie OO oder LO verwendet werden kann. Letzteres ist wegen des Makros (UDF) und der AGGREGAT()-Funktion geschehen; beide sind nicht in jedem Fall kompatibel.

▲ nach oben …

Drei Bereiche (plus einer außer Konkurrenz)

Im Arbeitsblatt erkennen Sie drei Bereiche, welche die gleichen Zahlen enthalten aber dennoch unterschiedlich aufgebaut sind. Bereich 1: A1:A11, Bereich 2: E1:E11, Bereich 3: A15:A25. – Bereich 1 ist ein ganz „normaler“ Bereich, Bereich 2 wurde als Liste/Intelligente Tabelle formatiert. Gleiches trifft für Bereich 3 zu, der aber wegen des eingesetzten Filters unterhalb der bestehenden Daten geschrieben wurde. Der Vorteil der Intelligenten Tabelle(n): Sie sind dynamisch, auch wenn es mehr oder weniger Zeilen werden, die Formel wird sich immer auf die derzeitige Größe der Liste beziehen.

Als Muster für wirklich große Datenmengen ist der Bereich I1:I11 als Datenbank angelegt. Dazu gehört auch ein Kriterien-Bereich in K1:K2. Die Geschwindigkeit ist dabei einfach nur Top. Die beiden zusammen gehörigen Teile des DB-Bereichs habe ich mit zwei gelblichen Tönen hinterlegt.

▲ nach oben …

Die Formeln in den Zellen

C2: =SUMMEWENN(A2:A11;">0";A2:A11)
Die „offizielle“ Langform der SUMMEWENN()-Funktion. Im Prinzip: Durchsuche den Bereich A2:A11 nach Werten größer als 0 und addiere die Werte aus den gefundenen Zeilen (wiederum) aus dem Bereich A2:A11. Hinweis: Sie hätten aus den Bereich A1:A11 schreiben können, der Text in A1 wäre dann ignoriert worden.

C3: =SUMMEWENN(A2:A11;">0")
Die Kurzform der Funktion reicht hier vollkommen aus, da der zu summierende Bereich identisch ist mit dem zu durchsuchenden Bereich. Der Hinweis hierüber hat gleichermaßen Gültigkeit.

C4: =SUMMENPRODUKT((A2:A11>0)*A2:A11)
Eine durchaus interessante Variante. Insbesondere bei „reichlich“ Vergleichskriterien sinnvoll. Aber bei wirklich großen Datenmengen ist sie einiges langsamer als die Funktionen SUMMEWENN() bzw. SUMMEWENNS().

C5: {=SUMME(WENN(A2:A11>0;A2:A11))}
Eine Matrix-Formel. Die geschweiften Klammern dürfen nicht eingegeben werden, Sie geben die Formel ohne {} ein und schließen die Eingabe unbedingt mit der Tastenkombination StrgShiftEingabe ab. Dadurch wird die Formel zu einer Matrix-Formel und die geschweiften Klammern werden automatisch gesetzt. Wird gerne von erfahrenen Usern eingesetzt und gehört zu den mittel schnellen Varianten.

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 innerhalb der Klammern durch Klicken eingefügt (siehe auch die Hinweise). – Hinweis: Den Eintrag übertragen Sie, indem Sie etwas oberhalb der Überschrift zeigen, bis der Mauscursor ein dicker Pfeil ist und dann klicken:

Einfache Auswahl der kompletten Listen-Einträge

Auswahl der Listen-Einträge

Im Prinzip ist diese Formel gleich aufgebaut wie jene in A2.

G3: =SUMMEWENN(PlusMinus[Liste];">0")
Auch hier gilt: Ein Pendant zu A3, jedoch mit Bezug auf die Adresse der Intelligenten Tabelle.

G4: =SUMMENPRODUKT((PlusMinus[Liste]>0)*PlusMinus[Liste])
Nicht wirklich erwähnenswert, siehe auch A4.

G5: {=SUMME(WENN(PlusMinus[Liste]>0;PlusMinus[Liste]))} 
Auch die Matrix-Formel funktioniert in den Listen…

C16: =AGGREGAT(9;5;PlusMinus_2[Liste 2])
Da ein gesetzter Filter ja auch die Zeilen der daneben liegenden Spalten ausblendet, habe ich diese Liste nach unten versetzt. Der Bequemlichkeit halber ist hier auch eine Intelligente Tabelle verwendet worden, ein „normaler“ Filter hätte es auch getan.

Die AGGREGAT()-Funktion gibt es erst seit der Excel-Version 2010. Die Beschäftigung damit ist aber gewiss lohnenswert, in diesem Beispiel werden durch verschiedene Argumente/Parameter nur sichtbare Zellen berücksichtigt. Hier bedeutet das: Die Funktion (hier: 9) steht für die SUMME(), die Option 5 besagt, dass ausgeblendete Zeilen nicht berücksichtigt werden und das Array schließlich ist der auszuwertende Bereich, hier als Bereichsname der Liste.

Vorab zum Thema „Datenbank“: Um mehr Klarheit in die Aussage der Formel zu bringen, habe ich I1:I11 mit dem Bereichsnamen Datenbank und K1:K2 mit Kriterien versehen. Das ist nicht zwingend notwendig, hilft aber gewiss bei der Interpretation der Formeln.

M2: =DBSUMME(Datenbank;"Zahlen";K1:K2)
Im Bereich Datenbank soll in der Spalte mit der Überschrift „Zahlen“ eine Summe gebildet werden. Als Kriterium gilt das, was im Bereich K1:K2 (genauer gesagt: in K2) angegeben ist. – Machen Sie sich gerne mit dem Thema Datenbankfunktionen vertraut, es lohnt sich gewiss, wenn Sie zig tausende von Zellen auswerten wollen.

M3: =DBSUMME(Datenbank;I1;Kriterien)
Praktisch wie vor; was anders ist: Statt der Überschrift als Text ist hier eine Zelladresse verwendet worden und bei den Kriterien auch der Bereichsname.

M4: =DBSUMME(PlusMinus[[#Alle];[Liste]];PlusMinus[[#Kopfzeilen];[Liste]];Kriterien) (gehört alles in 1 Zeile)
Vorweg angemerkt: Die Formel an sich ist richtig, nur die Nebenbedingungen stimmen nicht. Darum ist das Ergebnis falsch. – Das erste Argument PlusMinus[[#Alle];[Liste]] übertragen Sie am einfachsten, wenn Sie in die Überschrift der Liste klicken (also E1) und dann StrgShift betätigen. Für das zweite Argument PlusMinus[[#Kopfzeilen];[Liste]] einfach nur ein Klick in E1 und für das letzte Argument schreiben Sie den Bereich per Hand oder sie markieren ihn und dann wird automatisch der Bereichsname übernommen.

So weit, so gut. Das natürlich falsche Ergebnis kommt dadurch zustande, dass die Überschrift des Kriterien-Bereichs nicht mit der Überschrift des auszuwertenden Datenbank-Bereichs übereinstimmt. Mit anderen Worten: In der Datenbank muss eine Spalte mit jener Überschrift existieren, welche exakt der Überschrift der Kriterien entspricht. Ändern Sie E1 in Zahlen und das Ergebnis stimmt. Oder Sie legen einen zweiten Kriterien-Bereich mit der Überschrift Liste an; der eventuell verwendete Bereichsname muss sich natürlich vom bisherigen unterscheiden.

▲ nach oben …

Eigene Funktion (UDF) per VBA

Falls Sie darauf angewiesen sind, solch eine bedingte Summierung in einem Makro anzuwenden, genügen wenige Zeilen Code, um das Ziel zu erreichen:

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 schnellste Variante, aber als Grundversion reicht es vollkommen aus. Wie üblich muss die UDF in ein Modul eingebunden werden, mehr dazu können Sie hier nachlesen.

▲ nach oben …

Dieser Beitrag wurde unter Excel-Funktionen, Mit VBA/Makro, Musterlösungen, Ohne Makro/VBA, Rechnen & Zahlen, Tipps und Tricks, Verschiedenes, Wege nach Rom abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.