Summe nach Zellfarbe bilden

Eine SUMMEWENN-Funktion berücksichtigt zwar Kriterien und bildet bei Erfüllen dieser die Summe der jeweiligen Werte, beispielsweise die Zellfarbe kann allerdings nicht ohne weiteres als Kriterium erkannt und berücksichtigt werden. Dazu benötigt es einen zusätzlichen Schritt, welchen wir in diesem Beitrag betrachten:

Im Menüband gehst Du unter "Formeln" auf "Name definieren". Gib dort als Name "Farbe" ein - dies ist später der Name einer Formel, welche wir in eine Hilfsspalte eingeben werden (Du kannst daraufhin "=Farbe" in eine Zelle eingeben). Bei "Bezieht sich auf" gibst Du folgendes ein:

=ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))

Zusammengefasst sieht Dein definierter Name wie folgt aus:

ZellfarbeName

[Der Bezug ZS(-1) ist ein Referenzbezug: Dieser bedeutet, dass Du auf die selbe Zeile (Z), allerdings auf eine Spalte (S) nach links (-1) zugreifst.]

Wenn Du nun eine Spalte rechts Deiner farbigen Zelle "=Farbe" eingibst, wirst Du eine Zahl erhalten - diese bezieht sich auf die Zellfarbe. Wenn Du die Zellfarbe änderst, wird sich auch das Resultat in der Zelle nebenan verändern, sobald Du die "=Farbe"-Formel aktualisierst (oder F9 auf der Tastatur zur Kalkulation des gesamten Tabellenblatts drückst). Nun kannst Du die bekannte SUMMEWENN-Formel verwenden und alle Werte summieren, welche in der Spalte nebenan den entsprechenden Farbcode aufweisen. In diesem Beispiel erstelle ich die Summe aller orangen Zellen (mit dem Farbcode 44):

SummewennZellfarbe

(10 + 2 + 3 + 5 + 10 = 30)

Wichtig zu beachten ist, dass Du Deine Datei daraufhin als .xlsm (Makro-Datei) abspeichern musst, damit die "=Farbe"-Funktion auch zu einem späteren Zeitpunkt wieder funktionsfähig ist.

26 Gedanken zu “Summe nach Zellfarbe bilden

  1. Guten Tag,

    ich habe die Anleitung genauso umgesetzt. Die Formel erkennt den Farbcode beim ersten Mal korrekt und ich kann mit dem Farbcode eine Summierung erzielen. Ändere ich jedoch die Farbe (das passiert automatisch durch eine Bedingung, sobald ich eine andere Jahreszahl eingebe) erkennt die Formel die neue Farbe nicht und ändert nicht den angezeigten Farbcode.
    Meine Berechnungsoptionen sind auf automatisch und alles als Zahlen formatiert.
    Interessant ist jedoch, dass wenn ich bei einem Feld die Farbe manuell änder der Farbcode nach der aktualisierung sich ändert.
    Ich hoffe es war verständlich wo genau mein Problem ist und hoffe mir kann jemand helfen.

  2. Hallo Vincenz
    Danke für Deinen Kommentar. Du hast alles korrekt eingepflegt, super. Der eingepflegte Name/Die selbst angelegte Funktion ("=Farbe") benötigt ein Aktualisieren, damit der Farbcode neu berechnet wird. Damit Du nicht alle Formeln einzeln anklicken und per Enter erneut bestätigen musst, kannst Du einfach "F9" auf Deiner Tastatur drücken - so werden alle Formeln neu berechnet (alternativ gehe via Menüband "Formeln" --> "Neu berechnen").

    Deine "Jahreszahl" steht in einer bestimmten Zelle? Du könntest ein Makro auslösen lassen, sobald die entsprechende Zelle sich ändert. Siehe hierzu den Beitrag "Live-Suche in der Excel-Suchmaschine": https://excelblog.ch/live-suche-in-der-excel-suchmaschine/
    Darin ist erklärt, wo Du Code wie "If target.Row = 1 And target.Column = 2 Then" (mit dem korrekten Spalten- und Zeilen-Bezug referenzierend auf die Zelle Deiner "Jahreszahl") und so weiter einpflegen musst. Damit das Makro beim Anpassen der Jahreszahl quasi "F9" zum Berechnen der Formeln auslöst, füge in dem Code zudem das hier ein:
    "Application.Calculate"
    Ich hoffe, das löst Deine Anfrage.
    Viel Erfolg und eine schöne Weihnachtszeit,
    Roman

  3. hallo ich habe ebenfalls alles so gemacht wie angegen.
    ich habe eine Formel angelegt mit dem Name "Farbe" und die Formel so wie beschrieben darin reinkopiert
    dann eine Zelle mit meiner Fareb angelegt und daneben die Formel =Farbe eingefügt.
    Dennoch bekomme ich keine Farbe sonder die Fehlermeldung "Name"
    ich bin seit zwei Tagen am Suchen woran das liegen könnte, denn Makros ist aktivert und die Datei wurde auch als xlsm Datei gespeicht, Auch den Refresh mit F9 habe ich mehrmals versucht aber es hilft alle nichts.
    ich habe office365
    brauch Hilfe

    • Hallo Carmen
      Ich habe den Fall soeben erneut in einer komplett neuen Excel-Datei nachgestellt und bei mir hat es funktioniert. Die beschriebenen Schritte in Deinem Kommentar scheinen korrekt zu sein.
      Ist es möglich, dass Du mir die (Beispiel-)Datei per Mail zustellst? Du kannst mir gerne direkt an roman@excelblog.ch schreiben.
      Beste Grüsse
      Roman

  4. Grüß euch,
    Ich habe alle Schritte befolgt und es hat auch alles vor dem Speichern geklappt.
    Nachdem ich die Mappe, auch als macro fähige Datei, abgespeichert habe und wieder öffne erkennt er die Formel nicht mehr und spuckt mir #NAME? aus.
    Woran könnte es liegen? Makros aktivieren?
    Danke im voraus
    LG Patrick

    • Hallo Patrick
      Hast Du es mittlerweile erneut versucht oder in einer zweiten Datei nachkonstruiert und hat es da funktioniert?
      Könntest Du mir allenfalls die Datei oder einen Ausschnitt davon per Mail zustellen? Da kann ich es mir sehr gerne anschauen. (roman@excelblog.ch)
      Beste Grüsse
      Roman

  5. Hallo
    Das mit der Farberkennung funktioniert bei mir, wie beschrieben, jedoch funktioniert die "summewenn"-Funktion nicht: =SUMMEWENN(C42:BL42;"="&BN42); in der Zelle BN42 steht der Farbwert.

    Kann mir jemand bitte helfen?

    Danke
    Gruß Hansjörg

    • Hallo Hansjörg

      Wie sieht Deine Tabelle genau aus? Stehen die Werte, die Du aufsummieren möchtest, untereinander? Gemäss Deiner Formel und dem Bereich C42:BL42 bin ich mir da nicht ganz sicher - stehen die Werte womöglich nebeneinander?

      Welcher Code hat Deine Farbe? Im Beispiel unten verwende ich einmal die 99, damit Du es besser erkennst und auf Deine Gegebenheiten anpassen kannst.

      Falls z.B. Deine Werte in Spalte C stehen, wäre die SUMMEWENN-Formel:
      =SUMMEWENN(BN:BN;99;C:C)
      So zählst Du alle Werte in Spalte C zusammen, bei denen in Spalte BN die 99 steht.

      Falls Deine Werte tatsächlich von C42 bis BL42 stehen, frage ich mich, wo Deine Summe stehen soll? Ist das nur auf diese Zeile bezogen? Diese Formel könnte Dir das gewünschte Resultat geben:
      =SUMMEWENN(BN;99;C42:BL42)

      Die 99 oben entspricht einfach Deinem Farbcode. Du musst keine komplizierten Kombinationen mit "="&BN42 oder dergleichen machen. Schreibe einfach die effektive Zahl (Farbcode) hinein. Wenn Du auf eine Zelle referenzieren möchtest, wo dieser Farbcode sicher drinsteht (z.B. in BN42), dann verwende einfach den normalen Bezug wie beispielsweise BN42 (oder allenfalls mit Dollarzeichen fixiert/absolut gesetzt: $BN$42).

      Ich hoffe, die Inputs helfen Dir.

      Liebe Grüsse
      Roman

  6. Guten Tag!

    Viele Dank für die zahlreiche Tipps und Tricks. Ich würde gern aus meiner Farbpalette "lila" wählen, weiß aber nicht welcher Farbcode dies ist und wo ich ihn finde, damit ich ihn im Namensmanager anpassen kann.

    Viele Grüße
    Johanna

    • Hallo Johanna

      Du kannst jede beliebige Farbe verwenden. Färbe einfach einmal eine beliebige Zelle mit Deiner Farbe "lila" ein (z.B. in Zelle A1). Wenn Du nun einen neuen Namen definierst (exakt wie im Beitrag beschrieben), dann kannst Du in der Zelle nebenan =Farbe reinschreiben und Du wirst den Farbcode erhalten.

      Beachte: Du definierst Deinen Namen und fügst die Formel "=ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))" ein, wie im Beitrag beschrieben. Die 63 ist nicht etwa der Farbcode, sondern eine Funktion, die eben hilft, einen Farbcode zu ermitteln!

      Nun weisst Du, welchen Farbcode Deine gewünschte Farbe hat (z.B. 7); so kannst Du die SUMMEWENN-Formel anwenden (z.B. "=SUMMEWENN(B:B;7)").

      Lass mich wissen, falls Du noch Fragen hast oder etwas nicht funktioniert hat -- oder natürlich auch gerne, wenn Du die Lösung implementieren konntest!

      Liebe Grüsse
      Roman

  7. Hallo zusammen,
    meiner Ansicht nach habe ich alles wie beschrieben gemacht. Nur taucht bei mir in dem rechten "Nachbarfeld", das heißt das Feld, in dem ich =Farbe eingegeben habe, die Fehlermeldung #NV auf.

    • Hallo Lutz

      Kannst Du bitte Deine Formel im definierten Namen erneut prüfen? Hast Du tatsächlich diese Formel eingegeben:
      =ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))

      [Der Bezug ZS(-1) ist ein Referenzbezug: Dieser bedeutet, dass Du auf die selbe Zeile (Z), allerdings auf eine Spalte (S) nach links (-1) zugreifst.]

      Spontan fällt mir ansonsten noch ein: Welche Sprache/Systemsprache verwendest Du? Ist alles auf Deutsch eingestellt? (Ich bin noch nicht sicher, ob dies einen Einfluss haben könnte; aber der Referenzbezug oben mit Zeile (Z) und Spalte (S) sowie die Formel allgemein (Deutsch) könnte womöglich Probleme verursachen.)

      Beste Grüsse
      Roman

  8. Gibt es auch eine Lösung für 2-dimensionale Tabellen (z.B. A1:I250)?
    HIer möchte ich die eingefärbeten Werte / Zellen nach Spalten aufsummieren.

    Danke im Voraus
    Marcus

    • Hallo Marcus

      Über mehrere Spalten hinweg ist etwas schwieriger, da die obige Formel sich immer nur auf eine Zelle (in der Spalte links davon) bezieht. Ein Weg wäre, neben Deiner Tabelle (A1:I250) eine Hilfstabelle anzufügen - also von J1:R250. Du müsstest die obige Formel für den Namen "=Farbe" anpassen, damit jeweils die Farbe der Zelle weiter links analysiert wird. Dies wäre die korrekte Formel für Deinen Anwendungsfall (A1:A250):
      =ZELLE.ZUORDNEN(63;INDIREKT("ZS(-9)";))

      Beachte, dass ich lediglich die -9 angepasst habe, alles andere bleibt identisch zum initialen Beitrag. Dies bezweckt, dass in J1 der Farbcode von A1 angegeben werden wird. In K1 referenzieren wir auf die Farbe in B1 - usw. In der Hilfstabelle in J1:R250 schreiben wir also lediglich "=Farbe", um den Code zu erhalten. Du kannst nun eine Legende erarbeiten und mittels Formeln "SUMMEWENN/SUMMEWENNS" oder "ZÄHLENWENN/ZÄHLENWENNS" die Werte im Bereich "J1:R250" zusammenzählen respektive deren Häufigkeit zu ermitteln.

      Liebe Grüsse
      Roman

  9. Hallo, Roman,

    ganz herzlichen Dank für die Mühe, die Du Dir mit dieser ausführlichen Beschreibung gemacht hast!

    Ich hatte jedoch die Hoffnung, dass es mittlerweile (der Lösungsansatz ist ja schon 5 Jahre alt) auch einen anderen Lösungsansatz, ohne zusätzliche Zellen oder in meinem Fall ohne eine Hilfstabelle gibt.

    Nochmals: ganz herzlichen Dank für die Mühe!

    Marcus

  10. Hallo Marcus,

    ich habe die Formel wie beschrieben erfasst. Es klappt.
    Aber kann ich den Farbwert auch ermitteln, wenn dieser über eine "Bedingte Formatierung" gesetzt wird? Bei mir steht als Farbe in diesen Fällen immer 0.
    Danke im Voraus.
    Viele Grüße
    Torsten

    • Hallo Torsten

      Du kannst den Farbwert leider nicht direkt ermitteln, wenn dieser via "Bedingte Formatierung" gesetzt wurde. Dabei wird lediglich eine "Schicht" über die eigentliche Zellformatierung gelegt, die wir nicht auslesen können.

      Um was für eine Bedingte Formatierung handelt es sich denn? Hast Du da lediglich eine Farbe (z.B. Rot) gesetzt? Wenn es sich um einzelne Farben handelt, dann kannst Du eine Hilfstabelle anlegen, bei der Du die selbe Bedingung und eine Zelle mit der Zielformatierung und Farbe (z.B. Rot) hinterlegst.

      Nun kannst Du sowohl die Zelle in einer Formel ansprechen, die Du eigentlich hattest auswerten wollen - und Du ziehst die erfüllte Bedingung beispielsweise in einen SVERWEIS, um den Farbwert aus Deiner Hilfstabelle auszulesen.

      Der Aufwand, um eine solche Hilfstabelle anzulegen und zu betreuen, ist moderat, falls Du lediglich wenige spezifische Formate und Farben setzt. Wenn Deine Bedingte Formatierung jedoch eine Farbskala ist, gestaltet sich das schwieriger, da wir das womöglich nicht im gleichen Masse auf der Hilfstabelle rekonstruieren können.

      Liebe Grüsse
      Roman

  11. Lieber Roman,

    besten Dank für deinen Input, bisher hat bei mir alles bestens funktioniert. Ich möchte deine Formel in einer Zeile, aber über mehrere Spalten hinweg verwenden.
    Beispiel: Ich habe drei Farben mit den Farbcodes 6, 33 und 50 im Bereich E4:P4 und möchte jeweils die Summe vom Farbcode 6 in Zelle A4, vom Farbcode 33 in Zelle B4 und Farbcode 50 in Zelle C4 ausgegeben haben.

    Wie muss ich deine Formel anpassen, sodass ich jeweils mit einer SUMMEWENN Formel die Zellen befüllen lassen kann?

    Vielen Dank im Voraus,
    Katharina

    • Hallo Katharina

      Die Formel im Namensmanager müsstest Du anpassen, sodass nicht die Zellfarbe der Spalte links davon sondern zum Beispiel der Spalte oberhalb. Verwende dafür einfach diese Formel, wenn Du den Namen "Farbe" definierst:
      =ZELLE.ZUORDNEN(63;INDIREKT("Z(-1)S";))

      Der Unterschied gegenüber der Formel im Beitrag liegt wie erwähnt darin, dass wir die Zeile (Z) mit "(-1)" angeben, also eine Zeile oberhalb die Farbe extrahieren. Eine Nuance, aber das liefert bereits die Lösung für Deinen Anwendungsfall!

      Somit musst Du in Deinem Beispiel einmal die Zellen A4 bis C4 entsprechend einfärben, erhältst aber unterhalb (Zeile 5) mit dem Bezug "=Farbe" direkt den jeweiligen Farbcode.

      Um die SUMMEWENN-Formel korrekt auf Deinen Bereich "E4:P4" anzuwenden, setze diese Formel ein, beispielsweise für A4:
      =SUMMEWENN($E$4:$P$4;A5)

      Liebe Grüsse
      Roman

      • Lieber Roman,
        super, hat bestens funktioniert!
        Jetzt wird es noch etwas komplexer: Theoretisch ist meine Tabelle sehr viel größer, mit vielen Zeilen untereinander, die unterschiedlich gefärbt sind. Ich habe in dieser Tabelle nicht die Möglichkeit unter jeder Zeile die Farbcodes zu definieren, sondern möchte an einem bestimmten Platz in der Tabelle eine Legende einfügen, die einmal alle drei Farbcodes definiert und auf den Bereich ich jeweils Bezug nehmen möchte. Leider funktioniert das mit der SUMMEWENN nicht 🙁
        Beispiel: =SUMMEWENN(E4:P4;6;E3:P3) rechnet mir die Summe perfekt aus, jedoch nur, weil ich in Bereich E4:P4 jeweils die Farben-Formel eingefügt habe. Wenn ich in Zelle A8 die Zelle färbe und in Zelle A9 den Farbcode hinterlege, greift SUMMEWENN (=SUMMEWENN(E3:P3;6;A8:A9)) nicht, mir wird 0 angezeigt.
        Hast du Ideen dazu?

        Besten Dank im Voraus!

        LG, Katharina

        • Hallo Katharina

          Hast Du die Formel "=WENNS()" in Deiner Excel-Version verfügbar? Ein möglicher Lösungsweg wäre, wenn Du jedes mögliche Szenario in der "WENNS-Formel" hinterlegst. So wäre dies bei den drei Farbcodes "6, 33 und 50" beispielsweise:
          =WENNS(Farbe=6;...;Farbe=33;...;Farbe=50;...)

          Allerdings könnte es Schwierigkeiten geben, wenn Du keine Hilfstabelle hast, da die Formel "=Farbe" sich stets nur auf eine Zelle bezieht. Womöglich erlangst Du deshalb nicht das gewünschte Ergebnis, wie dies in der Antwort an Marcus hervorging (siehe oben/respektive diesen Kommentar: https://excelblog.ch/summe-nach-zellfarbe-bilden/#comment-3082).

          Liebe Grüsse
          Roman

  12. Moin,
    ich bekomme die SUMMEWENN(Bereich;Kriterium) nicht hin.
    Ich habe eine Tabelle mit Zahlen untereinander, die addiert werden sollen (ganz oben, dementsprechend dachte ich, die Formel hieße beispielsweise: =SUMMWENN(H5:H23;37), wobei 37 der Farbcode wäre. Ist diese Annahme falsch und wie ginge es richtig?
    Im Raum H5:H23 stehen die Summen, die aufaddiert werden sollen.

    Danke für die Hilfe und viele Grüße!
    Jakob

    • Hallo Jakob

      Beachte diese Punkte: Die SUMMEWENN addiert Zahlen, wenn eine Kondition erfüllt ist - also der Farbcode "37" entspricht. Die "37" müsste also in Deinem Bereich - die erste Angabe Deiner Formel - aufgeführt sein. Hast Du die Farbcodes jeweils rechts von den Summen, also z.B. in der Spalte I? Die korrekte Formel wäre:
      =SUMMEWENN(I5:I23;37;H5:H23)

      In der obigen Formel habe ich einen gleich grossen Bereich der Spalte I (I5:I23) definiert; dort sollte der Farbcode "37" teilweise vorhanden sein. Wenn dem so ist, wird es den entsprechenden Wert aus Spalte H bei der Summen-Aggregation berücksichtigen.

      Die Syntax von SUMMEWENN beinhaltet drei Angaben:
      - Bereich -> I5:I23 (hier sollten Farbcodes stehen)
      - Suchkriterien -> 37 (der Farbcode, der für die Summe relevant ist)
      - Summe_Bereich -> H5:H23 (hier stehen Deine Werte zur Aufsummierung. Dies ist eine optionale Angabe und kann ignoriert werden, wenn Bereich = Summe_Bereich, was in unserem Kontext nicht der Fall ist).

      Die Unterscheidung ist wichtig: Du kannst mit der SUMMEWENN-Formel nicht die Zelle an sich analysieren (welchen Farbcode hat sie denn); deshalb arbeiten wir mit einer Hilfsspalte "I", wo wir den Farbcode der Zelle links davon auslesen. In Kombination können wir dann wie oben erwähnt die Summe ermitteln.

      Hoffe, das funktioniert so bei Dir!

      Liebe Grüsse
      Roman

  13. Hallo Roman,

    danke für deine Anleitung hat mir sehr geholfen!

    Bei =ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";)) musste ich anstatt der "63" die "24" verwenden, ansonsten hatte ich immer "0" als Farbcode dargestellt. Liegt das an meiner Excelversion oder habe ich einfach etwas falsch gemacht?

    Das einzige Problem das ich habe ist, dass ich öfter bei meiner Excelliste neue Zeilen einfügen muss und die Zelle für den Farbcode dann einfach leer bleibt. Oder muss ich jedes mal die Formatierung auf die nächste Zelle übertragen?

    Liebe Grüße
    Thomas

    • Hi Thomas

      Danke für Deine Ergänzung hinsichtlich der Zuordnungsnummer (wo wir im Beitrag oben die 63 verwendet haben).

      Die 63 bezieht sich auf die Hintergrundfarbe, die 24 wird die Textfarbe auslesen. Kannst Du das so bestätigen?

      Neben diesen zwei Zuordnungsnummern gibt es eine Vielzahl an weiteren Optionen, die ich allenfalls in einem späteren Beitrag vorstellen werde.

      Hinsichtlich Deiner zweiten Fragestellung: Der Farbcode wird leider nicht jederzeit aktualisiert. Das heisst: Wenn Du eine Zeile einfügst oder auch die Farbe (Hintergrundfarbe oder Schriftfarbe, je nach Anwendungsfall) änderst, bleibt der vorherige Farbcode bestehen. Aktualisiere eine beliebige Formel oder einen Zelleninhalt innerhalb der Datei, dann werden die ausgelesenen Farbcodes korrekt angezeigt. Das ist auch der Fall, selbst wenn unter dem Register "Formeln" die Berechnungsoptionen auf "Automatisch" eingestellt sind (man kann auch dort auf den Button "Neu berechnen" klicken, um die Farbcodes zu aktualisieren).

      Beste Grüsse
      Roman

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.