Frage von Frage1990, 30

Wie lasse ich in Office 365 Excel bestimmte Spalten beim Summieren weg?

Hallo allerseits, ich benötige bitte dringend Hilfe bei Excel. Ich möchte eine bestimmte Spalte addiert haben. Das bekomme ich auch hin. Jedoch möchte ich, dass zum Beispiel die Grün markierten Zeilen nicht mitberechnet werden. Wie stelle ich das ein?

Danke schonmal für Eure Mühe

Expertenantwort
von Iamiam, Community-Experte für Excel, 22

Mach in die Ergebniszelle erst dieselbe Farbe.

Du kannst dann mit Spalte auswählen, Menü Daten, Filtern, nach Farbe filtern Deine Farbe wählen: alle anderen Farben sind ausgeblendet.

Jetzt schreibst Du in die Ergebniszelle: =Teilergebnis(9;F1:Fn)

n ist die Zeilennummer des untersten zu summierenden Werts, also wahrscheinlich eins über der Ergebniszelle.

Teilergebnis(9=Summe;...) behandelt nur die sichtbatren Zellen.

Schau auch mal in der Hilfe nach, Teilergebnis ist in den letzten Versionen stark ausgebaut worden!

Nachteil: hebst Du die Filterung auf, werden die wieder sichtbaren Zellen wieder miteinbezogen. Du müsstest also das Ergebnis zu Wert umsetzen, dann ist die Funktion aber nicht mehr dynamisch, sie ist ja durch einen fixen Wert ersetzt. Ich kenne keine Möglichkeit, das dynamisch zu machen (ausser den von Ninombre und Subopt genannten -wobei letztere bei Dir ja nicht geht-),

es sei denn, Du markierst die Zellen einzeln, also zB

=Summe(F2;F6:F11;F15; F22;F27:F33;F38)

Kannst Zellen und Bereiche mit der Maus zeigen, aber den Semicolon musst Du mit der Hand eingeben (oder ich weiß wieder mal was nicht). Ist aber auch nicht komplizierter als das Xen, allerdings nicht so offensichtlich.

Kommentar von Frage1990 ,

Danke für Deine Tipps. Also ich werde die Zellen nun jedes Mal manuell markieren. Damit Ihr den Hintergrund versteht: Ich habe eine Potenzialliste (für Kundenaufträge). Nun markiere ich alles was in der Schwebe ist gelb. Abgeschlossene grün und Projekte, die nicht zustande kommen rot. Unten müssen lediglich 2 Summen sein. Das gelb markierte Potenzial und die abgeschlossenen Projekte in grün. 

Also für eine Lösung wäre ich wirklich sehr dankbar, aber vermutlich werde ich es tatsächlich manuell machen müssen

Kommentar von Frage1990 ,

Hab jetzt eine gute Lösung gefunden: Ich habe ja noch eine Spalte mit der Prozentzahl an Potenzial. Nun habe ich folgende Formel verwendet für Abgeschlossene Projekte: =SUMMEWENN(H:H;"100%";F:F)

Klappt wunderbar. Nun muss ich nur noch wissen, wie ich das mache mit Prozentzahlen in der Spalte H von 10-95% . Weiß das jemand?

Kommentar von Iamiam ,

Du willst also alle Zellen F:F summieren, neben denen in H:H 100% steht (so verstehe ich das):

=Summewenn(H:H;1;F:F) 

bzw. =Summewenn(H1:H55;100%;F1:F55) , wenn die Summe in Spalte F, zB F56, stehen soll (damit kein Zirkelbezug entsteht).

100% = 1, kannst aber auch 100% schreiben, ist später vllt besser nachvollziehbar (man vergisst ja soo schnell!)

Ich mach solche Formeln übrigens oft oben in eine der ersten Zeilen (nach den Überschriften) und fixiere dann den Titel plus einiger Zeilen (Fenster einfrieren, die Zeile unterhalb der Grenze ganz markieren, am Zeilenkopf, oder nur eine Zelle da drin markieren, dann werden Zeilen drüber und Spalten links davon fixiert,im übrigen Blatt kannst du dann frei manövrieren. Und wenn Du das neben der Spalte machst, dann kannst Du auch wieder F:F schreiben ohne Gefahr eines Zirkelbezugs. In zB F3 schreibe ich dann "Aufträge ║ Sum.erl.>", ggf mit Zeilenumbruch anstatt ║, erzwungen mit Alt+Enter)

