Microsoft Excel

Herbers Excel/VBA-Archiv

VBA Sumif Jahreszahl gleich


Betrifft: VBA Sumif Jahreszahl gleich
von: Berg
Geschrieben am: 11.07.2018 14:51:46

Hallo allerseits,

folgendes Problem. In Spalte B werden über eine Userform ein Datum und in Spalte C bzw. D zwei zugehörige Zahlenwerte in Tabelle3 eingetragen. Nun möchte ich in Tabelle 2 diese Werte jeweils Summieren wenn die Jahreszahlen identisch sind. Ich weiß das ganze lässt sich über eine Pivottabelle lösen, aber ich muss das ganze via VBA-Code bewerkstelligen:

D.h. aus…

Tabelle3:
Spalte B Spalte C Spalte D
01.01.2018 1 1
01.02.2019 1 1
01.03.2019 2 2
01.04.2020 1 1
02.05.2020 1 1
… … ..

…Mach…

Tabelle2:
Spalte B Spalte C Spalte D
2018 1 1
2019 3 3
2020 2 2

Um eine Hilfe bin ich sehr dankbar. Vorab besten Dank

Gruß

  

Betrifft: AW: VBA Sumif Jahreszahl gleich
von: Rob
Geschrieben am: 11.07.2018 15:59:24

Für Spalte C (D kannst du entsprechend anpassen):

Sub CountIfYear()

Dim r As Range
Dim Sum2018, Sum2019, Sum2020 As Integer

Sum2018 = 0
Sum2019 = 0
Sum2020 = 0

For Each r In Intersect(Tabelle1.UsedRange, Range("B:B"))
    Select Case Right(r.Offset(0, -1), 4)
    
        Case Is = "2018"
        Sum2018 = Sum2018 + 1
        
        Case Is = "2019"
        Sum2019 = Sum2019 + 1
        
        Case Is = "2020"
        Sum2020 = Sum2020 + 1
                 
    End Select
Next r

With Tabelle2
    .Range("B2") = Sum2018
    .Range("B3") = Sum2019
    .Range("B4") = Sum2020
End With

End Sub




  

Betrifft: AW: VBA Sumif Jahreszahl gleich
von: Rob
Geschrieben am: 11.07.2018 16:02:00

Sorry, kleiner Fehler. Anstelle Sum2018 + 1 muss es heißen Sum2018 + r


  

Betrifft: Summenprodukt()
von: ChrisL
Geschrieben am: 11.07.2018 16:02:45

Hi

Nur mal das Formelthema...

Sub t()
'=SUMMENPRODUKT((JAHR(B1:B10)=2019)*(C1:C10))
MsgBox Evaluate("SUMPRODUCT((YEAR(B1:B10)=2019)*(C1:C10))")
End Sub
cu
Chris


  

Betrifft: AW: Summenprodukt()
von: Rob
Geschrieben am: 11.07.2018 19:34:49

Ist eine in VBA eingebettete Excel-Funktion streng genommen noch VBA? Weil wozu dann noch ein Makro schreiben, wenn Du das direkt über das Arbeitsblatt lösen kannst?


  

Betrifft: AW: Summenprodukt()
von: ChrisL
Geschrieben am: 12.07.2018 08:54:54

Hi Rob

Die Sinnfrage stelle ich mir tatsächlich.

Aber ich wollte hauptsächlich darauf hinweisen, dass die im Titel erwähnte Funktion hier nicht funktioniert.

WorksheetFunction.SumIf =SUMMEWENN
WorksheetFunction.CountIf = ZÄHLENWENN
WorksheetFunction.SumProduct = SUMMENPRODUKT

Es ist nicht verboten, Tabellenfunktionen auch in VBA zu verwenden. Meist kommt man sogar auf eine schlankere Lösung, wenn man sich vorher überlegt, welches die geeignete Formel wäre und dann eine Transformation nach VBA vornimmt.

z.B.

Sub t()
Dim i As Long

For i = 2017 To 2020
    Cells(i - 2016, 4) = Evaluate("SUMPRODUCT((YEAR(B1:B10)=" & i & ")*(C1:C10))")
next i
End Sub
cu
Chris


  

Betrifft: AW: Summenprodukt()
von: Berg
Geschrieben am: 12.07.2018 09:16:20

Hallo Rob und ChrisL,

eine wirklich sehr schlanke und elegante Lösung. Genau das was ich gesucht habe. Vielen vielen Dank!

Viele Grüße
Berg


  

Betrifft: AW: Summenprodukt()
von: Rob
Geschrieben am: 12.07.2018 09:22:23

Das einzige Problem bei der Lösung ist, dass sie nicht dynamisch ist. Wenn sich Deine Tabellen aber nicht in der Zeilenanzahl verändern, kann man natürlich damit leben.


  

Betrifft: AW: Summenprodukt()
von: ChrisL
Geschrieben am: 12.07.2018 18:12:07

Hi

Die Sinnfrage bleibt, aber Dynamik kannst du haben ;)

Sub t()
Call MeinProgramm(2017, 2020)
End Sub

Private Sub MeinProgramm(intStart As Integer, intEnde As Integer)
 Dim i As Integer
 
 For i = intStart To intEnde
   Cells(i - intStart + 1, 4) = _
   Evaluate("SUMPRODUCT((YEAR(Bereichsname1)=" & i & ")*(Bereichsname2))")
 next i
 End Sub

cu
Chris


  

Betrifft: AW: Summenprodukt()
von: Berg
Geschrieben am: 13.07.2018 11:22:01

Hallo Chris,

super, jetzt funktionierts sogar Dynamisch :) Danke nochmal fürs Nachhaken. Jetzt hätte ich nochmal eine Sache. Wenn ein Jahr in der Ausgangsliste die gruppiert werden soll fehlt dann funktioniert es nicht.

2018
2019
2021

Kann man noch was einbauen, sodass er alle Jahre aufschlüsselt und wenn ein Jahr fehlt (hier z.B. 2020) dann schreibt er den Wert "0". Geht das?

Dankeschöööön!!!


  

Betrifft: AW: Summenprodukt()
von: ChrisL
Geschrieben am: 13.07.2018 12:42:37

Hi

Ist doch bereits so. Es werden alle Jahre von/bis durchlaufen und ggf. 0 ausgegeben.

cu
Chris


  

Betrifft: AW: Summenprodukt()
von: Berg
Geschrieben am: 13.07.2018 13:39:11

Hi Chris,

danke nochmal. Ich erhalte immer #WERT! in den Zellen. Ich hab allerdings deinen Code mit Tabelle2.cells und tabelle3.evaluate ergänzt. Darf man das vll nicht?

Sub Schaltfläche2_Klicken()
Call MeinProgramm(2017, 2022)
End Sub

Private Sub MeinProgramm(intStart As Integer, intEnde As Integer)
 Dim i As Integer
 
 For i = intStart To intEnde
 'Ich Starte in Zeile 8 wegen den Überschriften
   Tabelle2.Cells(i - intStart + 8, 4) = _
   Tabelle3.Evaluate("SUMPRODUCT((YEAR(C8:C15)=" & i & ")*(E8:E15))")
 Next i
 End Sub
Gruß


  

Betrifft: AW: Summenprodukt()
von: ChrisL
Geschrieben am: 13.07.2018 14:45:24

Hi

ungetestet...

Private Sub MeinProgramm(intStart As Integer, intEnde As Integer)
 Dim i As Integer
 
 For i = intStart To intEnde
 'Ich Starte in Zeile 8 wegen den Überschriften
   Tabelle2.Cells(i - intStart + 8, 4) = _
   Evaluate("SUMPRODUCT((YEAR(Tabell3!C8:C15)=" & i & ")*(Tabelle3!E8:E15))")
 Next i
 End Sub
cu
Chris


  

Betrifft: AW: Summenprodukt()
von: Berg
Geschrieben am: 13.07.2018 15:01:11

Hi,

jetzt weis ich woran es liegt. Ein Formatproblem mit dem Datum! Aber völlig absurd. Ich gebe meine Daten (Datum und Wert) über eine Userform ein. Die speichere ich formatiert wie folgt:

Tabelle3.Cells(lZeile, 3).Value = CDate(TextBox2)
Tabelle3.Cells(lZeile, 3) = Format(TextBox2, "dd.mm.yyyy")
Wenn ich dann in die Zelle klicke sind die Werte als Datum bzw. Zahl formatiert hinterlegt.

Wenn ich dann dein Makro laufen lassen schreibt er #Wert!
Wenn ich aber das Datum direkt in die Zelle schreibe (ohne Userform) funktionert dein Makro genau so wie es soll. Häääää?


  

Betrifft: AW: Summenprodukt()
von: Berg
Geschrieben am: 13.07.2018 15:26:48

Ja, es war ein Formatfehler vom Datum.

So klappts:

Tabelle3.Cells(lZeile, 3).Value = CDate(TextBox2)
Tabelle3.Cells(lZeile, 3).NumberFormat = "dd.mm.yyyy"

Chris, ein fettes Merci für deine Hilfe. Echt super!!!

VG