vlookup and sumif (sum profit per item)-CodePudding

I have a dataset with 100k lines and i would like to sum up the profit per unique item. the table looks like this:

Is there an option to combine vlookup and sumif in a way to extract all the profit per item?

CodePudding user response：

Using a Dictinary will easily solve your problem. Please, copy the next code in a standard module and run it. It will return in shDest worksheet. In the example code it returns in the next sheet, after the active one. But instead Set shDest = sh.Next you may Set any sheet:

Sub TotalProfit()
 Dim sh As Worksheet, shDest As Worksheet, lastRow As Long, arr, i As Long, dict As New Scripting.Dictionary
 
 Set sh = ActiveSheet 'use here the sheet you need, even not active
 Set shDest = sh.Next
 lastRow = sh.Range("C" & sh.rows.count).End(xlUp).Row
 arr = sh.Range("C2:N" & lastRow).Value2
 For i = 1 To UBound(arr)
    dict(arr(i, 1)) = dict(arr(i, 1))   arr(i, 12)
 Next i
 shDest.Range("A1:B1").value = Array("ItemType", "Total Profit")
 With shDest.Range("A2")
    .Resize(dict.count, 1).value = Application.Transpose(dict.Keys)
    .Offset(0, 1).Resize(dict.count, 1).value = Application.Transpose(dict.Items)
 End With
End Sub

Application.Transpose() has some limitations, but I do not think your reange will exceed them. If so, I can easily adapt the code to build a 2D array based on the Dictionary keys/items...

CodePudding user response：

Start with the function UNIQUE, which list the items with no duplicates. Then you just need to SUMIF on the new list.

The formula for the list is: =UNIQUE(Table13[Item Type])

Since you are working with tables, you can refer to the column by its name: "Item Type"--which is in the Table13 of my file. I mean, "Table13[Item Type]" is your range.
The formula "spill over" the cells, you only need to write it in one cell to see the whole list.

Now, you just need to sum each item. My unique list of items is on column "G". Therefore my formula for the first item is: =SUMIF(Table13[Item Type],G5,Table13[Total Profit])

The 1st argument is the list of items on the original table.
The 2nd is the value to compare with the range (compare with the 1st argument).
The 3rd argument is where the values are located.

Drag down the formula and you have your new table!

The final result is the following.

I am assuming you are using Excel 365. If you have an older Excel version, you cannot use "unique", so we should go to a bit more complex solution.