Excel: Summenprodukt mit Leerstrings in Array

Computer: Software & Programmierung Office Software
#1

Hallo liebe Excel-Experten,

ich habe eine Tabelle mit folgendem vereinfachten Inhalt:

Länge Anzahl Ja/Nein
1100 1 Ja
1500 2 Nein
1350 1 Nein
1250 2 Ja

Ich möchte einen Summenprodukt aller Zeilen bilden, wenn in der dritten Spalte ein „Ja“ steht. Ab einer bestimmten Zeile folgen dann bis zum Array-Ende nur noch Leerstrings (wo also die Formel in diesen Zellen „“ ausgibt). Wie fange ich dies ab?

Hätte ich das Problem mit den abzufangenden Leerstrings nicht, wäre ich mit der Formel
SUMMENPRODUKT((A2:A6) * (B2:B6) * (C2:C6=„Ja“))
fertig. Dann kommt bei obigem Beispiel richtigerweise 3600 raus.

Ich habe bei obigem Beispiel aber als Ergebnis ab Zeile 6 als Ergebnis „“ anstatt Null.
Mit etwas Googelei bin ich auf einen Lösungsvorschlag wie folgt gekommen, um die Leerstrings abzufangen:
SUMMENPRODUKT((A2:A6>0) * (B2:B6>0) * (C2:C6=„Ja“))
Dann erhalte ich aber fälschlicherweise das Ergibnis 2 (scheinbar eine Anzahl, aber kein Summenprodukt).

Kann mir jemand sagen, was ich falsch mache?

Vielen Dank vorab für jede Hilfe!

#2

Bei mir klappt es mit einer Matrixformel:
{=SUMMENPRODUKT(WENN(ISTZAHL(A2:A6);(A2:A6)) * WENN(ISTZAHL(B2:B6);(B2:B6)) * (C2:C6=„Ja“))}
Die geschweiften Klammern nicht von Hand eingeben! Sondern: Die Formel eingeben und dann die Eingabe nicht mit RETURN abschliessen sondern mit SHIFT + CTRL + RETURN.
Grüsse Niclaus

1 Like
#3

Hallo.

Du solltest Formeln nicht nur abschreiben, sondern auch verstehen was sie machen. Bezogen auf das was in der Formel steht ist das Ergebnis nämlich korrekt. Die Formel prüft ob die einzelnen Werte größer als 0 sind und hat dann eben ein Array mit Prüfwerten statt den ursprünglichen Zahlen. Im Endeffekt änderst du, bezogen auf die ersten vier Zeilen aus deinem Beispiel, die Rechnung von:

1100*1*WAHR+1500*2*FALSCH+1350*1*FALSCH+1250*2*WAHR=3600

zu:

WAHR*WAHR*WAHR+WAHR*WAHR*FALSCH+WAHR*WAHR*FALSCH+WAHR*WAHR*WAHR=2

Da WAHR und FALSCH im Prinzip nichts anderes als 1 und 0 sind, funktioniert das in dem Fall als Rechnung und liefert eben ein entsprechendes Ergebnis.

Statt der vorgeschlagenen Matrixformel könntest du auch eine Hilfsspalte verwenden mit:

=WENN(ANZAHLLEEREZELLEN(A2:C2);"";A2*B2*(C2="Ja"))

und in der Ergebniszelle berechnest du nur noch die SUMME() der Hilfsspalte.

Gruß
Tobias

#4

Hallo Tobias
Könnte man für Deine Hilfsspalte in Zeile 6 nicht auch folgende Formel einsetzen:
=WENN(A6="";"";A6 * B6 * (C6=„Ja“))?

Gelernt habe ich dank Dir: Für ANZAHLLEEREZELLEN() ist der Zellinhalt ="" eine leere Zelle.

Im Gegensatz zu ISTLEER(): Eine Zelle mit dem Inhalt ="" ist für diese Formel nicht leer.

Grüsse Niclaus

#5

Vom Prinzip her genau richtig, allerdings prüft das exakt so nur Spalte A und nach der Formel in der Fragestellung zu urteilen soll Spalte B auch geprüft werden. Du kannst aber z.B. einfach A6="" zu (A6="")+(B6="") ändern und hast beide Prüfungen.
Es gibt sicherlich noch einige andere Möglichkeiten, ANZAHLLEEREZELLEN() war einfach nur mein erster Einfall.

Ohne Einblick in die genaue Funktionsweise von Excel ist das letztendlich nicht mit absoluter Sicherheit zu beantworten aber meine Interpretation ist:

Mit Anführungszeichen markierst du einen String, also eine Zeichenkette. Mit ="beispieltext" sagst du z.B. einer Zelle dass ihr neuer Wert der String beispieltext ist. Wenn du stattdessen ="" schreibst, sagst du der Zelle dass ihr Wert ein leerer String ist. Einerseits hat der Inhalt der Zelle keinen eigenen Wert, andererseits ist der Zelle aber bekannt dass sie einen String enthält, was wohl von ISTLEER() geprüft wird. Davon abgesehen spielt das aber, so weit ich mich erinnere, nur in sehr speziellen Situationen eine Rolle.