Sumproduct on another sheet

Question

I'm new to vba, I want to know how to implement a formula (sumproduct) on excel to vba. The formula gets value from other sheets on the workbook. The formula is:

=((SUMPRODUCT(-(Details!$C$7:$C$1182=A3),-(Details!$E$7:$E$1182=B3), -(Details!$S$7:$S$1182="Delivered"), -(Details!$G$7:$G$1182=C3), Details!$N$7:$N$1182)))

Here's my code;

Private Sub ATO()

Dim ws1 As Worksheet
Dim wb1 As Workbook
Dim last As Long
Dim i As Integer

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Summary")

last = ws1.Cells(Rows.Count, "A").End(xlUp).Row

ws1.Range("I2:I" & last) = Application.SumProduct(?)

End Sub

Is my code on the right track? Any response would be appreciated.

Phonebox Phonebox · Accepted Answer · 2015-07-11T15:45:34

Yes you are on the right track however if you call an excel function in vba you should use Application.WorksheetFunction For example for your problem Application.WorksheetFunction.sumif() There is one exception, keep in mind that if the same function exists in vba then you can not use the excel version with WorksheetFunction. Therefore if you don't find a function in worksheetfunction then try it without it. Good luck! If something does not work comment and I will try to help!

Sumproduct on another sheet

1 Answers