[GELÖST]Summenprodukt, bedingte Formatierung

[Tschubi]

Hallo,

in "B3" steht ein variabler Referenzwert und in "B4" soll die Summe aller Werte(Text) aus "B5:B10" als Ergebnis stehen welche > dem Referenzwert sind.

Des weiteren soll die Hintergrundfarbe der Zellen B5:B10 durch bedingte Formatierung grün bzw. blau ausgegeben werden
grün - wenn > Referenzwert aus B3
rot - wenn < Referenzwert aus B3

Siehe auch Beispiel im Anhang.

Danke und Gruß

[HeritzP]

Hallo,

ein Beispiel.

Test_1.ods

(14.73 KiB) 119-mal heruntergeladen

Die Werte waren immer noch Texte.
Durch 1* werden daraus Zahlen.

Code: Alles auswählen

SUMMENPRODUKT(1*GLÄTTEN(LINKS(B5:B10;2))>$B$3)

Diese Formel wird auch für die Formatierung verwendet.


Gruß HeritzP

[Mondblatt24]

Hallo,

grün - wenn > Referenzwert aus B3
rot - wenn < Referenzwert aus B3

grün: Formel ist: --LINKS(B5;2)>$B$3
rot: Formel ist: --LINKS(B5;2)<$B$3



Gruß
Peter

[Tschubi]

Hallo,

@HeritzP
Danke dir
Sobald ich meine Formel im Sheet mit

Code: Alles auswählen

1*

anpasse, bekomme ich in der Zelle #WERT! ausgegeben

Code: Alles auswählen

=SUMMENPRODUKT(1*GLÄTTEN(LINKS(B168:B193;2))>B165)

Sobald ich die

Code: Alles auswählen

1*

weglasse, werden wieder alle Zellen gezählt unabhängig des Referenzwertes aus Zelle

Code: Alles auswählen

B165

Alle Zellen sind mit Dezimalzahl/Standard formatiert

@Peter
Danke funktioniert ebenso für die Formatierung

VG

[HeritzP]

Hallo,

#Wert deutet darauf hin, dass es keine Zahl ist.
Bei 1-stelligen Werten passiert das bei deiner Formel.


Gruß HeritzP

[Tschubi]

Hallo HeritzP,

ich habe in der Tat einen 1-stelligen Wert dabei und wenn ich diese ändere auf 2-stellig passt es. Leider kann es eben vorkommen das auch 1-stellige Werte dabei sind. Liese sich daher die Formel so anpassen das nur Zellen beim Summenprodukt gezählt werden welche zweistellig und größer

Code: Alles auswählen

B165

sind? Wenn bleibt mir ja nur den Fehler abzufangen.

Danke und Gruß

[HeritzP]

Hallo,

mit Formeln ist das einigermaßen schwierig.
Eine andere Ausgabe, z. B. alle mit Punkt ist nicht möglich?

Gruß HeritzP

[Mondblatt24]

Hallo,

Wie ist es mit

Formel in B4:
=SUMMENPRODUKT((--WECHSELN(LINKS(B5:B11;SUCHEN("%";B5:B11));".";","))>$B$3/100)
oder ein bisschen kürzer
=SUMMENPRODUKT((--WECHSELN(REGAUS(B5:B11;".+%");".";","))>$B$3/100)

Formeln in der bedingten Formatierung:
--WECHSELN(LINKS(B5;SUCHEN("%";B5));".";",")>$B$3/100 für grün
--WECHSELN(LINKS(B5;SUCHEN("%";B5));".";",")<$B$3/100 für rot
?

Gruß Peter

[Tschubi]

Hallo Peter,

einfach nur TOP!!!

Das ist im Grunde was ich mir im besten Fall vorgstellt hatte sogar mit den Kommastellen das auswerten zu können und mit deiner Lösung funktioniert es. Super vielen Dank!

VG

[Tschubi]

Hallo Peter,

ich habe deinen folgenden Vorschlag umgesetzt und das funktioniert ja auch super.

Formeln in der bedingten Formatierung:
--WECHSELN(LINKS(B5;SUCHEN("%";B5));".";",")>$B$3/100 für grün
--WECHSELN(LINKS(B5;SUCHEN("%";B5));".";",")<$B$3/100 für rot

Nur jetzt müsste ich das die Formeln in der bedingten Formatierung so erweitern und zwar das ich am Ende 4 Farben für vier Bereiche habe wie z.B.

< $B$3/100 für blau
> $B$3/100 und <= 102 für grün
> 102 und <= 105 für orange
> 105 für rot

Habe mit WENN(UND()) experimentiert jedoch ohne Erfolg.

Hast du eine Idee wie man das mit einbauen könnte?

Danke und Gruß