I have a table with data that looks like this:
And I need to get those results into another table (on a different tab) that looks like this:
So I am currently using a basic Sumifs Function via VBA but it is not very efficient to write the same code for ALL columns and just changing the last Criteria (Criteria6)...
'-------------
' RETAIL
'-------------
'assign the range of cells
lr2 = WS5.Range("A" & Rows.Count).End(xlUp).row
Set rngSum1 = WS5.Range("H2:H" & lr2)
Set rngCriteria5 = WS5.Range("A2:A" & lr2)
'Criteria5 = WS4.Cells(n, 10)
Set rngCriteria6 = WS5.Range("D2:D" & lr2)
'Criteria6 = WS4.Cells("T3")
'use the ranges in the formula
Dim m As Long
Dim lr3 As Integer
lr3 = WS4.Range("J" & Rows.Count).End(xlUp).row
For m = 4 To lr3
WS4.Cells(m, 20).Value = Application.WorksheetFunction.SumIfs(rngSum1, rngCriteria5, WS4.Cells(m, 10), rngCriteria6, WS4.Range("T3"))
Next m
'release the range object
Set rngCriteria5 = Nothing
Set rngCriteria6 = Nothing
'-------------
' INDUSTRY
'-------------
'assign the range of cells
lr2 = WS5.Range("A" & Rows.Count).End(xlUp).row
Set rngSum1 = WS5.Range("H2:H" & lr2)
Set rngCriteria5 = WS5.Range("A2:A" & lr2)
'Criteria5 = WS4.Cells(n, 10)
Set rngCriteria6 = WS5.Range("D2:D" & lr2)
'Criteria6 = WS4.Cells("U3")
'use the ranges in the formula
Dim m As Long
Dim lr3 As Integer
lr3 = WS4.Range("J" & Rows.Count).End(xlUp).row
For m = 4 To lr3
WS4.Cells(m, 21).Value = Application.WorksheetFunction.SumIfs(rngSum1, rngCriteria5, WS4.Cells(m, 10), rngCriteria6, WS4.Range("U3"))
Next m
'release the range object
Set rngCriteria5 = Nothing
Set rngCriteria6 = Nothing
Is there a way to put this into an array to improve efficiency (and therefore time)?
Thanks a lot in advance!
E.

