Best function to sum quantities associated with text values?

You can highlight the column of Item descriptions and use the Advanced Filter to create a list of [x] Unique Values only copied to another location.

Once you have that new unique list, the function you want to use to collect all the totals in one column based on matching a single value to an adjacent column is SUMIF().

=SUMIF(MatchRange, MatchValue, SumRange)

Hello golfretailer,

The attached workbook has a button to run the macro below. This will create a summary of the stock line items on a separate sheet. Each time the macro is run, the previous summary data is deleted. The summary is sort in ascending order. Click the button and your done.

Sub Summarize()

  Dim Cell As Range
  Dim DstWks As Worksheet
  Dim QtyData() As Variant
  Dim R As Long
  Dim Rng As Range
  Dim RngEnd As Range
  Dim SrcWks As Worksheet
  Dim StockItems As Object
  Dim StockItem As Variant
  
    Set SrcWks = ActiveSheet
    Set DstWks = Worksheets("Summary")
    
    Set StockItems = CreateObject("Scripting.Dictionary")
    StockItems.CompareMode = vbTextCompare
    
      Set Rng = SrcWks.Range("A2")
      Set RngEnd = SrcWks.Cells(Rows.Count, Rng.Column).End(xlUp)
      If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = SrcWks.Range(Rng, RngEnd)
      
        For Each Cell In Rng
          StockItem = Trim(Cell.Value)
          If StockItem <> "" Then
            If Not StockItems.Exists(StockItem) Then
              'Add StockItem - First time
               ReDim QtyData(1)
                 QtyData(0) = Cell.Offset(0, 1).Value
                 QtyData(1) = Cell.Offset(0, 2).Value
               StockItems.Add StockItem, QtyData
            Else
              'Accummulate Quantities
               QtyData = StockItems(StockItem)
                 QtyData(0) = QtyData(0) + Cell.Offset(0, 1).Value
                 QtyData(1) = QtyData(1) + Cell.Offset(0, 2).Value
               StockItems(StockItem) = QtyData
            End If
          End If
        Next Cell
                
       'Clear the Summary sheet except for the header row
        Set Rng = DstWks.Range("A2:C2")
        Set RngEnd = DstWks.Cells(Rows.Count, Rng.Column).End(xlUp)
          If RngEnd.Row >= Rng.Row Then Set Rng = DstWks.Range(Rng, RngEnd)
        Rng.ClearContents
        
       'Starting row on Summary sheet
        R = Rng.Row
        
        For Each StockItem In StockItems.Keys
         'List StockITem and Quantities on the Sunnary sheet
          QtyData = StockItems(StockItem)
            DstWks.Cells(R, "A") = StockItem
            DstWks.Cells(R, "B").Resize(1, 2).Value = QtyData
          R = R + 1
        Next StockItem
    
   'Sort the Summary sheet in ascending order
    Rng.Sort Key1:=Rng.Cells(1, 1), Order1:=xlAscending, Header:=xlNo, _
             MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
            
   'Release the object and memory
    Set StockItems = Nothing
    
End Sub

Alternatively - use a Pivot Table (see the link in sig. for general intro.)