Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 00:42:03
Listbox mit gefilterten Daten füllen
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
24.04.2024 18:49:56
merge & select
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
952to956
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
952to956
952to956
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel für Summenbildung bei formatierten Werten

Formel für Summenbildung bei formatierten Werten
Sandra
HILFE!
Ich habe eine Excel-Tabell. In Spalte J stehen Werte, die je nach Grund eine unterschiedliche Farbe haben. Nun möchte ich gerne aus den unterschiedlich formatierten Werten eine Summe bilden ohne alle roten, alle blauen und danach alle schwarzen Werte anzuklicken, da auch bei Umformatierung eines Wertes in eine andere Farbe, die Summe automatisch aktuallisiert werden soll.
Geht das überhaupt?
Kann mir irgendjemand sagen wie?
Ich muss diese Tabelle nämlich noch heute abgeben.
DANKE!!!

48
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Formel für Summenbildung bei formatierten Wert
Rudi
Hallo,
im Archiv gibt es 100erte Beiträge zu diesem Thema.
Stichwort Farbsumme
Gruß
Rudi
Eine Kuh macht Muh, viele Kühe machen Mühe

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Wenn ich nach "Farbsumme" suche, zeigt er mir an, dass nichts gefunden wurde.
Aber trotzdem danke für die Info!

AW: Formel für Summenbildung bei formatierten Wert
Rudi
Hallo,
im Archiv
Oben die erste Box neben Online-Recherchen
111 Beiträge
Gruß
Rudi
Eine Kuh macht Muh, viele Kühe machen Mühe

AW: Formel für Summenbildung bei formatierten Wert
Sapoot
Hi...
Hast du die unterschiedlichen Formate mit einer Formel und über "Bedingtes Format" gefärbt oder manuell?
Wenn über Bedingtes Format, dann kannst du SUMMEWENN und die Formeln aus dem Bedingten Format nutzen.
cu Sapoot

Anzeige
AW: Formel für Summenbildung bei formatierten Wert
Sandra
Die wurden von Hand gefärbt, da die Farbe nicht abhängig vom Wert ist. Zur Erklärung. Es handelt sich um eine Auflistung von geprüften Positionen einer Rechnung. Die Rechnung wird aber nicht voll bezahlt, da in der Leistung Mängel vorhanden sind. Die Farben unterscheiden zum einen: Mangel, der nicht mehr behoben wird - heißt Farbe rot, da einbehalt des geforderten Betrages. Mangel, der demnächst behoben wird heißt Farbe blau. Die Summe des Einbehaltes wird nach Mängelbehebung gezahlt.
:-(
Ich hoffe es war verständlich!

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Habe vergessen einen Haken bei "Frage noch offen" zu machen.

Anzeige
AW: Formel für Summenbildung bei formatierten Wert
Sapoot
Dann würde ich die Formatierung über eine Hilfespalte versuchen.
Also die sogenannten Statien mit Noten z.B. 1-3 wobei 1=rot 2=grün und 3=blau. Vielleicht hilft das auch beim Auswerten, natürlich ist das Bedingte Format nur auf drei Formatierungen begrenzt. Somit kann man sich auf feste Werte beziehen und Bedingungen finden.
Hoffe das war soweit verständlich, erkläre meist kompliziert aber meine das Richtige ;)
cu Sapoot

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Das habe ich nicht verstanden. Wie muss ich das angehen?

AW: Formel für Summenbildung bei formatierten Wert
mpb
Hallo,
angenommen, die eingefärbten Zellen sind im Bereich A1:A20. Füge irgendwo eine Hilfsspalte ein (z.B. Spalte B), die Du ausblenden kannst. Schreibe in die Hilfsspalte jeweils eine Zahl für die entsprechende Farbe im Bereich A1:A20, z.B. A1 ist blau, dann B1 = 1; A2 = rot, dann B2 = 2; A3 = blau, dann B3 = 1 usw.
Die Summe aller blauen Zellen ergibt sich dann in diesem Beispiel mit Hilfe der Formel
=SUMMEWENN(B1:B20;"="&1;A1:A20)
Gruß
Martin

