Summenprodukt mit Text

Forumthread: Summenprodukt mit Text

14.08.2022 21:34:55

Steffi

Hello Leute,
ich habe ein Problem, dass mich schon seit einiger Zeit beschäftigt! Ich möchte Arbeitszeiten in Kalenderwochen berechnen, dies funktioniert recht gut, aber am Samstag und Sonntag steht eben FREI dabei und hierbei wirft mir dann.. die Formel den Fehler #WERT aus.
Jetzt wäre meine Frage, ob man irgendwie dein Text bei der Formel ignorieren kann. Ich bin dankbar für jede Hilfe.. Leider kenn ich mich so gut wie gar nicht aus :-(
Meine Formel lautet für die SOLL Zeiten:
=WENN(C5="";"";WENN(WOCHENTAG(C5;1)=6;'Verweis Kal'!$C$9;WENN(ODER(WOCHENTAG(C5;2)=6;(WOCHENTAG(C5;2) =7));"FREI";'Verweis Kal'!$C$6)))
diese Funktioniert sehr gut... doch in dieser Zeile sollen auch die Kalenderzeiten berechnet werden... hier die Formel
=WENN(AI5="";"";SUMMENPRODUKT((AI5=KALENDERWOCHE(--$C$5#;21))*BEREICH.VERSCHIEBEN($C$6;0;0;1; (ANZAHL(--$C$5#)))))
Es wär echt super, wenn es eine Möglichkeit gibt, das man den TEXT bei Sa. und So. ignorieren könnte ohne das ich ihn weglassen muss.
Danke und liebe Grüße
Steffi
https://www.herber.de/bbs/user/154646.xlsx

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Summenprodukt mit Text

14.08.2022 21:59:35

SF
AW: Summenprodukt mit Text

14.08.2022 22:04:24

Daniel
AW: Summenprodukt mit Text

14.08.2022 22:09:52

onur
=AGGREGAT(9;6;A1:M1)

14.08.2022 22:16:02

WF
AW: Summenprodukt mit Text

14.08.2022 22:48:34

Steffi
AW: Summenprodukt mit Text

14.08.2022 22:51:09

onur
AW: Summenprodukt mit Text

14.08.2022 22:49:42

Daniel

AW: Summenprodukt mit Text

14.08.2022 21:59:35

Hola,
=SUMMENPRODUKT((KALENDERWOCHE(+$C$4#;21)=AI5)*1;$C$6:$AF$6)

Alternativ ermittelst du die Kalenderwoche in einer Hilfszeile und nimmst dann Summewenn().
Gruß,
steve1da

AW: Summenprodukt mit Text

14.08.2022 22:04:24

Daniel

Hi
kann man. Summenprodukt ist eine Auswertefunktion, die in der Lage ist Texte zu ignorieren, ähnlich wie Summe das auch kann.
man muss nur den Zellbereich, der neben den Zahlen auch Text enthalten kann, als eigenständigen Parameter in der Funktion angeben, ohne weitere Berechnung
mach aus dem
=WENN(AI5="";"";SUMMENPRODUKT((AI5=KALENDERWOCHE(--$C$5#;21))*BEREICH.VERSCHIEBEN($C$6;0;0;1; (ANZAHL(--$C$5#)))))
mal das hier:
=WENN(AI5="";"";SUMMENPRODUKT(--(AI5=KALENDERWOCHE(--$C$5#;21);BEREICH.VERSCHIEBEN($C$6;0;0;1; (ANZAHL(--$C$5#)))))
das Semikolon statt dem * macht aus dem Faktor Bereich.Verschieben() einen eigenständigen Parameter.
bei deiner Excelversion könntest du dir auch mal anschauen, ob das ganze mit FILTER und SUMME nicht einfacher wird.
Gruß Daniel
Als Faktor in der Multiplikation verursacht der Text einen Fehler, der dann als Formelergebnis verwendet wird.
Als eigenständiger Parameter kann ihn das Summenprodukt erkennen und ignorieren.
Das -- vor dem Vergleich brauchst du jetzt, um aus dem Wahrheitswert des Vergleichs die Zahl 0 oder 1 zu machen, da du nicht mehr die Multiplikation mit den Werten hast. Ein Wahrheitswert als eigenständiger Parameter würde genauso ignoriert wie die Texte.
Gruß Daniel

AW: Summenprodukt mit Text

14.08.2022 22:09:52

onur

Nur ein Tip:
statt


WENN(ODER(WOCHENTAG(C5;2)=6;(WOCHENTAG(C5;2) =7);....

geht auch


WENN(WOCHENTAG(C5;2)>5;......)

Wieso eigentlich einmal


WOCHENTAG(C5;1)=6

und dann


WOCHENTAG(C5;2)=6

=AGGREGAT(9;6;A1:M1)

14.08.2022 22:16:02

ignoriert Texte bei der Summierung.
WF

AW: Summenprodukt mit Text

14.08.2022 22:48:34

Steffi

Hello Leute,
danke für die Hilfe, hat mir wirklich geholfen :-)
@Daniel / Danke hat wirklich super geklappt :-)
@Onur Hab eben mal getestet, einmal 1 und dann 2 weil bei mir sonst, der Sonntag nicht mit FREI angezeigt wird. Er gibt sonst 08:20 aus.
@ SF und WF / Vielen Dank für Eure Hilfe... bin immer froh zu lernen. :-)

AW: Summenprodukt mit Text

14.08.2022 22:51:09

onur

Das beweist meine Vermutung, dass du nicht wirklich weisst, was die 1 bzw 2 überhaupt bedeutet.....

AW: Summenprodukt mit Text

14.08.2022 22:49:42

Daniel

Hi
noch eine andere Möglichkeit:
ändere in der Formel ab Zelle C6 das "FREI" in 0.
das 0 geht nicht in die Summe ein und erzeugt auch keinen Fehler.
Wenn in den Zellen statt 0 "FREI" stehen soll, kannst du das über das benutzerdefinierte Zahlenformat regeln: hh:mm;-0;"FREI";@
dann wird anstelle von 0 der der Text "FREI" angezeigt, aber in der Zelle steht weiterhin 0 und kein Text
Gruß Daniel

Infobox / Tutorial

Summenprodukt mit Text ignorieren in Excel

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um in Excel das Summenprodukt zu verwenden und dabei Text wie "FREI" zu ignorieren, kannst du folgende Schritte befolgen:

Überprüfe deine Formel: Stelle sicher, dass du die richtige Formel verwendest. Zum Beispiel:

=WENN(AI5="";"";SUMMENPRODUKT(--(AI5=KALENDERWOCHE(--$C$5#;21);BEREICH.VERSCHIEBEN($C$6;0;0;1;(ANZAHL(--$C$5#)))))

Verwende das richtige Trennzeichen: Ersetze das Multiplikationszeichen (*) durch ein Semikolon (;) zwischen den Bedingungen, um die Texte zu ignorieren:
```
=WENN(AI5="";"";SUMMENPRODUKT(--(AI5=KALENDERWOCHE(--$C$5#;21);BEREICH.VERSCHIEBEN($C$6;0;0;1;(ANZAHL(--$C$5#)))))
```
Testen: Überprüfe das Ergebnis, um sicherzustellen, dass die Formel korrekt funktioniert und Texte ignoriert werden.

Häufige Fehler und Lösungen

Fehler #WERT: Dieser Fehler tritt auf, wenn die Formel Textwerte in einem Bereich hat, der nur Zahlen enthalten sollte. Verwende die oben genannten Schritte, um diesen Fehler zu vermeiden.
Falsches Ergebnis: Wenn du unerwartete Ergebnisse erhältst, überprüfe, ob alle Zellreferenzen korrekt sind und ob du das richtige Trennzeichen verwendest.

Alternative Methoden

Wenn das Summenprodukt nicht die gewünschten Ergebnisse liefert, kannst du auch die Funktion SUMMEWENN() verwenden. Hier ein Beispiel:

=SUMMEWENN(KALENDERWOCHE(C5:C10;21);AI5;B6:B10)

Diese Funktion summiert nur die Werte, die den angegebenen Kriterien entsprechen und ignoriert Text automatisch.

Praktische Beispiele

Wenn du beispielsweise die Arbeitszeiten für Kalenderwochen berechnen möchtest, kannst du die folgende Formel verwenden:

=SUMMENPRODUKT((KALENDERWOCHE($C$5:$C$10;21)=AI5)*($C$6:$C$10))

Diese Formel summiert die Werte in der angegebenen Woche und ignoriert dabei Text wie "FREI".

Tipps für Profis

Nutze benutzerdefinierte Zahlenformate: Wenn du in einer Zelle "FREI" anzeigen möchtest, aber die Berechnungen nicht beeinflussen soll, kannst du das benutzerdefinierte Zahlenformat verwenden:
```
hh:mm;-0;"FREI";@
```
Dies zeigt "FREI" an, behält aber die Zahl 0 in der Zelle.
FILTER() anstelle von SUMMENPRODUKT(): In neueren Excel-Versionen kannst du auch die Funktion FILTER() nutzen, um nur die relevanten Daten zu erhalten und sie dann zu summieren.

FAQ: Häufige Fragen

1. Kann ich das Summenprodukt auch ohne Text verwenden? Ja, das Summenprodukt kann ohne Text verwendet werden. Du kannst es einfach auf die Zahlenbereiche anwenden.

2. Was mache ich, wenn ich andere Texte in der Berechnung habe? Wenn du andere Texte hast, die du nicht ignorieren möchtest, stelle sicher, dass du nur die relevanten Zellen in die Formel einbeziehst, oder nutze die SUMMEWENN()-Funktion.

Beliebteste Forumthreads (12 Monate)

Forumthread: Summenprodukt mit Text

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Summenprodukt mit Text ignorieren in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread