noch ne VBA Lösung
24.01.2019 16:26:49
UweD
Hallo
so?
Sub dgdgdfd()
Dim TB1, TB2, Z1 As Integer, LR1 As Long, LR2 As Long
Set TB1 = Sheets("Tabelle1")
Z1 = 3 'erste Zeile mit Daten
'Blatt kopieren
TB1.Copy after:=Sheets(Sheets.Count)
Set TB2 = ActiveSheet
LR1 = TB1.Cells(TB1.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte
With TB2
'Duplikate raus
.Range("$A:$G").RemoveDuplicates Columns:=Array(4, 5, 7), Header:=xlYes
'Formel setzen
LR2 = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte
With .Cells(Z1, 1).Resize(LR2 - Z1 + 1, 1)
.FormulaR1C1 = _
"=SUMPRODUCT((" & TB1.Name & "!R" & Z1 & "C4:R" & LR1 & "C4=RC[3])" & _
"*(" & TB1.Name & "!R" & Z1 & "C5:R" & LR1 & "C5=RC[4])" & _
"*(" & TB1.Name & "!R" & Z1 & "C7:R" & LR1 & "C7=RC[6])" & _
"*(" & TB1.Name & "!R" & Z1 & "C1:R" & LR1 & "C1))"
.Value = .Value
End With
'Summe ergänzen
With .Cells(1, 1).Offset(LR2 + 2, 0)
.FormulaR1C1 = "=SUM(R[-" & (LR2 - Z1 + 3) & "]C:R[-1]C)"
.Value = .Value
End With
'ggf Spalten löschen
'.Columns(2).Resize(, 2).Delete xlLeft
End With
End Sub
Verwendet wird die Formel
A3: =SUMMENPRODUKT((Tabelle1!$D$3:$D$153=D3)*(Tabelle1!$E$3:$E$153=E3)*(Tabelle1!$G$3:$G$153=G3) _
*(Tabelle1!$A$3:$A$153))
Wobei die Zeilenzahlen ermittelt werden
LG UweD