Anzeige
AW: Formel für Summenbildung bei formatierten Wert
Sandra
Also das ist mal einfach.
:-)
Das ist ja schon sehr hilfreich, aber wenn ich mehr als 1000 Werte habe, ist es doch sehr anstrengend, jede Positon mit einer Zahl für die Farbe zzu versehen.
Gibt es denn nicht soetwas wie ne Farberkennung? Das Ich zum beispiel sagen kann im Bereich von bis findest Du Zahlen. Wenn Zahl schwrze Schriftfarbe, dann 1. Wenn rote Schriftfarbe, dann 2. Und dann die wenn-Formel anwenden?
Bei dieser manuellen Lösung muss auch immer daran gedacht werden bei einer Änderung der Formatierung, die Zahl in der Hilfsspalte anzupassen, damit die Summe nachher noch stimmt.
Danke aber schonmal. Ist zumindestens hilfreich!

Anzeige
AW: Formel für Summenbildung bei formatierten Wert
mpb
Hallo Sandra,
wie viele unterschiedliche Farben brauchst Du maximal und welche sind das? Wenn Du mit Hilfsspalte arbeiten willst und höchstens 3 Farben benötigst, wäre es einfach, die entsprechende Zahl manuell einzugeben und das Einfärben über eine bedingte Formatierung zu automatisieren.
Gruß
Martin

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Derzeit sind es drei Farben. Schwarz, rot und blau. Sollte auch bei drei bleiben. Allerdings sind nicht die Zellen eingefärbt. Nur die Schriftfarbe soll dementsprechen sein.

AW: Formel für Summenbildung bei formatierten Wert
Rudi
Hallo,
vovon ist die Farbe denn abhängig? Vom Wert? Dann kannst du doch mit Summewenn() arbeiten.
Gruß
Rudi
Eine Kuh macht Muh, viele Kühe machen Mühe

Anzeige
AW: Formel für Summenbildung bei formatierten Wert
Sandra
Hallo,
nein der Wert spielt überhaupt keine Rolle. Die Schriftfarbe ist abhängig von der Art des Mangels. Z. B. noch nicht behobener Mangel = rot, nicht erbrachte Leistung = schwarz, nicht behebbarer Mangel = blau.

AW: Formel für Summenbildung bei formatierten Wert
Rudi
Hallo,
nimm meine Lösung, ändere aber die 63 in 24.
Gruß
Rudi
Eine Kuh macht Muh, viele Kühe machen Mühe

AW: Formel für Summenbildung bei formatierten Wert
mpb
Hallo Sandra,
lösche zuerst alle manuellen Farbformatierungen in Spalte J. Markiere die erste Zelle in Spalte J. Mal angenommen es wäre J1 und die Hilfsspalte wäre B. Dann FORMAT-BEDINGTE FORMATIERUNG.
Im ersten Feld "Formel ist" einstellen und als Formel
=B1=1
eingeben.
Dann Schaltfläche "Format" anklicken und die gewünschte Schriftfarbe auswählen.
Dann über "Hinzufügen" die Bedingungen für zwei weitere Farben definieren.
Die Formeln wären z.B. =B1=2 und =B1=3.
Format dieser Zelle J1 dann soweit nach unten kopieren wie nötig.
Summewenn-Formel wie bereits beschrieben.
Deine Kollegen müssen nun die 3 Fälle nicht mehr mit Farben, sondern mit den Zahlen 1, 2 und 3 in der Hilfsspalte kennzeichen. Die Schriftfarbe wird über die bedingte Formatierung automatisch gesetzt.
Gruß
Martin

Anzeige
AW: Formel für Summenbildung bei formatierten Wert
Rudi
Hallo,
1. Namen vergeben.
Name: Farbe
Bezieht sich auf: =Zelle.Zuordnen(63;indirekt("ZS(-1)";0)+0*jetzt()
In K: =Farbe
Jetzt mit Summewenn() summieren.
Gruß
Rudi
Eine Kuh macht Muh, viele Kühe machen Mühe

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Dann muss ich doch aber immer die Spalte K ändern, wenn ich die Schriftfarbe einer Zelle änder, oder?
Beispiel: Wenn Zell J10 derzeit blau ist und ich die Schriftfarbe nun auf rot änder muss ich in Spalte K Zelle 10 die Farbe von Blau auf Rot ändern, damit die Summe aktuallisiert wird?
Das heißt dann auch, wenn meine Spalte insgesamt 1000 Zellen mit Werten umfasst, dass ich jede Zelle in Spalte K die Farbe eintragen muss? Funktioniert das auch bei Schriftfarbe oder nur bei Zellenfarbe?
Was genau bedeutet die Formel: =Zelle.Zuordnen(63;indirekt("ZS(-1)";0)+0*jetzt()
Die kann ich doch bestimmt nicht 1 zu 1 übernehmen, oder?
Danke für die Geduld!!!

Anzeige
AW: Formel für Summenbildung bei formatierten Wert
Sapoot
Verate doch bitte noch wie die Zahlen zu der Farbe kommen. Irgendjemand muss die ja einfärben?
Wenn das einer mach, dann muss der die Sachen mit den Zahlen machen.

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Die Zahlen werden schon von Hand vom entsprechenden Bauleiter eingefärbt. Der wird mir aber was husten, wenn er erst ne ausgeblendete Hilfspalte einblenden und dann noch die richtige Zahl eintragen muss. Und dann auch noch die Hilfsspalte wieder ausblenden.
Hier muss am Ende immer alles automatisch, einfach und idiotensicher funktionieren und wie ist egal. Das hinzukriegen ist leider immer meine aufgabe!

Anzeige
AW: Formel für Summenbildung bei formatierten Wert
Rudi
Hallo,

Die kann ich doch bestimmt nicht 1 zu 1 übernehmen, oder?


doch.
Du sollst in K dann =Farbe schreiben. Dann wird die Farbnummer aus J wiedergegeben. Das aktualisiert sich bei jeder Neuberechnung von selbst.
Gruß
Rudi
Eine Kuh macht Muh, viele Kühe machen Mühe

AW: Formel für Summenbildung bei formatierten Wert
Majong
Hi,
oder VBA, wobei Farbwechsel keine Neukalkulation auslöst; was aber mit Taste 'F9' erledigt wird:

Option Explicit
Sub ttt()
MsgBox Range("A3").Interior.ColorIndex
End Sub
Public Function MySumme(Color As Integer, RowIndex As Long, ColumnIndex As Integer) As Double
Dim rng As Range
'Application.Volatile
For Each rng In Range(Cells(RowIndex, ColumnIndex), Cells(65536, ColumnIndex).End(xlUp))
If rng.Interior.ColorIndex = Color Then MySumme = MySumme + rng
Next
End Function


Wobei Sub ttt zur Anzeige der Farbnummer einer Zelle dient. In der Tabelle dann z.B.:


=MySumme(3;1;1)


eingeben für Farbnummer=3, von Startzeile 1 in Spalte 1 bis zum Ende der Spalte (daher am Ende der Spalte nicht diese Formel eingeben).
Gruss

Anzeige
AW: Formel für Summenbildung bei formatierten Wert
Majong
... das Bemerkungszeichen vor Application.Volatile entfernen!
Gruss

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Sorry, aber mit VBA hatte ich noch nie etwas zu tun!
Würden wir das zusammen schritt für schritt hinbekommen?

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Geht das auch so, dass ich das als VBA-Nichtkenner verstehe?
Ich habe noch nie nen Makro angelegt.

AW: Formel für Summenbildung bei formatierten Wert
Majong
hmm ...
- TastenCombi 'ALT (links) / F11' um in den VBA-Editor zu kommen
- links im Projekt-Fenster rechte Maustaste / Einfügen / Modul
- Doppelklick auf Modul
- Code von Option Explicit bis End Function ins rechte Editor-Fenster reinkopieren
- Rückkehr zur Tabelle
- Formel in eine Zelle schreiben (nicht unterhalb der Auswertespalte)
- Test-Zelle einfärben
- Rückkehr in VBA-Editor und Cursor innerhalb der Sub ttt platzieren und kleines Dreieck oben (Sub ausführen drücken)
- Rückkehr zur Tabelle
- angezeigten Farbwert in die Formel der Tabelle eintragen
- weitere Zellen der Spalte einfärben und Taste 'F9' drücken, u.s.w.
Gruss

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Bis zum Dopelklick auf Modul bin ich gekommen aber von wo soll ich jetzt den Code kopieren?

AW: Formel für Summenbildung bei formatierten Wert
Majong
... den Code, den ich Dir zuvor anbot (siehe weiter oben ...)?!?

AW: Formel für Summenbildung bei formatierten Wert
Majong
... in das GROSSE Fenster rein damit!

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Hab ich getan. Ich bin bis zu der Formel mysumme... gekommen. Sollte dann den Cursor bei sub ttt reinstellen und auf das kleine dreieck klicken. Habe es jetzt gefunden.
Es ging ein Feld auf in dem Stand -4142

AW: Formel für Summenbildung bei formatierten Wert
Majong 

... na, Du musst bei Range natürlich eine eingefärbte Zelle schreiben
... z.B. färbe Zelle A1 rot, schreibe in Range "A1" rein und führe die Sub aus ==> Anzeige 3
... also hat rot den Farbwert 3
Okey ?


AW: Formel für Summenbildung bei formatierten Wert
Renee
Hi Sandra,
Diese Funktion funktioniert ein bisschen anders.
Kopier den Code in ein Modul, wenn du das Modul offen hast, wechsle in den Browser, selektier mit der Maus den untenstehenden blauen Text, Ctrl-C, zurück zum VBE-Modul, Ctrl-V.

Public Function SummeSchriftFarbe(Summenbereich As Range, ZelleMitderFarbe As Range) As Double
Application.Volatile
Dim rC As Range
For Each rC In Summenbereich
If rC.Font.ColorIndex = ZelleMitderFarbe.Cells(1, 1).Font.ColorIndex Then _
SummeSchriftFarbe = SummeSchriftFarbe + rC.Value
Next
End Function


Die Formel in einer Zelle lautet dann (z.b. Dein Bereich geht von A1:A200, du willst grüne Schriftfarbe, eine Zelle mit grüner Schrift befindet sich in A17):


=SummeSchriftFarbe(A1:A200;A17)


GreetZ Renée

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Hallo und guten Morgen.
Die Lösung gefällt mir schon am besten, da wir hier wie gewohnt mit den Schriftfarben arbeiten können, aber irgendetwas haut noch nicht ganz hin.
Ich habe jetzt das Modul von gestern gelöscht (das mit der farbigen Zellenmarkierung). Habe dann in Modul1 die oben stehende Formel reinkopiert.
Meine Werte stehen in J7:J31. Bei J7 beginnt blau und bei J8 rot. dies sind im Moment die einzigen Farben. Soll aber ggfs. noch eine dazukommen.
In Zelle H37 habe ich folgende Formel geschrieben: =SummeSchriftFarbe(J7:J31;J7 Damit mir dort die Summe der Werte angiebt, die in blauer Schriftfarbe sind.
In Zelle H38 habe ich folgende Formel geschrieben: =SummeSchriftFarbe(J7:J31;J8 Damit mir dort die Summe der Werte angiebt, die in roter Schriftfarbe geschrieben sind.
Nach Bestätigung der Formel kommt #Name. Wenn ich nocheinmal in die Formel gehe und sie bestätige wirft er mir als Ergebnis 0 aus.
Habe ich bei dem Modul etwas falsch gemacht?
Ich bin ins Projekt-Fenster gegangen, rechte Maustaste, einfügen, Modul. Dann habe ich die oben stehende Formel in das rechte, neu aufgegangene Fenster kopiert. Ich wollte das Modul ausführen, aber das ging nicht. Also habe ich es so gelassen und dann die Formeln in die Tabelle eingetragen.
Wo ist mein Fehler?
Vielen Dank schon mal!!!
Sandra

AW: Formel für Summenbildung bei formatierten Wert
Renee
Hallo Sandra,
Ins Modul im VB-Editor kommt nur der blaue Teil mit dem VBA Code (NICHT die Formel!)
Den VBA Code darfst du nicht verändern. Er muss so lauten wie oben angegeben.
Diesen Code kannst du nicht ausführen weil es eine Funktion ist. Der Name der Funktion kann als Formel in Zellen der Tabelle gebraucht werden.
Die Formel kommt ganz normal (wie z.B. eine Excel Summenformel) in die Zelle in der du das Resultat willst.
Wenn du nicht weiterkommst, lade doch mal deine Mappe auf den Sever. Mit diesem Link: https://www.herber.de/forum/file_upload.html
GreetZ Renée

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Kann ich nicht. Da kommt die Meldung: Ungeültiger Dateityp.
Es ist ein xls-Dokument.

AW: Formel für Summenbildung bei formatierten Wert
Renee
Hi Sandra,
der Upload ist ein bisschen shaky mit Namen, Sonderzeichen, Leerzeichen im Datei- und/oder den Verzeichnisnamen.
Am besten eine Kopie der Datei mit einem simplen Namen (keine Umlaute, Blanks etc) erstellen, z.B. Sandra.xls. Dies Kopie auf einem 'simplen' Verzeichnis speichern, so dass es am Schluss z.B. so aussieht: C:\Sandra.xls. Jetzt die Datei von dort hochladen.
GreetZ Renée

AW: Formel für Summenbildung bei formatierten Wert
Renee
Hi Sandra,
Ich denke doch mal das Ihr die Datei löscht, sobald wir alles geklärt haben, oder?
Nein. Wenn du das willst (aber unüblich und eigentlich nicht erwünscht ist) musst du dich direkt an Hans W. Herber wenden. Er ist der Betreiber dieses Forums.
Ich sehe in deiner Datei das Problem nicht. Die Summen die durch die Funktion erstellt werden stimmen! Falls du dich an der Formatierung störst, die musst du an den Summenzellen selber so vornehmen wie in den Betragszellen, oder von dort die Formate kopieren.
GreetZ Renée

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Wie die Summen stimmen? Die Summen sind bei mir 0

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Jetzt geht es!
Warum auch immer!!!
Musste die Datei wohl erst einmal geschlossen werden.
Vielen Dank an alle!!!!!!!!!!!!!!!!!!!
Einfach ein super Forum!!!

AW: Formel für Summenbildung bei formatierten Wert
Renee
Hi Sandra,
Na endlich... ;-)
Noch zwei Tipps:
1. Die Summen dieser Formel verändern sich nur, wenn eine Änderung im Inhalt von Zellen gemacht wird. Sie ändern sich nicht, wenn z.B. nur die Farbe verändert wird.
2. Den Zellbezug zur Schriftfarbe würde ich in der Formel auf die Zelle mit der Legende (Erklärung) beziehen und nicht auf einen Betrag in der Summenspalte. Wenn du dann die Schriftfarbe einer Betragszelle änderst und F9 drückst, ändert sich die Summen beider Totalzellen richtig.
Greetz Renée

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Punkt 1 ist schon klar. Habe ich schon bemerkt und hatte ja gestern schon die Info bekommen, das ich dazu (bei Farbänderungen) F9 drücken müsste. Aber was Du mit Punkt 2 meinst weiß ich mal wieder nicht. Bzw. verstehe es mal wieder nicht!

AW: Formel für Summenbildung bei formatierten Wert
mpb
Hallo Sandra,
Du hast den 2. Parameter der Formel, mit dem die Frabe bestimmt wird, auf J7 bzw. J8 gesetzt. Wenn nun die Frabe in einer dieser Zellen geändert wird, stimmt die Formel nicht mehr. Wenn Du z.B. in J7 die Schrift auf rot änderst, liefren beide Formeln die Summe für rot. Das kannst Du verhindern, wenn Du die Formeln auf A37 bzw. A38 beziehst, da Du die Schriftfarben in diesen Zellen (=Legende) ja nicht ändern wirst. Korrekt wäre also
=SummeSchriftFarbe(J7:J31;A37) für blau und
=SummeSchriftFarbe(J7:J31;A38) für rot.
Gruß
Martin

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Achso!!! Sag das doch gleich! :-)
Werde ich machen! Vielen Dank!!!!
Schlecht ist dann nur, wenn noch so viele Positionen dazu kommen, dass die Summen nicht mehr in 37 bzw. 38 stehen.
Wäre es da sinnvoll die Legende über die Tabelle zu setzen?
Gruß

AW: Formel für Summenbildung bei formatierten Wert
mpb
Hallo Sandra,
wenn Du vor der Zeile 37 Leerzeilen einfügst ist das ja kein Problem, die Formel passt sich automatisch an. Die Formeln funktionieren nur dann nicht mehr, wenn Du A37/A38 löschst und die Legende an eine andere Stelle setzt. Dann müsste der Bezug angepasst werden.
Wenn es von der Gestaltung der Tabelle her möglich ist, würde ich die Legende dort platzieren, wo sie "für immer" unverändert stehen bleiben kann, und den Bezug in den Formeln auf diese Zellen setzen.
Gruß
Martin

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Gut. Ich setze sie einfach nach oben.
Vielen Dank nochmal!!!

AW: Formel für Summenbildung bei formatierten Wert
Sandra
Die Formel (=MySumme(3;7;10)) habe ich jetzt einfach in K3 geschrieben. 7 weil ab Zeile 7 und 10 weil ab Spalte 10.
Er zeigt mir ne null an. Jetzt habe ich J8 eingefärbt. Komme jetzt aber mit dem Punkt nicht weiter:
Rückkehr in VBA-Editor und Cursor innerhalb der Sub ttt platzieren und kleines Dreieck oben (Sub ausführen drücken)
Ich finde weder das dreieck, noch sub ausführen.

AW: Formel für Summenbildung bei formatierten Wert
Majong
... Dreieck oben in der zweiten Menüleiste.
... 3 ist der Farbwert, den du Dir über Sub tt ermitteln kannst (rot=3)
... in Deinem Beispiel ist 7 die erste Zeile und 10 die Spaltennummer also Spalte J
... die Funktion läuft bis zum Spaltenende automatisch durch
Gruss

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige