Translate an Excel Sumifs function to a VBA statement

[mannyghee]

Below is a cell formula that I have on a worksheet that I wanted to translate as VBA macro statement:

=(SUMIFS(RawData!$J:$J,RawData!$D:$D,C$4,RawData!$H:$H,">7/24/2015 08:00:00 PM",RawData!$H:$H,"<7/25/2015 12:16:00 PM"))

where: Each row in the data table is a unique dataset based on the date in the week
Column J in the data table contains the values to be summed
Column D in the data table has the values that describes type of data it is for that cell (sum only Type A product which is identified on Cell C$4 on the Summary Page)
Column H in the data table has the timestamps on when the event occurred that the particular product was processed. On the example above, the time range is looking for any event that happened between 08:00 PM 7/24/15 and 12:15 PM 7/25/15.

In short, the statement above is asking the sum of the values in Column J if it was a Type A product (Column D) processed after 08:00 PM on 7/24/15 AND before 12:15 PM on 7/25/15 (Column H). The cell formulas work in the worksheet. There are 5 different product types that I have to type in manually for the whole week so I need to programmatically calculate it where the dates will be stored variables depending on the week number.

Any help will be appreciated.

 

[RickXL]

Hi,

I am not quite sure whether I have understood your requirements properly.

I have made some assumptions:

1. You don't need to "fill down" the calculation;
2. The date will be in cell A1 on the Summary sheet;
3. The summary sheet will be called "Summary";
4. The result will appear in cell A2 on the Summary sheet;
5. The SUM will need to cope with floating point numbers;
6. The times will be fixed but the date will come from the spreadsheet.

Sub SumIfs()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim arrD As Variant, arrH As Variant, arrJ As Variant
    Dim lr As Long, i As Long
    Dim dt1 As Date, dt2 As Date
    Dim Sum As Double
    
    Set ws1 = ThisWorkbook.Worksheets("Summary")
    Set ws2 = ThisWorkbook.Worksheets("RawData")
    
    dt1 = ws1.Range("A1").Value + TimeValue("08:00:00 PM")
    dt2 = ws1.Range("A1").Value + 1 + TimeValue("12:15:00 AM")
    
    With ws2
        lr = .Cells(.Rows.Count, "J").End(xlUp).Row
        arrD = .Range("D2:D" & lr)
        arrH = .Range("H2:H" & lr)
        arrJ = .Range("J2:J" & lr)
        
        For i = 1 To UBound(arrJ)
            If arrD(i, 1) = ws1.Range("C4").Value Then
                If arrH(i, 1) >= dt1 And arrH(i, 1) <= dt2 Then Sum = Sum + arrJ(i, 1)
            End If
        Next
    End With
    
    ws1.Range("A2").Value = Sum

End Sub

The macro reads the columns required for thethe SUMIFS calculation into arrays (arrD, arrH and arrJ).
One IF Statement selects array elements with the correct Product Type and a second IF Statement carries out the DateTime selection.