Uploaded January 2020 | Updated September 2026, 2 weeks ago

---

How to create a super simple inventory automation system quickly and easily using the SUMIFS function with VBA.
Here's the complete VBA code:
Option Explicit

Sub myStock()
Dim prodID As String
Dim stockQty As Long
prodID = InputBox("Enter product ID to check quantity available.")

Dim rA As Range, rB As Range, rC As Range

With Worksheets("Sheet1")
Set rB = .Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rA = .Range("A2", Range("A" & Rows.Count).End(xlUp))
Set rC = .Range("C2", Range("C" & Rows.Count).End(xlUp))
End With

stockQty = Application.WorksheetFunction.SumIfs( _
rC, rA, prodID, rB, "Purchase") - Application.WorksheetFunction.SumIfs( _
rC, rA, prodID, rB, "Sale") + Application.WorksheetFunction.SumIfs( _
rC, rA, prodID, rB, "Return")

MsgBox "The quantity of item " & prodID & " available is " & stockQty
Range("F3") = stockQty
End Sub