Tag Archives: SUMMENPRODUKT

Eben am Kondomständer im Drogeriemarkt vorbeigelaufen. So nah am Sex war ich schon lange nicht mehr.

Hallo Herr Martin,

ZÄHLENWENN (und SUMMEWENN) kenne ich. Damit kann ich die Anzahl der Zeilen bestimmen, die einem Kriterium genügen. ZÄHLENWENNS kenne ich auch. Damit kann ich zählen, wie oft mehrere Kriterien erfüllt sind, die mit einem logischen UND verknüpft sind.

Soweit so gut.

Aber wie kann ich eine Formel erstellen (gerne auch mit SUMMENPRODUKT), in der die Zeilen gezählt werden, bei denen in der ersten oder zweiten oder dritten Spalte nichts steht. Also: mindestens eine Spalte muss gefüllt sein.

Hallo Frau S.

ich würde die die drei Zellen verketten und wenn alle drei nicht leer sind, also <>““, dann würde ich diese zählen. Da Falsch = 0 und Wahr = 1, kann man die Summe verwenden.

beispielsweise so

=SUMME((C4:C15&D4:D15&E4:E15<>"")*1)

Hallo Herr Martin,

vielen Dank für die pragmatische Lösung, gefällt mir 😉

Früher waren es Mickey-Maus Hefte. Dann folgten Yps und Bravo. Anschließend der Playboy. Und heute ist es die Apotheken-Umschau.

Einfach nicht aufgepasst!

Excel-VBA-Schulung. Eine Teilnehmerin möchte ein kleines Programm mit mir geschrieben haben: Jede Woche erhält sie eine Liste und jede Woche muss sie in dieser Liste Berechnungen durchführen. Eine bestimmte ID (beispielsweise Idefix) wird gesucht, sämtliche Werte (hier drei) werden wie folgt berechnet:

Die Anzahl der Römer wird mit der Anzahl der Piraten multipliziert und die einzelnen Produkte summiert. Das Ergebnis wird durch die Summe der Römer dividiert. Aber nur dann, wenn keine Hinkelsteine vorhanden sind. Alles klar? – Klar!

Ich beginne Schritt für Schritt. Multipliziere und addiere – hierfür bietet sich doch SUMMENPRODUKT an, oder?. Also: los geht’s:

=SUMMENPRODUKT((A:A=G5)*(B:B)*(C:C))

Ich habe drei Mal überlegen müssen, woher die Fehlermeldung rührt. Die Antwort:

Klar: ich multipliziere jede Zelle jeder Spalte. Und das funktioniert bei der Überschrift (Text!) natürlich nicht!

Ich muss ändern. Entweder so:

=SUMMENPRODUKT((A:A=G5)*1;(B:B);(C:C))

Oder indem ich auf den Bereich ohne Überschrift verweise:

=SUMMENPRODUKT((A2:A40=G5)*(B2:B40)*(C2:C40))

Oder indem ich den Fehler mit WENNFEHLER abfange, oder oder oder.

Und DANN ist der Rest auch kein Problem – beispielsweise so:

=SUMMENPRODUKT((A2:A40=G9)*1;(B2:B40);(C2:C40);(D2:D40<>"Hinkelstein")*1)/SUMMEWENNS(B2:B40;A2:A40;G9;D2:D40;"<>Hinkelstein")

Gerechnet wird also:

(10 x 31 + 40 x 50) / (10 + 40) = 46,2

Und diese Formel kann man mit dem Makrorekorder aufzeichnen und über alle Zellen „laufen lassen“. Das Ganze wird in der Datei Personal.xlsb gespeichert.

Früher war alles leichter. Ich auch.

War das früher besser? Zumindest ging das früher!

Vorn vorne: die Excel-Funktion HÄUFIGKEIT ist eine Matrixfunktion. Mit ihrer Hilfe (ebenso wie mit ZÄHLENWENN oder SUMMENPRODUKT) kann man die Häufigkeit von Daten in einer Liste bestimmen, beispielsweise, wie oft Notenwerte vorhanden sind. Dabei muss Die Funktion als Matrixfunktion verwendet werden, das heißt mit [Umschalt] + [Strg] + [Enter] beendet werden:

Das funktioniert bei exakt vorkommenden Werten, aber auch bei „Zwischenwerten“ – von – bis:

Man kann die Funktion aber auch als eine Funktion verwenden und runterziehen – dann werden die Werte kumuliert:

Nein – so konnte man das früher machen – vor den Spillfunktionen (den Arrayfunktionen SEQUENZ, SORTIEREN, EINDEUTIG, …) machen. Seit Microsoft diese Funktionen und diese Arbeitstechnik eingeführt hat, erzeugt HÄUFIGKEIT beim Markieren eines Wertes zwei Ergebnisse: Wie oft taucht dieser Wert auf und wie viele andere Werte sind vorhanden.

Und heute? – Man muss HÄUFIGKEIT mit einem @ entwerten, dann hat man wieder die gleiche Funktionalität wie früher:

Danke an Mourad Louha für diesen Hinweis.

Ich lese „SPÜLMASCHINENFEST“. Aber nicht wann, wo, Kosten? Weiß jemand mehr darüber?

Guten Tag Herr Martin,

ich hatte schon lange kein Problem mehr mit Excel, sicherlich auch dank Ihrer Kurse, die ich mir in LINDEDIN Learning immer wieder einmal ansehe.

Heute nun habe ich ein Problem, bei dem Sie mir vielleicht helfen können. Wenn es allerdings nicht so nebenher geht, dann können Sie mir dies gerne mitteilen, dann muss ich weiter forschen.

Ich möchte mir hervorgehobene Zellen zählen lassen, genau genommen, möchte ich beim Wahlverfahren D’Hondt sofort sehen, wie viele Sitze hat Liste 1, 2…

Möglicherweise geht es auch nicht mit dem Zählen der hervorgehobenen Zellen sondern anders.

Ich habe es mit SVERWEIS versucht, was leider dann ab der 5. Zeile ein „NV“ brachte – mir leider unverständlich. Mit INDEX und VERGLEICH komme ich auch nicht weiter, weil ich ja keine genaue Zeilen oder Spalenzahl angeben kann. Diese kann ja – je nach Höchstwert – variieren.

Ich sende Ihnen die Datei einmal zu und freue mich auf Ihre Antwort, kann aber auch verstehen, wenn Sie schreiben: Kann ich Ihnen nun leider nicht mitteilen, dauert zu lange…

Trotzdem danke für Ihr offenes Ohr.

Ich wünsche Ihnen noch einen schönen Tag.

Hallo Frau P.,

das ist eine hübsche Fingerübung.

Ein paar Anmerkungen:

1. man kann Farben in Excel nicht zählen. Und wenn ich jetzt von Ihnen die Funktion ZELLE höre – mit Einschränkungen ja. Aber – diese Funktion reagiert nicht auf Formatänderungen. Und: zeigt auch nicht alle Formate an!

2. Ich würde es klassisch mit SUMMEWENN lösen. SVERWEIS, INDEX & co greifen auf mehrere Spalten zu. Mit der neuen Funktion XVERWEIS kann man es auch lösen – aber ich weiß nicht, ob sie diese schon haben. Werfen Sie einen Blick auf meine Lösung in Spalten M:O, bzw. zusammengefasst in Spalte Q.

=KGRÖSSTE($B$6:$E$10;ZEILE(A1))
=SUMMENPRODUKT((M6=$B$6:$E$10)*ZEILE($A$1:$A$5))
=INDEX($A$6:$A$10;N6)

3. Für welches Beispiel/Land verwenden Sie das? Ich lese bei wikipedia, dass noch die Schweiz, Spanien, Portugal, Belgien, Polen und Finnland dieses Verfahren verwenden. Sie wissen, dass dies in Deutschland 1985 durch das Hare-Niemeyer-Verfahren abgelöst wurde. Ich hätte es nämlich fälschlicherweise in meinem Excel-Formelbuch erläutert … und dann gemerkt, das wir so (in Deutschland) gar nicht mehr rechnen.

zusammengefasst

Hallo Herr Martin,

herzlichen Dank für Ihre schnelle Rückmeldung und Ihren Vorschlag der Berechnung.

Ich habe es jetzt noch einmal nachgebaut und verstanden, obwohl ich niemals auf die Formel gekommen wäre.

Zu Nr. 3:

Ich weiß nur, dass die Hochschule Furtwangen den Vorschlag unserer Justiziarin aus Stuttgart aufgegriffen hat und diesen in ihrer Wahlordnung nun festgelegt hat. Wir haben bisher mit Hare-Niemeyer gewählt. Aber leider soll es auch bei D’Hondt bleiben, obwohl wir eine Hochschule mit drei Standorten sind und unserer kleinster Standort mit diesem Wahlverfahren ernste Schwierigkeiten bekommen könnte. Ich hatte das (als Wahlleitung) angemerkt, es bleibt trotzdem bei D’Hondt.

Ich wünsche Ihnen noch einen schönen Tag und weiterhin viel Erfolg bei Ihrer Arbeit.

Ich bin heute so romantisch. Ich könnte alle Menschen drücken. Gegen eine Wand. Mit dem Gesicht zuerst.

Amüsant: Überprüft man, ob in einer leeren Zelle der Wert 0 steht, also =A1=0, so lautet das Ergebnis WAHR. Leer wird als 0 interpretiert. Das gleiche Ergebnis erhält man bei der Funktion SUMMENPRODUKT:

=SUMMENPRODUKT((B2:B17>=0)*1)

Die leeren Zellen werden als 0 interpretiert. Anders dagegen ZÄHLENWENN, SUMMEWENN & co – dort ist 0 etwas anderes, wie

=ZÄHLENWENN(B2:B17;“>=0″)

zeigt:

Danke an Dominic Dauphin für diesen Hinweis.

Gefühle sind heutzutage auch nur noch was für ganz Mutige.

Excel ist an vielen Stellen nicht konsequent. Der Text „1“ ist beispielsweise etwas anderes als die Zahl 1. Dennoch kann man den Text mit 1 multiplizieren.

Ebenso verhält es sich mit WAHR und 1. Eigentlich entspricht Wahr dem Wert 1 und Falsch dem Wert 0. Eigentlich. Multipliziert man die beiden Werte WAHR und FALSCH, beziehungsweise die Funktionen =WAHR() und =FALSCH() mit 1, erhält man 1 beziehungsweise 0. Intern handelt es sich jedoch um einen anderen Wert. Also
=WAHR=1

liefert FALSCH.

Und diese Unschärfe führt auch dazu, das Wahr-Werte nicht addiert werden können:

=SUMMENPRODUKT(C:C=“Briefpapier“)

liefert 0.

=SUMMENPRODUKT((C:C=“Briefpapier“)*1)

dagegen das korrekte Ergebnis: