Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
916to920
916to920
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Summenprodukt als VBA

Forumthread: Summenprodukt als VBA

Summenprodukt als VBA
25.10.2007 13:39:00
Verena
Hallo Leute,
ich habe eine Sub geschrieben, die wie folgt aussieht:

Private Sub CommandButton1_Click()
Dim zeile As Integer
Dim z As Integer
Set wsh1 = Sheets("Tab1")
Set wsh3 = Sheets("Tab2")
Dim b As Integer
Application.ScreenUpdating = False
Rows("3:1000").ClearContents
Rows("3:1000").Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
Selection.Font.Size = 9
zeile = 2
z = 2
b = 0
bereich = Array("A", "B", "C")
'Bereiche durchgehen:
For b = 0 To 2
Range("A" & z) = bereich(b)
Rows(z).Select
Selection.Font.Bold = True
Selection.Font.Size = 12
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
For zeile = 2 To 1000
'Handelt es sich um den gewünschten Bereich: Wenn Ja dann 1) sonst nächsten Bereich  _
untersuchen
If wsh1.Range("E" & zeile) > (bereich(b) & "00000") And wsh1.Range("E" & zeile) '1) Neuer Kunde? Wenn ja dann weiter sonst nächste Zeile
If wsh1.Range("A" & zeile)  wsh1.Range("A" & zeile - 1) Or ((Left((wsh1.Range("E" & zeile) _
), 1))  (Left((wsh1.Range("E" & zeile - 1)), 1))) Then
z = z + 1
wsh3.Range("A" & z) = wsh1.Range("A" & zeile)
wsh3.Range("B" & z) = wsh1.Range("B" & zeile)
'Berechnungen:
wsh3.Range("C" & z).Value = Application.WorksheetFunction.SumIf(wsh1.Columns(1), wsh3. _
Range("A" & z), wsh1.Columns(17))
        'wsh3.Range("D" & z) = Application.WorksheetFunction.SumProduct((wsh1.Range(Cells(1,  _
1), Cells(1, 1000)) = wsh3.Range("A" & z)) * (wsh1.Range(Cells(30, 1), Cells(30, 1000)) = wsh3.Range("D1")) * (wsh1.Range(Cells(17, 1), Cells(17, 1000))))
End If
End If
Next
zeile = 2
z = z + 4
Next
Application.ScreenUpdating = True
End Sub


Und die SumProduct-Formel (fett markiert) funktioniert nicht. Es ergibt immer einen Laufzeitfehler 1004 und ich weiß nicht, warum. Kann mir jmd weiterhelfen?
Lieber Gruß
Verena

Anzeige

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summenprodukt als VBA
25.10.2007 13:45:00
Verena
Ohne VBA funktioniert meine Formel übrigens wie folgt:
Tab2!D3=SUMMENPRODUKT((Tab1!A3:A1000=Tab2!A3)*(Tab1!AD3:AD1000=D$1)*(Tab1!Q3:Q1000))

Cells(1,1000) geht nur in Excel 2007
25.10.2007 14:05:00
NoNet
Hallo Verena,
beim Drüberschauen ist mir aufgefallen, dass Du Cells(1, 1000) als Bestandteil Deiner SUMPRODUCT-Funktion verwendest.
Cells(1,1) ist A1, Cells(1,2)=B1 etc.
Bis Excel 2003 gibt es nur 256 Spalten, d.h. max. Cells(1,256).
Ab Excel 2007 gibt es 16.384 Spalten, da funktioniert diese Bereichsangabe.
Oder meintest Du etwa Cells(1000,1) ?
Gruß, NoNet

