Cookies helfen uns, unsere Dienstleistungen zu liefern. Durch die Nutzung unserer Dienste stimmen Sie der Verwendung von Cookies.
Tipp: Andere Sprachen sind Google-Übersetzungen. Sie können die English Version dieses Links.
Einloggen
x
or
x
x
Registrieren
x

or

Wie zähle und summiere ich Zellen anhand der Hintergrundfarbe in Excel?

Angenommen, Sie haben eine Reihe von Zellen mit unterschiedlichen Hintergrundfarben, z. B. rot, grün, blau usw. Sie müssen jetzt jedoch zählen, wie viele Zellen in diesem Bereich eine bestimmte Hintergrundfarbe haben, und die farbigen Zellen mit derselben bestimmten Farbe summieren . In Excel gibt es keine direkte Formel zum Berechnen der Summe und der Anzahl der Farbzellen. Hier möchte ich Ihnen einige Möglichkeiten vorstellen, dieses Problem zu lösen.

Zählen und summieren Sie die Zellen basierend auf der spezifischen Füllfarbe nach Filter und SUBTOTAL

Zählen und summieren Sie Zellen basierend auf einer bestimmten Füllfarbe mit der GET.CELL-Funktion

Zähle und summiere Zellen basierend auf einer bestimmten Füllfarbe mit benutzerdefinierter Funktion

Zählen und summieren Sie Zellen mit Kutools for Excel basierend auf einer bestimmten Füllfarbe (oder einer bedingten Formatierungsfarbe)

Mit einem Klick werden farbige Zellen in Excel gezählt, summiert und gemittelt

Mit dem Ausgezeichneten Zählen nach Farbe Merkmal von Kutools for Excelkönnen Sie Zellen mit nur einem Klick in Excel anhand der angegebenen Füll- oder Schriftfarbe schnell zählen, summieren und mitteln. Außerdem ermittelt diese Funktion die maximalen und minimalen Werte von Zellen anhand der Füllfarbe oder der Schriftfarbe. Kostenlose Testversion mit vollem Funktionsumfang 60-Tag!
Anzeigenanzahl nach Farbe 2


Zähle und summiere farbige Zellen nach Filter und SUBTOTAL

Angenommen, wir haben eine Obstverkaufstabelle, wie in der Abbildung unten gezeigt, und wir werden die farbigen Zellen in der Spalte Betrag zählen oder summieren. In dieser Situation können Sie die Spalte "Betrag" nach Farbe filtern und gefilterte farbige Zellen mit der SUBTOTAL-Funktion einfach in Excel zählen oder summieren.

1. Wählen Sie leere Zellen aus, um die SUBTOTAL-Funktion aufzurufen.

  1. Um alle Zellen mit der gleichen Hintergrundfarbe zu zählen, geben Sie bitte die Formel ein = SUBTOTAL (102, E2: E20);
  2. Um alle Zellen mit der gleichen Hintergrundfarbe zusammenzufassen, geben Sie bitte die Formel ein = SUBTOTAL (109, E2: E20);


Note: In beiden Formeln ist E2: E20 die Amount-Spalte, die die farbigen Zellen enthält, und Sie können sie bei Bedarf ändern.

2. Wählen Sie die Kopfzeile der Tabelle aus und klicken Sie auf Datum > Filtern. Siehe Screenshot:

3. Klicken Sie auf das Filtersymbol Klicken Sie in der Kopfzeile der Spalte Betrag auf Filter nach Farbe und die angegebene Farbe werden Sie nacheinander zählen. Siehe Screenshot:

Nach dem Filtern zählen sowohl alle SUBTOTAL-Formeln als auch alle gefilterten Farbzellen in der Spalte Betrag automatisch und summieren diese. Siehe Screenshot:

Note: Für diese Methode müssen sich die farbigen Zellen, die Sie zählen oder summieren, in derselben Spalte befinden.


Zählen oder summieren Sie farbige Zellen mit der GET.CELL-Funktion

In dieser Methode erstellen wir einen benannten Bereich mit der GET.CELL-Funktion, ermitteln den Farbcode von Zellen und zählen oder summieren sich nach dem Farbcode in Excel. Bitte machen Sie folgendes:

1. Klicken Sie Formeln > Definieren Sie den Namen. Siehe Screenshot:

2. Führen Sie im Dialogfeld Neuer Name bitte den folgenden Screenshot aus:
(1) Geben Sie im Feld Name einen Namen ein.
(2) Geben Sie die Formel ein = GET.CELL (38, Sheet4! $ E2) in der Box Bezieht sich auf (beachten: in der Formel, 38 bedeutet den Zellencode zurückgeben und Sheet4! $ E2 ist die erste Zelle in der Spalte "Amount", mit Ausnahme der Spaltenüberschrift, die Sie basierend auf Ihren Tabellendaten ändern müssen.)
(3) Klicken Sie auf OK Taste.

3. Fügen Sie jetzt eine neue Color-Spalte rechts in die Originaltabelle ein. Als nächstes geben Sie die Formel ein = NumColor , und ziehen Sie den AutoFill-Ziehpunkt, um die Formel auf andere Zellen in der Spalte Farbe anzuwenden. Siehe Screenshot:
Note: In der Formel, NumColor ist der benannte Bereich, den wir in den ersten 2-Schritten angegeben haben. Sie müssen es in den angegebenen Namen ändern.

Nun wird der Farbcode jeder Zelle in der Spalte "Betrag" in der Farbspalte angezeigt. Siehe Screenshot:

4. Kopieren Sie die Füllfarbe in einem leeren Bereich und listen Sie sie in das aktive Arbeitsblatt auf. Geben Sie Formeln daneben ein, wie in der folgenden Abbildung dargestellt:
A. Um die Zellen nach Farbe zu zählen, geben Sie bitte die Formel ein = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Um die Zellen nach Farbe zusammenzufassen, geben Sie bitte die Formel ein = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Note: In beiden Formeln $ F $ 2: $ F $ 20 ist die Farbe Spalte, NumColor ist der angegebene benannte Bereich, $ E $ 2: $ E $ 20 ist die Menge-Spalte, und Sie können sie nach Bedarf ändern.

Jetzt werden die Zellen in der Spalte "Betrag" gezählt und nach ihren Füllfarben summiert.


Zähle und summiere Zellen basierend auf einer bestimmten Füllfarbe mit benutzerdefinierter Funktion


Unter der Annahme, dass die farbigen Zellen in einem Bereich streuen (siehe Abbildung unten), können die beiden obigen Methoden die farbigen Zellen weder zählen noch summieren. Hier wird mit dieser Methode ein VBA eingeführt, um das Problem zu lösen.

1. Halten Sie die Taste gedrückt ALT + F11 Schlüssel, und es öffnet die Microsoft Visual Basic für Applikationen Fenster.

2. Klicken Sie Einsatz > Modul, und fügen Sie den folgenden Code in das Modulfenster ein.

VBA: Anzahl und Summe der Zellen basierend auf der Hintergrundfarbe:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. Speichern Sie dann den Code und wenden Sie die folgende Formel an:
A. Zähle die farbigen Zellen: = Farbfunktion (A, B: C, FALSCH)
B. Addieren Sie die farbigen Zellen: = Farbfunktion (A, B: C, WAHR)

Hinweis: In obigen Formeln A ist die Zelle mit der bestimmten Hintergrundfarbe, für die Sie die Anzahl und Summe berechnen möchten, und B: C ist der Zellbereich, in dem Sie die Anzahl und Summe berechnen möchten.

4. Nehmen Sie den folgenden Screenshot zum Beispiel, geben Sie die Formel ein= Farbfunktion (A1, A1: D11, FALSE) um die gelben Zellen zu zählen. Und benutze die Formel = Farbfunktion (A1, A1: D11, TRUE) um die gelben Zellen zu summieren. Siehe Screenshot:

5. Wenn Sie andere farbige Zellen zählen und summieren möchten, wiederholen Sie den Schritt 4. Dann erhalten Sie folgende Ergebnisse:


Mit Kutools for Excel können Sie Zellen anhand einer bestimmten Füllfarbe zählen und zusammenfassen

Mit der obigen benutzerdefinierten Funktion müssen Sie die Formel einzeln eingeben, wenn es viele verschiedene Farben gibt, ist diese Methode mühsam und zeitaufwendig. Aber wenn du es getan hast Kutools for Excel Zählen nach Farbe Dienstprogramm, können Sie schnell einen Bericht der farbigen Zellen erstellen. Sie können nicht nur die farbigen Zellen zählen und summieren, sondern auch die Durchschnitts-, Maximal- und Minimalwerte des Farbbereichs ermitteln.

1. Wählen Sie den Bereich aus, den Sie verwenden möchten, und klicken Sie auf Kutools Plus > Zählen nach Farbe, siehe Screenshot:

2. Und in der Zählen nach Farbe Bitte führen Sie den folgenden Screenshot aus:
(1) Wählen Sie Standardformatierung von dem Farbmethode Dropdown-Liste;
(2) Wählen Sie Hintergrund von dem Zählertyp Dropdown-Liste.
(3) Klicken Sie auf die Schaltfläche Bericht erstellen.

Kutools for Excel - Enthält mehr als praktische 300-Excel-Tools. Kostenlose 60-Testversion mit vollem Funktionsumfang, keine Kreditkarte erforderlich! Jetzt kaufen!

Note: Bitte wählen Sie, um farbige Zellen nach bestimmten bedingten Formatierungsfarben zu zählen und zu summieren Bedingte Formatierung von dem Farbmethode Dropdown-Liste im obigen Dialogfeld oder wählen Sie aus Standard- und bedingte Formatierung Aus der Dropdown-Liste können Sie alle Zellen zählen, die mit der angegebenen Farbe gefüllt sind.

Nun erhalten Sie eine neue Arbeitsmappe mit den Statistiken. Siehe Screenshot:


In Verbindung stehender Artikel:

Wie zähle / summiere ich Zellen anhand der Schriftfarben in Excel?


Demo: Zähle und summiere Zellen basierend auf Hintergrund, bedingte Formatierungsfarbe:

In diesem Video ist das Kutoolen Tab und die Kutools Plus Registerkarte werden von hinzugefügt Kutools for Excel. Wenn es nötig ist, klicken Sie bitte auf hier einen kostenlosen 60-Test ohne Einschränkungen durchführen zu lassen!



Empfohlene Produktivitätswerkzeuge

Office Tab

Goldstern1 Bringen Sie praktische Tabs zu Excel und anderer Office-Software, genau wie Chrome, Firefox und den neuen Internet Explorer.

Kutools for Excel

Goldstern1 Tolle! Steigern Sie Ihre Produktivität in 5 Minuten. Brauche keine besonderen Fähigkeiten, spare zwei Stunden pro Tag!

Goldstern1 300 Neue Funktionen für Excel, machen Excel viel einfacher und leistungsfähiger:

  • Zelle / Zeilen / Spalten zusammenführen, ohne Daten zu verlieren.
  • Kombinieren und konsolidieren Sie mehrere Arbeitsblätter und Arbeitsmappen.
  • Bereiche vergleichen, Mehrere Bereiche kopieren, Text in Datum umwandeln, Einheit und Währungsumrechnung.
  • Zählen nach Farben, Paging-Zwischensummen, Erweiterte Sortierung und Super-Filter,
  • Mehr Auswählen / Einfügen / Löschen / Text / Formatieren / Verknüpfen / Kommentieren / Arbeitsmappen / Arbeitsblätter Werkzeuge ...

Screenshot von Kutools für Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mori · 3 days ago
    I copied and paste but calculation result is "0" why?? I am using Office 2016.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 1 months ago
    I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
    • To post as a guest, your comment is unpublished.
      Jku · 14 days ago
      #NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
    • To post as a guest, your comment is unpublished.
      Troy Havard · 1 months ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 2 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 1 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 3 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 5 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.