Kapat

Sum Unique Values In Excel With VBA Codes

excel  listbox context menu VBA Sorting & Summing
Unique Values

In this template, we have spreadsheet with 9 columns. The spreadsheet in sheet includes products that purchased from vendors , product unit prices and total amounts.The vendors are listed in column B and the purchased product amounts in column I based on dates.

We will follow 2 different ways for two sheets that contained same spreadsheet to find the amount of purchased product from a vendor (process of summing unique values).

Solution 1 (for Example1 sheet ) sum unique items in excel With the sum_unique_items procedure firstly, vendor names in column B are listed as unique values in column K using VBA AdvancedFilter method. excel advancedfilter unique values
Columns("B:B").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns( "B:B"), CopyToRange:=Range("'Example1'!K1"), Unique:=True

To find the total amount for each individual value(vendor) in column K, the SUMIF formula is entered using a loop into the cells in column L corresponding to the value .excel enter sumif formula with vba code
For i = 2 To Cells(Rows.Count, 11).End(xlUp).Row
Cells(i, "L").FormulaR1C1 = "=SUMIF(C[-10],RC[-1],C[-3])"
Next i

sum unique values in excel
Result : excel sum unique values

Solution 2 (for Example2 sheet ) excel vba subtotal The range A: I is sorted alphabetically as ascending based on Column B. excel vba sort alphabetically
Range("A2:I" & Cells(Rows.Count, 1).End(xlUp).Row).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Subtotals are created for the used range on the Example2 sheet with the VBA Subtotal method. The table is grouped by the second field and subtotals are added to the field 9 (Column I).

They are colored using a loop to highlight cells that contained subtotals : excel vba highlight cell
For Each rng In Range("I2:I" & Cells(Rows.Count, 9).End(xlUp).Row)
If rng.HasFormula Then
With rng
.Interior.ColorIndex = 37
.Font.Bold = True
End With
End If
Next

Result : excel sum unique values
excel vba subtotal grouping

📥 Download sample workbook