Anzeige
AW: Cells(1,1000) geht nur in Excel 2007
25.10.2007 14:09:02
Verena
Oh ja, das hab ich versehentlilch vertauscht. :-)
Hab es jetzt geändert, aber funktioniert trotzdem net... :-(
Gruß
Verena

Hast Du auch Cells(30,1000) etc. getauscht ?
25.10.2007 14:09:44
NoNet
_oT

Ja:
25.10.2007 14:15:06
Verena
wsh3.Range("D" & z2) =
Application.WorksheetFunction.SumProduct((wsh1.Range(Cells(1, 1), Cells(1000, 1)) = wsh3.Range("A" & z2)) * (wsh1.Range(Cells(1, 30), Cells(1000, 30)) = wsh3.Range("D1")) * (wsh1.Range(Cells(1, 17), Cells(1000, 17))))

Anzeige
SUMPRODUCT funktioniert bei mir nicht mehr
25.10.2007 14:48:30
NoNet
Hallo Verena,
das ist ja der Oberhammer : Die VBA-Funktion Application.Worksheetfunction.SumProduct funktioniert bei mir nun überhaupt nicht mehr (Excel 2003 SP2).
Selbst bei einer einfachen Berechnung zeigt mir der Debugger einen Fehler an :
Userbild
Klicke ich auf "Sumproduct" und drücke F1, kann mir Excel auch keine Hilfe dazu anzeigen (zu anderen Funktionen schon !)
Ist da etwa etwas Grundsätzliches defekt ?!?!? Selbst nach Beendigung und Neustart von Excel war das Problem nicht behoben.
Jetzt muss ich aber echt staunen !!
rätselhafte Grüsse, NoNet

Anzeige
AW: SUMPRODUCT funktioniert bei mir nicht mehr
25.10.2007 15:03:00
Verena
Ja, das ist bei mir auch so. Will ich die Hilfe zu SumIf oder SumProduct ansehen, klappt das nicht.... :-(

SUMPRODUCT per VBA akzeptiert keine Vergleiche
25.10.2007 15:48:00
NoNet
Hallo Verena,
habe eben mal etwas diesbezügl. gegoogelt und etliche Beiträge zu diesem Thema gefunden.
Es wurde jedoch immer nur die Fehlermeldung genannt und nach der Ursache gefragt, aber konkrete Antworten gab es nirgends.
Allerdings kristallisiert sich heraus, dass Worksheetfunction.Sumproduct() unter VBA wohl nicht ganz so universell eingesetzt werden kann wie als Tabellenfunktion. Konkret : Man kann damit wohl tatsächlich nur die PRODUKTE summieren, jedoch keine Vergleichsoperatoren einsetzen.
Beispiel : Diese Tabelle (per Tabellenfunktionen kann man hier einiges berechnen) :
Mit den VBA-Varianten klappt es nur ohne den Vergleich :
Sub Summenprodukte()
    MsgBox Application.WorksheetFunction.SumProduct(Range("A1:A6", "B1:B6")) 'funktioniert : 72
    MsgBox Application.WorksheetFunction.SumProduct(Range("A1:A6"), Range("B1:B6")) 'funktioniert : 120
    'MsgBox Application.WorksheetFunction.SumProduct((Range("A1:A6") = 1) * (Range("B1:B6"))) 'LFZ 13
    'MsgBox Application.WorksheetFunction.SumProduct(Array((Range("A1:A6") = 1), Range("B1:B6"))) 'LFZ 13
    'MsgBox Application.WorksheetFunction.SumProduct(True * (Range("B1:B6"))) 'LFZ 13
    'MsgBox Application.WorksheetFunction.SumProduct((Array("1", "2", "3", "1", "2", "3")), (Range("B1:B6")))  'LFZ 1004
End Sub
Gruß, NoNet

Anzeige
AW: SUMPRODUCT per VBA akzeptiert keine Vergleiche
25.10.2007 16:05:00
Peter
Hallo NoNet,
zu deinem Beispiel:
MsgBox Evaluate("=SumProduct((A1:A10 = 1) * 1)")
geht, das ist aber eben nur ein Ausweg.
Gruß Peter

Schon klar, aber nicht per Worksheetfunction
25.10.2007 16:18:19
NoNet
Hallo Peter,
danke für Deine Ergänzung. MIR ist das schon klar, dass man EVALUATE oder auch die Kurzform [] verwenden kann. Es ging mir jedoch mehr darum, darzustellen, dass dies mit der WORKSHEETFUNCTION.SUMPRODUCT offenbar nicht funktioniert, während die anderen Worksheetfunctions schon recht gut in VBA einsetzbar sind.
Gruß, NoNet

Anzeige
Das liegt daran,...
25.10.2007 17:13:00
Luc:-?
...NoNet,
dass VBA zur kompletten Nachgestaltung dieser Fktionalität offensichtlich die .HasArray-Eigenschaft der Formelzelle benötigt - was wohl mit einer Subroutine nicht nachgestaltbar ist -, zumindest wenn gar kein direkter Bezug übergeben wird, sondern ein Datenfeld aus der Berechnung eines Ausdruckes im Bezug (da müsste wohl mit For Each...In gearbeitet wdn). Deshalb liefert die nachfolgende udF einen Fehlerwert (der ja mit MsgBox ohne Umwandlung in Text nicht ausgegeben wdn kann), wenn sie nicht als Matrixformel eingegeben wird, sonst aber das richtige Ergebnis.

Function ProduktSumme(ByVal DFeld1, Optional ByVal DFeld2)
If IsMissing(DFeld2) Then
ProduktSumme = WorksheetFunction.SumProduct(DFeld1)
Else: ProduktSumme = WorksheetFunction.SumProduct(DFeld1, DFeld2)
End If
End Function


Gruß Luc[ius]
:-?
PS: sum[ of ]product[s] = Produktsumme... ;-)

Anzeige
AW: Schon klar, aber nicht per Worksheetfunction
26.10.2007 10:20:59
Verena
Okay, ich kann das also so nicht machen, wie ich's machen wollte. Aber wie's jetzt richtig funktioniert hab ich noch net so raus...

AW: Summenprodukt als VBA
25.10.2007 13:45:00
Verena
Ohne VBA funktioniert meine Formel übrigens wie folgt:
Tab2!D3=SUMMENPRODUKT((Tab1!A3:A1000=Tab2!A3)*(Tab1!AD3:AD1000=D$1)*(Tab1!Q3:Q1000))
;

Forumthreads zu verwandten Themen

VBA Summenprodukt-Wochentag Typen unverträglich
Alfons

Hallo,
in Spalte A stehen die Tage: 01.02.2010, 02.02.2010 usw.
Zellformat ist Standard.
Mit VBA will ich davon die Anzahl der Montage ermitteln.
Dim rng As Range
Set rng = Range("A2:A65000")
MsgBox WorksheetFunction.SumProduct((Weekday(rng, 2) = 1) * 1)
Warum kommt...
29.03.2010 07:12:46
Thread-ID: 1148988
11 Beiträge
Excel VBA Summenprodukt
Susanne

Hallo Zusammen,
ich benötige Hilfe.
Ich teste gerade die Funktion Summenprodukt in VBA Excel 2013 mit Variablen...
Damits deutlicher wird habe ich meine Datei angehängt. Summenprodukt mit Variablen funktioniert, die zweite Variante Summenprodukt mit Variablen in Schleife funktionier...
14.01.2015 21:19:45
Thread-ID: 1402883
2 Beiträge
Anzeige
VBA Summenprodukt mit Nebenbedingung
Lenni

Guten Morgen,
Ich hänge schon seit einiger Zeit an einer Stelle und komme nicht weiter. Hoffe ihr könnt mir helfen.
Ich möchte mit VBA ein Summenprodukt schreiben, welches variabel gestaltet ist.
Dh. wenn ich zum Beispiel das Summenprodukt von Spalte A1:A6 mit B1:B6 nehme, möchte...
17.10.2016 11:31:30
Thread-ID: 1520058
3 Beiträge
VBA Summenprodukt mit Teilergebnis und Indirekt
LX

Hallo Zusammen,
ich versuche per VBA einer Variable einen Wert zuzuweisen. Sub Summe Dim Sum as Long Sum = Application.WorksheetFunction.Sumproduct(Subtotal(9,indirect(""A""&ROW(13:20000))*(B13: _ B20000=""X"")) MsgBox Sum End Sub
Die Summe soll gebildet werden für alle gefilterte...
20.11.2017 13:47:41
Thread-ID: 1592462
8 Beiträge
VBA Summenprodukt 2 Kriterien (Monat + Wert)
Wolfgang

Hallo,
ich habe folgende Schwierigkeit die ich leider nicht selbst gelöst bekomme:
Ich arbeite mit zwei Arbeitsblättern (AB) in Excel 2010 wobei AB1 die Ergebnisse und AB2 die Basistabelle darstellt. Die Basistabelle besteht aus 33 Spalten und kann bis zu ca. 100.000 Zeilen enthalten....
05.12.2018 23:32:30
Thread-ID: 1661528
7 Beiträge
VBA Summenprodukt
Bo
Hallo zusammen,
ich habe hier in Sheet1 eine monatlich generierte Liste, die von Monat zu Monat eine unterschiedliche Zeilenanzahl ausweist. Ich möchte hier eine Summenprodukt Formel in VBA auf Sheet2 realisieren, die einmal ein Datum ausliest und daraus den Vormonat ermittelt und dann die entsp...
26.05.2021 14:57:21
Thread-ID: 1832815
5 Beiträge
VBA Summenprodukt mal wieder
Boris
Hallo zusammen,
ich stehe mal wieder auf dem Schlauch. Ich möchte gerne folgende Formel mittels VBA abbilden:
=SUMMENPRODUKT((Sheet1!AF2:AF113=A2)*Sheet1!AD2:AD113) Da die Quelle immer wieder unterschiedlich Länge hat arbeite ich mit
DIM Letztezeile As Long Letztezeile = Range("a" &a...
02.03.2022 13:52:38
Thread-ID: 1873415
3 Beiträge
Anzeige

Infobox / Tutorial

Summenprodukt in VBA nutzen

Schritt-für-Schritt-Anleitung

Um das SUMMENPRODUKT-Verfahren in VBA zu verwenden, folge diesen Schritten:

  1. Öffne den VBA-Editor in Excel (ALT + F11).
  2. Füge ein neues Modul hinzu (Rechtsklick auf "VBAProject" > Einfügen > Modul).
  3. Schreibe eine Subroutine für das SUMMENPRODUKT, ähnlich wie in dem Beispiel unten:
Sub SummenproduktBeispiel()
    Dim wsh1 As Worksheet
    Dim wsh3 As Worksheet
    Dim zeile As Integer
    Dim z As Integer

    Set wsh1 = Sheets("Tab1")
    Set wsh3 = Sheets("Tab2")

    z = 2
    For zeile = 3 To 1000
        If wsh1.Range("A" & zeile) = wsh3.Range("A" & z) Then
            wsh3.Range("D" & z).Value = Application.WorksheetFunction.SumProduct((wsh1.Range("A3:A1000") = wsh3.Range("A" & z)) * (wsh1.Range("B3:B1000") = wsh3.Range("D1")) * (wsh1.Range("C3:C1000")))
            z = z + 1
        End If
    Next zeile
End Sub
  1. Achte darauf, dass die Bereiche korrekt definiert sind, um Fehler zu vermeiden.

Häufige Fehler und Lösungen

  • Laufzeitfehler 1004: Dieser Fehler tritt häufig auf, wenn die Bereiche nicht korrekt angegeben sind. Stelle sicher, dass du die richtigen Zellreferenzen verwendest, z.B. Cells(1000, 1) anstelle von Cells(1, 1000).

  • Falsche Nutzung von Vergleichsoperatoren: In VBA kann die WorksheetFunction.SumProduct nicht direkt mit Vergleichsoperatoren verwendet werden. Nutze stattdessen die Evaluate-Methode oder erstelle deine eigene Funktion, wie im Abschnitt "Alternative Methoden" beschrieben.

Alternative Methoden

Wenn die Verwendung von WorksheetFunction.SumProduct nicht funktioniert, kannst du alternative Ansätze ausprobieren:

  1. Evaluate-Methode: 

    MsgBox Evaluate("=SUMPRODUCT((Tab1!A3:A1000=Tab2!A3)*(Tab1!B3:B1000=Tab2!D1)*(Tab1!C3:C1000))")

  2. Eigene Funktion: Schreibe eine benutzerdefinierte Funktion (UDF), um die Funktionalität von SUMPRODUCT anzupassen. Beispiel:

Function ProduktSumme(ByVal DFeld1 As Range, Optional ByVal DFeld2 As Range) As Variant
    If IsMissing(DFeld2) Then
        ProduktSumme = Application.WorksheetFunction.SumProduct(DFeld1)
    Else
        ProduktSumme = Application.WorksheetFunction.SumProduct(DFeld1, DFeld2)
    End If
End Function

Praktische Beispiele

Hier sind einige praktische Beispiele für die Verwendung von SUMMENPRODUKT in VBA:

  1. Einfaches Beispiel: Berechne die Summe der Produkte zweier Bereiche:

    MsgBox Application.WorksheetFunction.SumProduct(Range("A1:A6"), Range("B1:B6"))

  2. Mit Bedingungen: Nutze SUMMENPRODUKT für eine bedingte Berechnung:

    MsgBox Application.WorksheetFunction.SumProduct((Range("A1:A6") = 1) * (Range("B1:B6")))

Tipps für Profis

  • Nutze die ScreenUpdating-Eigenschaft, um die Leistung deines VBA-Codes zu verbessern:

    Application.ScreenUpdating = False
' Code hier
Application.ScreenUpdating = True

  • Teste deine Formeln zuerst in Excel selbst, bevor du sie in VBA einfügst, um sicherzustellen, dass sie korrekt funktionieren.

FAQ: Häufige Fragen

1. Warum funktioniert die WorksheetFunction.SumProduct nicht wie erwartet?
Die WorksheetFunction.SumProduct kann keine Vergleichsoperatoren direkt verwenden, was zu Fehlern führen kann. Überlege, ob du die Evaluate-Methode nutzen kannst.

2. Gibt es eine Begrenzung für die Anzahl der Zeilen in Excel?
Ja, in Excel 2003 und älteren Versionen gibt es eine Begrenzung von 65.536 Zeilen, während Excel 2007 und spätere Versionen 1.048.576 Zeilen unterstützen. Achte darauf, dass deine Bereiche entsprechend definiert sind.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige