sum without duplicate calculation

[kelvin_9]

Hi All,

i need a vba formula to sum the order number without duplicate calculation from 3(or more) criteria as below

filter sheet1 column d, "pickup"
filter sheet1 column e, (today date from a date+time format), match sheet2 A52
filter sheet1 column Y, match sheet2 E50

Sub Macro1()
'
' Macro1 Macro
'136

'

    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("data")
    Set ws2 = Worksheets("Sheet2")
    
    If ws1.AutoFilterMode Then ws1.AutoFilter.ShowAllData
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 4, "pickup"
        .AutoFilter 5, 1, 11
        .AutoFilter 25, ws2.Range("E50").Value
    End With

    Sheets("sheet2").Select

  Dim f1, f2, f3
  f1 = "data!$A$2:$A$20000"
  f2 = "data!$E$2:$E$20000"
  f3 = "data!$Y$2:$Y$20000"
  
  With Range("E52")
    .FormulaArray = "=SUM((FREQUENCY(IFERROR(MATCH(x_x,x_x,0),0)*(INT(y_y)=RC1)*(R50C=z_z)," & _
      "IFERROR(MATCH(x_x,x_x,0),0)*(INT(y_y)=RC1)*(R50C=z_z))>0)*1)-1"
    .Replace "x_x", f1
    .Replace "y_y", f2
    .Replace "z_z", f3
  End With

End Sub

thank you very much for your guidance

 

[kelvin_9]

any one can help?