Die Excel-Funktion SUMMEWENNS für bedingtes Addieren von Werten Eine der Funktionen, die mit der Version Excel 2007 neu in den Funktionskatalog aufgenommen wurde, ist die Funktion SUMMEWENNS. Im Gegensatz zu der Funktion SUMMEWENN können Sie mit dieser Funktion einen Zellbereich von Werten addieren unter Berücksichtigung von bis zu 127 Bedingungen. Die Funktion ist für 2 Bedingungen folgendermaßen aufgebaut: =SUMMEWENNS(<Zellbereich, dessen Werte addiert werden sollen>;<erster zu prüfender Bereich;<erste Bedingung>;<zweiter zu prüfender Bereich>;<zweite Bedingung>) Die Anwendung dieser Funktion soll an dem folgenden Beispiel gezeigt werden: In der unteren Tabelle sind die Rechnungs- und Zahlungsbeträge von Kunden erfasst worden. Die Funktion SUMMEWENNS soll verwendet werden, um flexibel die Anzahl der überfälligen Beträge eines oder aller Kunden zu einem bestimmten Stichtag zu ermitteln: Die Zellen mit dem gelben Hintergrund markieren die offenen Posten, die bis zum 22.01.2011 fällig waren und noch nicht beglichen wurden. Die Formel für die Berechnung der Summe der überfälligen Beträge des Kunden Meyer zum Stichtag 22.01.2011 in Höhe von 310,00 € lautet folgendermaßen: =SUMMEWENNS(F2:F10;A2:A10;“Meyer“;C2:C10;“<=22.01.2011″) Diese Formel ist wie folgt zu lesen: Addiere die Zahlen in dem Zellbereich F2 bis F10 nur, wenn in der gleichen Zeile im Zellbereich A2 bis A10 der Kunde Meyer erscheintund im Zellbereich C2 bis C10 ein Fälligkeitsdatum kleiner oder gleich dem 22.01.2011 enthalten ist. Um die Formel flexibel verwenden zu können, sollten Sie den Namen des Kunden oder den Stichtag nicht als Konstanten in die Formel eintragen, sondern 2 Zellbezüge verwenden, in denen die Kriterien jeweils eingetragen werden: Wenn Sie in der Zelle A14 den Namen des Kunden und in Zelle B14 das Kriterium für den Stichtag eingeben, lautet die Formel für die Ermittlung der überfälligen Beträge in Zelle C14 wie folgt: =SUMMEWENNS(F2:F10;A2:A10;A14;C2:C10;B14) Sie können in Zelle B14 auch als Stichtag das aktuelle Tagesdatum verwenden. Geben Sie dazu in Zelle B14 die folgende Formel ein: =“<=“ &TEXT(HEUTE();“TT.MM.JJJJ“) Wenn Sie die überfälligen Beträge eines Kunden für Rechnungen ermitteln möchten, die in einen bestimmten Zeitraum fallen, können Sie die folgende Formel verwenden (siehe Zelle D17) =SUMMEWENNS(F2:F10;A2:A10;A17;C2:C10;B17;C2:C10;C17) Hinweise:
Sie können hier die Beispieldatei herunterladen, um so einen besseren Zugang zu der Funktion SUMMEWENNS zu bekommen. Autor Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.
Angenommen, Sie haben eine Reihe von Zellen mit unterschiedlichen Hintergrundfarben wie Rot, Grün, Blau usw. Jetzt müssen Sie 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 zur Berechnung der Summe und Anzahl der Farbzellen. Hier werde ich Ihnen einige Möglichkeiten zur Lösung dieses Problems vorstellen.
Zählen und summieren Sie farbige Zellen nach Filter und SUBTOTALAngenommen, wir haben eine
Obstverkaufstabelle wie im folgenden Screenshot gezeigt, und wir werden die farbigen Zellen in der Spalte Betrag zählen oder summieren. In dieser Situation können wir die Spalte Betrag nach Farbe filtern und dann gefilterte farbige Zellen mit der Funktion SUBTOTAL in Excel einfach zählen oder summieren. 1. Wählen Sie leere Zellen aus, um die Funktion SUBTOTAL aufzurufen.
Hinweis: In beiden Formeln ist E2: E20 die Spalte Betrag, die die farbigen Zellen enthält, und Sie können sie nach Bedarf ändern. 2. Wählen Sie die Kopfzeile der Tabelle aus und klicken Sie auf Daten und Zahlen > Filter. Siehe Screenshot: 3. Klicken Sie auf das Filtersymbol Nach dem Filtern zählen und summieren beide SUBTOTAL-Formeln automatisch alle gefilterten Farbzellen in der Spalte Betrag. Siehe Screenshot: Hinweis: Für diese Methode müssen sich die farbigen Zellen, die Sie zählen oder summieren, in derselben Spalte befinden. Ein Klick, um farbige Zellen in Excel zu zählen, zu summieren und zu mittelnMit dem Ausgezeichneten
Nach Farbe zählen Merkmal von Kutools for ExcelMit nur einem Klick in Excel können Sie Zellen schnell zählen, summieren und mitteln, indem Sie die gewünschte Füllfarbe oder Schriftfarbe angeben. Außerdem ermittelt diese Funktion auch die Max- und Min-Werte
von Zellen anhand der Füllfarbe oder der Schriftfarbe. Kostenlose 30-Tage-Testversion mit vollem Funktionsumfang! Kutools for Excel- Enthält mehr als 300 praktische Tools für Excel. Kostenlose 30-Tage-Testversion mit vollem Funktionsumfang, keine Kreditkarte erforderlich! Hol es dir jetzt Farbige Zellen mit der Funktion GET.CELL zählen oder summierenBei dieser Methode erstellen wir mit der Funktion GET.CELL einen benannten Bereich, ermitteln den Farbcode der Zellen und zählen oder summieren ihn dann einfach in Excel nach dem Farbcode. Bitte gehen Sie wie folgt vor: 1. Klicken Sie Formeln > Name definieren. Siehe
Screenshot: 2. Gehen Sie im Dialogfeld "Neuer Name" wie folgt vor: 3. Fügen Sie nun der ursprünglichen Tabelle eine neue Farbspalte hinzu. Geben Sie als
nächstes die Formel ein = NumColor und ziehen Sie das AutoFill-Handle, um die Formel auf andere Zellen in der Spalte Farbe anzuwenden. Siehe Screenshot: Jetzt wird der Farbcode jeder Zelle in der Spalte Betrag in der Spalte Farbe zurückgegeben. Siehe Screenshot: 4. Kopieren und listen Sie die Füllfarbe in
einem leeren Bereich im aktiven Arbeitsblatt auf und geben Sie die Formeln daneben ein, wie im folgenden Screenshot gezeigt: Hinweis: In beiden Formeln, $ F $ 2: $ F $ 20 ist die Spalte Farbe, AnzahlFarbe ist der angegebene benannte Bereich, $ E $ 2: $ E $ 20 ist die Betragsspalte, und Sie können sie nach Bedarf ändern. Jetzt sehen Sie, dass die Zellen in der Spalte Betrag gezählt und durch ihre Füllfarben
summiert werden. Zählen und summieren Sie Zellen basierend auf einer bestimmten Füllfarbe mit der benutzerdefinierten FunktionAngenommen, die farbigen Zellen streuen in einem Bereich wie im folgenden Screenshot gezeigt, können beide oben genannten Methoden die farbigen
Zellen nicht zählen oder 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 Tasten, und es öffnet die Microsoft Visual Basic für Applikationen Fenster. 2. Klicken Sie Insert > Modulund fügen Sie den folgenden Code in das Modulfenster ein. VBA: Anzahl und Summe der Zellen basierend auf der Hintergrundfarbe:
3. Speichern Sie dann den Code und wenden Sie die folgende Formel an: Hinweis: In den obigen Formeln ist 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 zum Beispiel den folgenden Screenshot und geben Sie die Formel ein= Farbfunktion (A1, A1: D11, FALSE) die gelben Zellen zu zählen. Und benutze die Formel = Farbfunktion (A1, A1: D11, TRUE)
die gelben Zellen zu summieren. Siehe Screenshot: 5. Wenn Sie andere farbige Zellen zählen und summieren möchten, wiederholen Sie bitte Schritt 4. Dann erhalten Sie die folgenden Ergebnisse: Zählen und Summen von Zellen basierend auf einer bestimmten Füllfarbe mit Kutools-FunktionenKutools für Excel unterstützt auch einige nützliche Funktionen, mit denen Excel-Benutzer spezielle Berechnungen durchführen können, z. B. Anzahl nach Hintergrundfarbe der Zelle, Summe nach Schriftfarbe usw. Kutools for Excel- Enthält mehr als 300 praktische Tools für Excel. Kostenlose 30-Tage-Testversion mit vollem Funktionsumfang, keine Kreditkarte erforderlich! Kostenlose Testversion jetzt! 1. Wählen Sie die leere
Zelle aus, in der Sie die Zählergebnisse platzieren, und klicken Sie auf Kutoolen > Kutools-Funktionen > Statistical & Math > ANZAHLFARBE. Siehe Screenshot: 2. Geben Sie im Dialogfeld Funktionsargumente den Bereich an, in dem
farbige Zellen gezählt werden sollen Referenz Wählen Sie im Feld die Zelle aus, die mit der angegebenen Hintergrundfarbe im Feld gefüllt ist Farbindex_nr Klicken Sie auf das Feld OK Taste. Siehe Screenshot: Hinweise: Kutools-Funktionen enthalten eine Reihe von integrierten Funktionen, mit denen Excel-Benutzer einfach rechnen können, einschließlich Anzahl / Summe / Durchschnitt Sichtbare Zellen, Anzahl / Summe nach Zellenfarbe, Anzahl / Summe nach Schriftfarbe, Zeichen zählen, Nach Fettdruck zählen, usw. Haben Sie eine kostenlose Testversion! Zählen und summieren Sie Zellen basierend auf einer bestimmten Füllfarbe mit Kutools for ExcelMit der oben genannten benutzerdefinierten Funktion müssen Sie die Formel einzeln eingeben. Wenn viele verschiedene Farben vorhanden sind, ist diese Methode mühsam und zeitaufwändig. Aber wenn du hast Kutools for Excel Nach Farbe zählen Dienstprogramm können Sie schnell einen Bericht über die farbigen Zellen erstellen. Sie können nicht nur die farbigen Zellen zählen und summieren, sondern auch die Durchschnitts-, Max- und Min-Werte des Farbbereichs abrufen. Kutools for Excel- Enthält mehr als 300 praktische Tools für Excel. Kostenlose 30-Tage-Testversion mit vollem Funktionsumfang, keine Kreditkarte erforderlich! Kostenlose Testversion jetzt! 1. Wählen Sie den Bereich aus, den Sie verwenden möchten, und klicken Sie auf Kutoolen Plus > Nach Farbe zählen, siehe Screenshot: 2. Und in der Nach Farbe zählen Dialogfeld, bitte machen Sie wie folgt Screenshot gezeigt: Hinweis: Um farbige Zellen nach bestimmten Farben für bedingte Formatierungen zu zählen und zu summieren, wählen Sie bitte Bedingte Formatierung von dem Farbmethode Dropdown-Liste im obigen Dialogfeld oder wählen Sie Standard- und bedingte Formatierung aus der Dropdown-Liste, um alle Zellen zu zählen, die mit der angegebenen Farbe gefüllt sind. Jetzt erhalten Sie eine neue Arbeitsmappe mit den Statistiken. Siehe Screenshot: Die Nach Farbe zählen Die Funktion berechnet (Anzahl, Summe, Durchschnitt, Maximum usw.) Zellen nach Hintergrundfarbe oder Schriftfarbe. Haben Sie eine kostenlose Testversion! In Verbindung stehender Artikel:Demo: Anzahl und Summe der Zellen basierend auf Hintergrund, bedingte Formatierungsfarbe:Die besten Tools für die Office-ProduktivitätKutools für Excel löst die meisten Ihrer Probleme und erhöht Ihre Produktivität um 80%
Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich
Kommentare (235) Noch keine Bewertungen. Bewerten Sie als Erster! Hinterlassen Sie Ihre Kommentare |