Kommentar von Iamiam ,

Jetzt hab ich mal wieder schlampig gelesen und deshalb Eulen nach Athen getragen. Gefragte Formel:

=Summewenn(H5:H55;"<100%";F5:F55)

willst Du das genauer eingrenzen (zB wie gefragt: 5..<95%) dann entweder mitt SummewennS (s. Hilfe) oder mit einer Doppelformel:

=Summewenn(H5:H55;"<95%";F5:F55)-Summewenn(H5:H55;"<=5%";F5:F55)

über <, <=, =, >= musst Du selbst entscheiden. Oder variabel (Obergrenze in F2, Untergrenze in F3):

=Summewenn(H5:H55;"<"&F2;F5:F55)-Summewenn(H5:H55;"<="&F3;F5:F55)

Expertenantwort
von Suboptimierer, Community-Experte für Excel, 30

Farben lassen sich schlecht abfragen. Bei Werten ist es einfacher. Wie hast du die Zellen denn grün gefärbt? Wenn du dies mit bedingter Formatierung getan hast, kannst du dieselbe Formel in der Funktion SUMMEWENN als Bedingung verwenden.

Kommentar von Frage1990 ,

Ich habe es ganz normal farblich ausgefüllt. Mit dem ''Eimer''. Wie kann ich die Farbe mittels Formatierung ändern?

Kommentar von Ninombre ,

Ribbon Start - ziemlich in der Mitte gibt es Bedingte Formatierung. Dort kannst Du Regeln hinterlegen, bzw. dass alle negativen Zahlen rot geschrieben werden. Genau nach dieser Regel hätte man halt auch summieren können.
Wenn Du die Zellen aber willkürlich grün gefärbt hast, kann bringt der Lösungsansatz nichts. Dann müsstest Du in eine Spalte nebendran zu jeder grünen Zelle ein X machen. Dann funktioniert die Idee mit summewenn. Steht das X in Spalte C und die Zahl in B und sind 10 Zeilen relevant, wäre es z.B.
=SUMMEWENN(C1:C10;"";B1:B10)

Kommentar von Suboptimierer ,

Das mit dem x lässt sich wiederum automatisieren. Dafür muss man einen Namen definieren: Formeln → Definierte Namen → Namen definieren.

Name: Hintergrundfarbe
Bezieht sich auf: 

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

Das ist die linke Zelle von der Formel.

In Spalte C dann eintragen: =Hintergrundfarbe 

und herunterziehen.

Dann über den Farbcode für grün summieren. Ein Grünton hat zum Beispiel den Code 10

=SUMMEWENN(C1:C10;10;B1:B10)

Die Spalte C kann dann ausgeblendet werden.

Kommentar von Frage1990 ,

Also erstmal Danke für Eure Unterstützung. Aber ich komme nicht so recht weiter... Ich muss dazusagen dass ich die Tablet-Version von Office 365 Excel habe. Wenn ich auf ''Formeln'' klicke, finde ich nichts mit ''definierte Namen''....

Aber wenn ich die Zelle anklicke, in dem die addierten Zahlen erscheinen sollen, kann ich die Formel frei eingeben oder aus vielen verschiedenen aussuchen. 

Wie löse ich das nun exakt?

Die zu addierenden Zahlen sind in der Spalte F. Und unten in der selben Spalte ist die Zelle, in der die Summe später drinstehen muss

Kommentar von Suboptimierer ,

https://www.youtube.com/watch?v=UtiqKSjNjI0

Wenn es den nicht in Office 360 gibt, würde mich das wundern.

Kommentar von Frage1990 ,

Hab mal gegoogelt. Kein Namensmanager.... Zumindest nicht in meiner kostenlosen Version. Ob es nach Kauf einer Lizenz möglich ist weiß ich nicht. Also, wie löse ich das Problem?

Kommentar von Suboptimierer ,

Dann so, wie Ninombre es beschrieben hat. In Spalte C trägst du überall ein x ein, wo in der Nachbarzelle in Spalte B (nicht) summiert werden soll.

Auf die Spalte B legst du eine Bedingte Formatierung: =C2="x" mit Hintergrundfarbe grün.

Die Summenformel lautet dann:

=SUMMEWENN(C1:C10;"x";B1:B10)

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten