So lässt sich die Funktion SUMMENPRODUKT nutzen, 13 Fälle

Dieser Beitrag zeigt 13 Möglichkeiten, die Excel-Funktion SUMMENPRODUKT zu verwenden. Doch denke nicht, dass dies eine erschöpfende Zusammenstellung ist. Es gibt nahezu unzählige weitere Beispiele, wie die Funktion zum Einsatz kommen kann. Vielleicht schreibe ich irgendwann einen neuen Beitrag darüber.

Die Funktion SUMMENPRODUKT gehört zur Kategorie Mathematik und Trigonometrie und reiht sich damit bei den gebräuchlichsten mathematischen Funktionen wie SUMME, SUMMEWENN und SUMMEWENNS ein.

1. SUMMENPRODUKT gemäß Excel-Hilfe

Zum Einstieg zeige ich dir, wie die Funktion standardmäßig eingesetzt wird.

Die Syntax lautet:

SUMMENPRODUKT(Matrix1;Matrix2;Matrix3; …)

wobei Matrix1; Matrix2;… 2 bis 255 Matrizen sind, deren Komponenten zunächst multipliziert und dann addiert werden.

Ein Beispiel dazu: Die nachfolgende Tabelle enthält Flächenangaben in ha und dazugehörige Verbräuche, z.B. Kalidünger, pro ha.

SumProdukt1

Errechnet werden soll der Gesamtverbrauch für die Gesamtfläche. Dazu nutze ich die Funktion SUMMENPRODUKT:

=SUMMENPRODUKT(B5:B14;C5:C14) =6.323

Wie hat die Funktion gearbeitet?
Rechne z.B. in Spalte D: 27*19 / 40*19 / 29*16 usw. und addiere dann alle Produktwerte aus D5:D14. Als Ergebnis erhältst du ebenfalls 6.323.

Du hast zwei Rechenoperationen durchgeführt. Einmal hast du die Produkte aus Fläche und Verbrauch pro ha gebildet und dann hast du die Produkte addiert. Dazu könntest du auch diese Formel verwenden:

{=SUMME((B5:B14)*(C5:C14))}

Du siehst, das ist eine Matrixformel. Die geschweiften Klammern sind nicht zu schreiben, sondern entstehen, indem du die Formel mit Strg + Shift + Enter abschließt.

SUMMENPRODUKT selbst ist schon eine Matrixformel, auf die geschweiften Klammern kannst du daher verzichten. Schon wegen der fehlenden Klammern ist SUMMENPRODUKT leichter zu händeln.

Es gibt noch eine zweite gebräuchliche Schreibweise, die allerdings nicht in der Excel-Hilfe erläutert wird:

=SUMMENPRODUKT((B5:B14)*(C5:C14)) =6.323

Was ist hier anders?
Die Matrizen werden in Klammern gesetzt und nicht, durch Semikolon getrennt, aufgezählt, sondern auch sichtbar, durch das Zeichen „*“, multipliziert.

 

2. Die Matrizen durch die Funktion BEREICH.VERSCHIEBEN definieren

Trägst du in die Funktion BEREICH.VERSCHIEBEN nicht nur Bezug, Zeile und Spalte, sondern auch noch Höhe und Breite ein, bestimmst du dadurch eine Matrix. Dazu habe ich bereits in einem früheren Artikel geschrieben.

Diesen Umstand nutze ich im zweiten Beispiel mit SUMMENPRODUKT.
Hier will ich die erste Matrix, also B5:B14 auf diese Weise definieren und das geschieht so:

Als Bezug für BEREICH.VERSCHIEBEN setze ich $B$4 fest, die Zelle, in der „ha“ steht. Von dort aus gehe ich eine Zeile nach unten und 0 Spalten nach rechts. Damit bin ich auf B5. Meine Matrix soll sich von B5 bis B14 ausdehnen, das sind 10 Werte. Als Höhe trage ich daher 10 und als Breite 1 ein, den der Bereich ist nur eine Spalte breit.

Somit lautet die Formel:

=BEREICH.VERSCHIEBEN($B$4;1;0;10;1)

Damit ersetze ich die erste Matrix in meiner Formel SUMMENPRODUKT:

=SUMMENPRODUKT(BEREICH.VERSCHIEBEN($B$4;1;0;10;1);C5:C14)

Das Ergebnis lautet auch hier wieder 6.323.

Ebenso verfahre ich, wenn ich die zweite Schreibweise bevorzuge:

=SUMMENPRODUKT((BEREICH.VERSCHIEBEN($B$4;1;0;10;1))*(C5:C14))

Das Ergebnis ist auch 6.323.

 

3. Die Matrizen durch Werteaufzählung bestimmen

Bisher habe ich die Matrizen über die Bereiche definiert. Eine Matrix kann aber auch bestimmt werden, indem die Werte, durch Semikolon getrennt, in geschweiften Klammern aufgelistet werden.

Den Bereich B5:B14 ersetze ich durch:
{27;40;29;33;35;43;35;28;36;32}

Eingesetzt in meine SUMMENPRODUKT-Formeln sieht das so aus:

=SUMMENPRODUKT({27;40;29;33;35;43;35;28;36;32};C5:C14)

Das Ergebnis ist keine Überraschung mehr: 6.323.

Verwende ich die zweite Formel-Variante, schreibe ich:

=SUMMENPRODUKT(({27;40;29;33;35;43;35;28;36;32})*(C5:C14))

und erhalte auch 6.323.

 

4. Die Matrizen namentlich benennen

Bereichen einen Namen zu geben und damit zu operieren, ist eine praktische Sache, die zu mehr Übersicht in langen Formeln führt.

Ich gebe deshalb der Matrix C5:C14 den Namen Verbrauch und setze diesen in die Formeln ein.

=SUMMENPRODUKT(B5:B14;Verbrauch)

bzw.

=SUMMENPRODUKT((B5:B14)*Verbrauch)

Mit beiden Formeln erhalte ich auch hier 6.323.

 

5. Mit INDIREKT auf einen Matrixnamen zugreifen

Mit der Funktion INDIREKT kann der Bezug auf einen mit Namen benannten Zellbereich zurück gegeben werden.

Ich schreibe den Namen der Matrix C5:C14 in die Zelle E4.

SumProdukt2

Statt der zweiten Matrix verwende ich in beiden SUMMENPRODUKT-Formeln nun den Ausdruck

=INDIREKT($E$4)

Die erste Formel:

=SUMMENPRODUKT(B5:B14;INDIREKT($E$4))

Die zweite Formel:

=SUMMENPRODUKT((B5:B14)*INDIREKT($E$4))

Ergebnis jeweils 6.323.

Positiv an dieser Variante ist, dass sich der Namen in E4 und damit auch der zugehörige Bereich dynamisch ändern können. Die SUMMENPRODUKT-Formeln greifen immer indirekt auf den Wert in E4 zu.

 

6. Matrizen aus verschiedenen Dateien

Es kann durchaus vorkommen, dass sich die Flächenmatrix und die Verbrauchsmatrix in verschiedenen Dateien befinden.

Die Formeln habe ich so geschrieben, wobei die Fremddatei ist in diesem Fall geöffnet ist:

=SUMMENPRODUKT(B5:B14;[Lagerdatei.xlsx]Verbräuche!$B$11:$B$20)

bzw.

=SUMMENPRODUKT((B5:B14)*([Lagerdatei.xlsx]Verbräuche!$B$11:$B$20))

Ergebnis jeweils 6.323.

 

7. Matrix durch INDIREKT und Verkettung definieren

Wie das mit INDIREKT geht, habe ich schon gezeigt. Nun will ich den Bezug, auf den INDIREKT zugreift, noch durch Verkettung erzeugen.

Den Bezug erzeuge ich z.B. in F5 so:

=“C“&ZEILE()&“:C“&ZEILE()+9

Das ergibt: „C5:C14“

Für die SUMMENPRODUKT-Formeln habe ich diese Möglichkeiten:

=SUMMENPRODUKT(B5:B14;INDIREKT($F$5))

und

=SUMMENPRODUKT((B5:B14)*(INDIREKT($F$5)))

In beiden Fällen erhalte ich als Ergebnis natürlich auch 6.323.

 

8. Flächenmatrix als Summe von jeweils drei Werten (drei verschiedene Flächen)

Bisher waren die gesamten Flächen in einer Matrix abgebildet. Wie aber ist zu verfahren, wenn sich die Werte in B5:B14 als jeweils Summe aus drei Teilflächen darstellen?
Beispielhaft habe ich in folgender Tabelle Werte dargestellt.

SumProdukt3

Ich könnte jetzt auf die Summenwerte in M5:M14 zugreifen und alles wäre beim Alten. Das will ich aber nicht, sondern jeweils die Bereiche in den Spalten J, K und L nutzen.

Für SUMMENPRODUKT muss ich also eine Matrix definieren, die erst die jeweils drei Werte addiert. So lässt sich das umsetzen:

=SUMMENPRODUKT(((J5:J14)+(K5:K14)+(L5:L14));C5:C14)

bzw.

=SUMMENPRODUKT((J5:L14)*(C5:C14))

In der ersten Formel muss ich die Teilbereiche in Klammern setzen und addieren, in der zweiten Formel nehme ich gleich den Bereich von J5:L14.

Mit beiden Formeln bekomme ich als Ergebnis 6.323.

 

9. Werte einer Matrix sollen mit „L“ beginnen

Die folgende Tabelle enthält Namen von Kunden, Bestellmengen, Einzelpreise und den Gesamtpreis.

SumProdukt4

Die farblichen Markierungen beziehen sich auf die Abschnitte 9 und 10.

OK, hier soll der Kundenname mit „L“ beginnen. Das ist nicht so schwierig, ich schreibe die Formel so:

=SUMMENPRODUKT((TEIL(B3:B12;1;1)=“L“)*(C3:C12)*(D3:D12))

und erhalte als Ergebnis 1.161. Die eingeflossenen Werte sind blau markiert, addiert sind demnach die Werte aus Spalte E für die Kunden Lehmann und Liebers.

Probiere das einfach mal aus, indem du die Anfangsbuchstaben „M“ oder „R“ oder andere wählst.

 

10. Werte einer Matrix sollen bestimmte Buchstaben enthalten

Im Beispiel 9 ging es darum, dass ein bestimmter Buchstabe am Anfang des Kundennamen steht. Hier sollen ein oder mehrere Buchstaben (als Folge) im Kundennamen enthalten sein.

Dazu habe ich drei Beispiele.
Zuerst soll der Buchstabe “a“ an zweiter Stelle im Namen stehen. Die Formel ist ähnlich der aus Beispiel 9.

=SUMMENPRODUKT((TEIL(B3:B12;2;1)=“a“)*(C3:C12)*(D3:D12))

Als Ergebnis erhalte ich so 1.336, das sind die Werte (grün) für die Kunden Bahlmann und Ramlow.

Wichtig ist, dass in der TEIL-Formel das Erste_Zeichen mit 2 benannt wird. Ob der Buchstabe groß oder klein geschrieben wird, ist unerheblich, beides funktioniert. Dennoch neige ich dazu, es auch deutlich zu machen, wenn ich einen kleinen Buchstaben suche.

Im zweiten Fall soll die Zeichenfolge „er“ an den letzten beiden Stellen des Namens stehen.
Dafür bietet sich die Funktion RECHTS an, ich schreibe daher:

=SUMMENPRODUKT((RECHTS(B3:B12;2)=“er“)*(C3:C12)*(D3:D12))

Das Ergebnis lautet 2.461, enthalten sind alle ockerfarben markierten Werte. Dazu gehören demnach die Kunden Müller, Meier, Krüger und Dudenhöfer.

Der dritte Fall zu Fall 10 fordert, dass ein (kleines) „t“ im Kundennamen enthalten sein soll, an welcher Stelle ist egal. Das geht mit dem bisherigen Formelaufbau nicht.
Ich verwende zum Aufsuchen des „t“ die Funktion FINDEN und verlange außerdem, dass das Ergebnis von FINDEN eine Zahl ist. Die Formel sieht so aus:

=SUMMENPRODUKT((ISTZAHL(FINDEN(„t“;B3:B12))*(C3:C12)*(D3:D12)))

Das Ergebnis ist 735 (violett markiert) und gehört zum Kunden Retzke.

Ich gebe zu, die Kombination von ISTZAHL und FINDEN habe ich in anderen Blogs, aber auch in Foren gefunden, sie ist kein Gedankenblitz von mir. Ohne ISTZAHL funktioniert es nicht.

 

11. Nur Werte eines bestimmten Monats berücksichtigen

Hierzu verwende ich eine Tabelle, die in der ersten Spalte ein Datum und in der zweiten z.B. eine Liefermenge enthält.

SumProdukt5

Aus der Tabelle soll die Summe der Mengen, die im Monat Januar geliefert wurden, ermittelt werden. Hinter dem Datum soll außerdem eine tatsächliche Menge stehen.

Dafür setze ich in die SUMMENPRODUKT-Formel die Funktionen MONAT und ISTZAHL ein.

=SUMMENPRODUKT((MONAT(B3:B14)=1)*(ISTZAHL(C3:C14))*(C3:C14))

Was passiert?
Der Monat soll 1, bedeutet Januar, sein. ISTZAHL muss das Ergebnis WAHR liefern. Soweit beides zutrifft, werden die Werte aus Spalte C addiert.

Im Beispiel ist der Monat Januar zweifach vertreten, in B4 und B5. In C4 und C5 stehen Zahlen, kein Text. Damit werden 564 und 232 addiert, das Ergebnis ist 796.

 

12. Dienstagswerte mit SUMMENPRODUKT und TEST

Eine Tabelle enthält Angaben zum Datum, zur Menge und zum Preis pro Mengeneinheit. Eine Möglichkeit, nur Dienstagswerte zu erfassen, ist, die Funktion REST anzuwenden. Als Divisor trage ich die 7 ein, denn es gibt 7 Wochentage.

SumProdukt6

Eine Möglichkeit, nur Dienstagswerte zu erfassen, ist, die Funktion REST anzuwenden. Als Divisor trage ich die 7 ein, denn es gibt 7 Wochentage.

=SUMMENPRODUKT((REST(A3:A26;7)=3)*(B3:B26)*(C3:C26))

Die Formel ermittelt die blau markierten Zeilen als Dienstagswerte. Menge und Preis/ME werden jeweils multipliziert und die Produkte addiert.

Das Ergebnis lautet 89.588.

 

13. Dienstagswerte mit SUMMENPRODUKT und TEXT

Für dieses Beispiel greife ich wieder auf die Tabelle aus Beispiel 12 zu und verwende statt REST die Funktion TEXT.

=SUMMENPRODUKT((TEXT(A3:A26;“TTT“)=“Di“)*(B3:B26)*(C3:C26))

Was macht TEXT?
Das Datum 26.01.2016 ist im Format „TTT“ das Kürzel für den Dienstag, „Di“. Die Funktion TEXT wandelt jetzt jeden Datumswert in eben das Wochentagskürzel um. Damit werden im Bereich A3:A26 drei Werte ermittelt, auf die „Di“ zutrifft.

Die Funktion SUMMENPRODUKT multipliziert jetzt nur die Werte aus B3:B26 und C3 und C26, auf die „Di“ zutrifft. Die Produkte werden abschließend addiert.
Das Ergebnis ist hier auch 89.588.

Ich habe nun die Formel noch ein wenig verändert und will nicht „Di“, sondern „Dienstag“ sehen. Dazu verändere ich das Zahlenformat von „TTT“ auf „TTTT“, mehr nicht.
=SUMMENPRODUKT((TEXT(A3:A26;“TTTT“)=“Dienstag“)*(B3:B26)*(C3:C26))
Das Ergebnis ist auch jetzt 89.588.

Zum Abschluss mache ich noch ein Spielchen. Ich lege mir eine Liste mit den Wochentagen „Sonntag“ bis „Samstag“ an und gebe ihr den Namen „Wochentage“. Einem Auswahlfeld in A28 teile ich mit der Datenüberprüfung die Liste „Wochentage“ als zulässig zu.

Ich setze zusätzlich die Funktion VERGLEICH ein, um festzustellen, ob das Datum in A3:A26 im Format „TTTT“ dem Wert im Auswahlfeld in A28, hier auch Dienstag, entspricht.

=SUMMENPRODUKT((VERGLEICH(TEXT(A3:A26;“TTTT“);
Wochentage;0)=VERGLEICH(A28; Wochentage;0))*(B3:B26)*(C3:C26))

Als Ergebnis erhalte ich wieder 89.588.

 

Zusammenfassung

In diesem Beitrag habe ich 13 Fälle zur Anwendung der Funktion SUMMENPRODUKT konstruiert. Vielleicht findest du Gefallen daran und magst den einen oder anderen für deine Zwecke nutzen.

Abschließend möchte ich dich noch auf einen zweiteiligen Beitrag verweisen, den ich in 2015 zum gleichen Thema für den Blog „Der Tabellenexperte“ schreiben durfte.

Advertisements

26 Kommentare zu „So lässt sich die Funktion SUMMENPRODUKT nutzen, 13 Fälle“

  1. Zwei Seelen, ein Gedanke … hab grad meinen Blogpost für morgen fertiggeschrieben … SUMMENPRODUKT. Den hab ich sofort um einen Link hierher erweitert.

    Sind nämlich wirklich coole Beispiele! Vor allem die vielen Möglichkeiten, Matrizen anzugeben, beflügeln die Phantasie 🙂 Und Wahrheitswerte-mit-SUMMENPRODUKT sind ohnehin mein heimlicher Liebling unter den Excel-Möglichkeiten 🙂

    Gefällt mir

    1. Ich danke ganz herzlich für den Link.
      Du hast ein schönes Beispiel gefunden, den Warenkorb, gefällt mir.
      Gefallen tun mir auch die speziellen Begriffe aus Österreich:
      Erdäpfel = Kartoffeln
      Paradeiser = Tomaten
      Danke für den Beitrag!

      Gefällt mir

  2. Hallo Gerhardt,

    du hast mir gerade für eine Durchschnittswertberechnung eine enorme Zeitersparnis geschenkt.

    In den Zellen F4:F32 habe ich einzelne Werte (Teilweise 0) und möchte nun einen Durchschnitt bilden Summe / Anzahl Werte 0….

    Dank dir bin ich nun auf folgende Formel für die Anzahl 0 gekommen und mag da einfach Danke sagen 🙂

    =SUMMENPRODUKT((F4:F320)*1)

    Viele Grüße
    Andreas

    Gefällt mir

    1. Hallo Andreas,
      das freut mich, dass ich einen bescheidenen Beitrag zur Lösung deines Problems leisten konnte.
      Danke für den Kommentar.
      Mit =MITTELWERTWENN(F4:F32;“0″;F4:F32)
      sollte es aber auch gehen.
      Viele Grüße
      Gerhard

      Gefällt mir

  3. Ich glaube, dass ich am Wochenende, mit Bezug auf deinen Beitrag, ein konkretes Beispiel veröffentlichen werde… Eigentlich war da ja das Thema Mindmapping angedacht, aber das hat zum Glück noch ein wenig Zeit um im Entwurf zu bleiben 🙂

    Viele Grüße
    Andreas

    Gefällt mir

  4. Das Problem war, dass im WP Kommentarfeld die < > Kleiner als und Größer als als HTML Code verstanden wurde…

    Es ging darum dass die Werte nur gezählt werden sollen wenn der Wert größer oder kleiner 0 ist.

    Konkret wäre die Formel dann

    =RUNDEN(SUMME(F4:F32)/SUMMENPRODUKT((F4:F32<>0)*1);-2)

    Ich habe größer als und kleiner als einmal in der Formel als != ersetzt 🙂

    =RUNDEN(SUMME(F4:F32)/SUMMENPRODUKT((F4:F32!=0)*1);-2)

    Gefällt mir

    1. Hallo!

      Mit Summenprodukt funktioniert die Durchschnittsberechnung (ohne Nullwerte) natürlich auch, ich würde aber an dieser Stelle die kürzere Funktion

      =MITTELWERTWENN(F4:F32;“0″;F4:F32)

      nutzen.

      Gefällt mir

    2. Hallo Thomas,
      ich danke für deinen Kommentar. Selbst würde ich auch MITTELWERTWENN bevorzugen, zumal die Funktion mit „Mittelwert“ schon einen Hinweis zum Ergebnis gibt. Aber SUMMENPRODUKT geht eben auch.
      Grüße
      Gerhard

      Gefällt mir

  5. Hallo Thomas,
    vielleicht kannst du mir helfen:
    In meiner Datei verwende ich unter anderem folgende Formel:
    =SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(„N“&ZEILE(3:1000)))*(H3:H1000=“PP/AmEx“)*(N3:N1000″“))
    Sie bezieht sich auf Daten in einer dynamischen Tabelle mit z.Zt. 63 Zeilen und das Ergebnis ist korrekt.
    Sobald ich aber eine weiter Zeile in der Tabelle hinzufüge (z.B. Zeile 64) sieht die Formel so aus:
    =SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(„N“&ZEILE(3:1000)))*(H3:H1001=“PP/AmEx“)*(N3:N1001″“))
    und das Ergebnis ist #NV.
    Was mach ich falsch? Herzlichen dank für deine Hilfe.
    Schöne Grüße
    Roland

    Gefällt mir

    1. Hallo Roland,

      ich denke, dass du mich, Gerhard, als den Autor meinst.
      Versuche es statt mit TEILERGEBNIS doch nur mit SUMME, etwa so:
      =SUMMENPRODUKT((SUMME(N$3:N1000))*(H$3:H1000=“PP/AmEx“)*(N$3:N1000))
      Bei mir hat es so funktioniert.

      Viele Grueße
      Gerhard

      Gefällt mir

  6. Hallo Gerhard,
    vielen Dank für deine Mühe und die schnelle Antwort.
    Inzwischen habe ich den Fehler gefunden. Es lag an „N“&ZEILE(3:1000).
    Nachdem ich daraus „N“&ZEILE($N$3:$N$1000) gemacht habe, hat alles funktioniert.
    Schöne Grüße
    Roland

    Gefällt mir

  7. Vielen Dank für diesen hilfreichen Blogeintrag. Trotzdem komme ich nicht ganz klar:

    Ich versuche Flugzeiten in Spalte P zu addieren, die in einem Teil der Spalte M den Text „CJ2“ oder „XLS“ enthalten. Also schreibe ich:

    =SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*($P$1:$P72))

    Die Syntax habe ich so aus irgendeinem Forenbeitrag entnommen. Das ergibt jedoch #WERT! Das verstehe ich schon nicht.

    Schreibe ich es hingegen so:

    =SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*(SUMME($P$1:$P72)))

    Dann ergibt es zwar einen Wert, aber der wird mit 5 multipliziert (weil die angegebenen Kriterien in $M$1:$M72 halt auf 5 Zellen zutreffen). Das erscheint mir zwar eigentlich logisch, aber sämtliche Forenbeiträge (und auch dieser Blog) behaupten, es würden nur die Werte in $P$1:$P72 addiert, jedoch nicht mit mit der Anzahl der zutreffenden Zellen multipliziert werden.

    Was mache ich falsch und wie mache ich es richtig? Excel 2013, übrigens.

    Gefällt mir

    1. Hallo Michael,

      eine Lösung für deine Formel habe ich im Moment nicht.
      Vielleicht magst du es mit dieser Formel versuchen, das funktioniert:
      =SUMMEWENN(M1:M72;“XLS“;P1:P72)+SUMMEWENN(M1:M72;“CJ2″;P1:P72)
      Wenn du Zeiten im Zahlenformat hh:mm verwendest, funktioniert es nicht wirklich. Nehme das Zahlenformat [hh]:mm.

      Grüße
      Gerhard

      Gefällt mir

  8. P.S. zu meinem letzten Beitrag: Ich sehe gerade, dass die Anführungszeichen in typographische umgewandelt wurden. Gemeint sind natürlich die „“ (hoffe, die werden jetzt nicht auch wieder konvertiert).

    Die eigentliche Formel ist etwas komplexer, die oben ist die verkürzte Form (führt aber zum selben Ergebnis). Hier noch die derzeit vollständige:

    =SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*(NICHT(ISTFEHLER(ZEITWERT(TEXT($P$1:$P72;“[h]:mm;@“)))))*(NICHT(ISTFORMEL($P$1:$P72)))*($P$1:$P72))

    Aber auch das ergibt #WERT! (bzw. in der anderen Variante ein mit der Anzahl der zutreffenden Zellen multipliziertes Ergebnis).

    Gefällt mir

    1. Hallo Gerhard,
      danke erstmal für die Hilfe. Du schriebst:
      [Zitat]
      ich habe weiter nachgedacht. So sollte es funktionieren:
      =SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*($P$1:$P72))
      Alles im Zahlenformat [hh]:mm
      [Zitatende]
      Das ist aber doch exakt dieselbe Formel, die ich bereits in meinem Beitrag vom 2. September 2017 um 0:30 erwähnt hatte (und die #WERT! ergibt):
      [Zitat]
      Ich versuche Flugzeiten in Spalte P zu addieren, die in einem Teil der Spalte M den Text „CJ2“ oder „XLS“ enthalten. Also schreibe ich:
      =SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*($P$1:$P72))
      Die Syntax habe ich so aus irgendeinem Forenbeitrag entnommen. Das ergibt jedoch #WERT! Das verstehe ich schon nicht.
      [Zitatende]
      Das mit dem Zahlenformat [hh]:mm ist mir natürlich geläufig.
      Einen Grund für das Problem habe ich aber mittlerweile gefunden: SUMMENPRODUKT funktioniert offenbar nur dann, wenn der zu summierende Spaltenbereich – in diesem Beispiel also ($P$1:$P72) – ausschließlich numerische Werte enthält. Befinden sich in einzelnen Zellen des Bereichs keine numerischen Werte (sondern z.B. Text), dann ergibt SUMMENPRODUKT eben #WERT!, statt den nicht numerischen Wert einfach zu ignorieren (wie es SUMME tut).
      Leider habe ich also noch keine Lösung.

      Gefällt mir

    2. Hallo Michael,

      ja sicher ist es die gleiche Formel, denn sie funktioniert doch, wenn das Zahlenformat [hh]:mm ist und wenn Flugzeiten auch als Zahl in der Spalte P stehen.
      Markiere doch einfach P1:P72 und setze das richtige Zahlenformat. Eine Flugzeit wird doch nicht mit „abc“ o.ä. angegeben sein. Übrigens rechnet SUMMENPRODUKT auch mit Zahleneinträgen im Textformat, nur nicht mit Buchstaben.

      Grüße
      Gerhard

      Gefällt mir

  9. Hallo Gerhard,
    natürlich steht da nicht „abc“, aber ohne jetzt allzu sehr ins Detail zu gehen und die komplette Tabelle zu posten: Es steht in der Spalte P z.B. schon mal „(1:24)“ in Klammern – und zwar deshalb, weil diese Flugzeit aus bestimmten Gründen und ganz bewusst dem Kunden nicht berechnet (aber trotzdem „irgendwie“ und rein informativ angezeigt) werden soll. Oder es sind ganze Textzeilen als Kommentar in der ein gesamtes Jahr umfassenden Tabelle vorhanden.
    SUMME hat damit kein Problem, SUMMENPRODUKT aber offenbar schon. Das ist aber bisher nirgends klar erwähnt worden.
    Mit dem Zahlenformat [hh]:mm hat das nix zu tun, das kenne und verwende ich seit Jahren (wenn nicht Jahrzehnten). 😉

    Gefällt mir

    1. Hallo Michael,

      ich danke dir für die ergänzenden Angaben. Auch wenn ich mich intensiv mit Excel beschäftige, kann ich nicht alle Fälle kennen, die auftreten können.
      Besser wäre es aus meiner Sicht, wenn die Spalte P wirklich nur den richtigen Zeiten vorbehalten bleibt. Hinweise auf Nichtberechnung oder Kommentare lassen sich doch sicher in gesonderten Spalten unterbringen. Dann macht auch SUMMENPRODUKT keine Probleme.
      Ich habe also hier und heute keine Lösung für dich.

      Grüße
      Gerhard

      Gefällt mir

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s