I ran the code in the example spreadsheet I uploaded and it worked great. However, when I ran the macro in the actual report, it did not work. Since I do not understand the code shared, I cannot say where the issue is and can only guess it is because each of the departments in reality contains more than 10 inventory items each. The format in the example is identical to the actual sheet except that there are many more rows than in the example I uploaded.

So my question now is how can this code be modified to take into account that the row count will often change from month to month as items are either added to inventory or removed?

Dim lr As Long, i As Long, adr As String
lr = Cells(Rows.Count, 2).End(xlUp).Row + 1
For i = 15 To lr Step 12
    Cells(i, 6).FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    Cells(i, 9).FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    Cells(i + 1, 11).Resize(, 2).Clear
    adr = adr & "," & Cells(i, 6).Address(0, 0)
Next
With Cells(i - 10, 6)
    .Formula = "=SUM(" & Mid(adr, 2) & ")"
    .Copy .Offset(, 3)
End With
End Sub
The formula above appears to have relative reference to the cells and will only sum a column consisting of just 10 cells.