combining Sumif with Vlookup on Excel VBA

[jeck32]

Hi Gurus,

I would need your help on building a code that combines SUMIF and Vlookup on Excel VBA.

I have a Table named StoreRecords, with one column for Store Locations and another column for Daily Sales.

Now, what I'm trying to accomplish is to create a userform with combo box that will return unique store names and one textbox or label text that will display its total sales for the month

ex.
StoreRecords
Num# Store Locations Daily Sales
1 UK $100
2 USA $150
3 UK $130
4 UAE $160

so, if I select UK from the dropdownbox,
it should return a value of $230

 

[DanteAmor]

Try this with a Label.
Note: Set the name of the sheet

Private Sub ComboBox1_Change()
  Label1.Caption = ""
  With ComboBox1
    If .ListIndex > -1 Then Label1.Caption = .List(.ListIndex, 1)
  End With
End Sub

Private Sub UserForm_Activate()
  Dim dic As Object
  Dim c As Range
  Set dic = CreateObject("Scripting.Dictionary")
  With Sheets("StoreRecords")     'sheet name
    For Each c In .Range("B2", .Range("B" & Rows.Count).End(3))
      If c.Value <> "" Then
        dic(c.Value) = dic(c.Value) + c.Offset(, 1).Value
      End If
    Next
  End With
  ComboBox1.List = Application.Transpose(Array(dic.keys, dic.items))
  Label1.Caption = ""
End Sub

Example:

varios 29sep2022.xlsm
	A	B	C
1	Num	Store Locations	Daily Sales
2	1	UK	100
3	2	USA	150
4	3	UK	130
5	4	UAE	160
StoreRecords

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jeck32]

Try this with a Label.
Note: Set the name of the sheet

Private Sub ComboBox1_Change()
  Label1.Caption = ""
  With ComboBox1
    If .ListIndex > -1 Then Label1.Caption = .List(.ListIndex, 1)
  End With
End Sub

Private Sub UserForm_Activate()
  Dim dic As Object
  Dim c As Range
  Set dic = CreateObject("Scripting.Dictionary")
  With Sheets("StoreRecords")     'sheet name
    For Each c In .Range("B2", .Range("B" & Rows.Count).End(3))
      If c.Value <> "" Then
        dic(c.Value) = dic(c.Value) + c.Offset(, 1).Value
      End If
    Next
  End With
  ComboBox1.List = Application.Transpose(Array(dic.keys, dic.items))
  Label1.Caption = ""
End Sub

Example:

varios 29sep2022.xlsm
	A	B	C
1	Num	Store Locations	Daily Sales
2	1	UK	100
3	2	USA	150
4	3	UK	130
5	4	UAE	160
StoreRecords

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

thank you for the quick response. much appreciated.

just a followup question. if my daily sales column is on a different column, say Column F, which part of the code should I change?

 

[DanteAmor]

This line:

dic(c.Value) = dic(c.Value) + c.Offset(, 4)

 

[jeck32]

Great. Thanks alot!

 

[DanteAmor]

Im glad to help you. Thanks for the feedback

 

[jeck32]

Im glad to help you. Thanks for the feedback

Hi, i have a follow up noob question again if you dont mind.

as I have not considered a case in which i only have ONE store location.
which part of the code would i have to adjust to accept the total sales for one store?