First tried to output manipulated values one by one, it was slow, then tried to output manipulated values into another array and then into an Excel range, slow as well
Sub Calc()
Dim colISO As New Collection
Dim arrRng()
Dim arrISO()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
FinalColumn = Cells(4, 5).End(xlToRight).Column
'Produce 5 columns for grouping values
For c = 5 To FinalColumn
On Error Resume Next
colISO.Add Cells(4, c), CStr(Cells(4, c))
On Error GoTo 0
Next c
'Create an array
ReDim arrRng(3 To FinalRow, 5 To FinalColumn)
ReDim arrISO(7 To FinalRow, 1 To colISO.Count)
For r = 3 To FinalRow
For c = 5 To FinalColumn
arrRng(r, c) = Cells(r, c)
Next c
Next r
'Group 55 columns into 5 (this is fast)
SumCost = 0
For r = 7 To FinalRow
For i = 1 To colISO.Count
SumCost = 0
Cells(6, i) = colISO(i)
For c = 5 To FinalColumn
If arrRng(4, c) = colISO(i) Then
SumCost = SumCost + arrRng(r, c)
End If
Next c
'Tried to output summed values one by one (extremely slow)
'Cells(r, i) = SumCost
'Put summed values into another array for further output into an Excel range (slow as well)
arrISO(r, i) = SumCost
Next i
Next r
Cells(7, 1).Resize(FinalRow, colISO.Count) = arrISO
End Sub