|
|
A |
B |
C |
|
1 |
Datum |
Produkt |
Anzahl |
|
2 |
01.08.2019 |
P_AB |
3 |
|
3 |
01.08.2019 |
P_AB |
4 |
|
4 |
01.08.2019 |
P_XY |
2 |
|
5 |
01.08.2019 |
P_XY |
3 |
Der obigen Beispieltabelle folgend:
Dim rng As Excel.Range
With Range("A2:C5")
'Sum_AB
Set rng = .Offset(0 + .Rows.Count, 2).Resize(1, 1)
rng.FormulaR1C1 = "=SUMIF(" & .Columns(2).Address(ReferenceStyle:=xlR1C1) & ",""*_AB*""," & .Columns(3).Address(ReferenceStyle:=xlR1C1) & ")"
' rng.Offset(, -1).Value = "Sum_AB"
'Sum_XY
Set rng = .Offset(1 + .Rows.Count, 2).Resize(1, 1)
rng.FormulaR1C1 = "=SUMIF(" & .Columns(2).Address(ReferenceStyle:=xlR1C1) & ",""*_XY*""," & .Columns(3).Address(ReferenceStyle:=xlR1C1) & ")"
' rng.Offset(, -1).Value = "Sum_XY"
'Gesamt
Set rng = .Offset(2 + .Rows.Count, 2).Resize(1, 1)
rng.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
' rng.Offset(, -1).Value = "Gesamt"
End With
|