Userform for sum the same values by the same data in previous column

[yoon]

Hye everyone, i am new to this forum and need all of your help asap TT. currently i am student and do my internship for one company and need to improvise the data given by the company. and I already have my userform but i need to sum the total hours and total quantity by the same process number and product name. can you all help me because currently i have no idea and blank already TT.

 

[DanteAmor]

Hi and Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.


I recommend using combobox for the name of the product and for the process number.

Put all the code in the form.

Option Explicit

Private Sub ComboBox1_Change()
  Dim c As Range
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")

  ComboBox2.Clear
  ComboBox2.Value = ""
  TextBox1.Value = ""
  TextBox2.Value = ""
  
  For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
    If c.Value = ComboBox1.Value Then
      dic(c.Offset(, 1).Value) = Empty
    End If
  Next

   ComboBox2.List = dic.keys
End Sub

Sub totals()
  Dim c As Range
  Dim totH As Double, totQ As Double
  Dim cmb2 As Variant
  
  If ComboBox1.ListIndex = -1 Then Exit Sub
  
  With ComboBox2
    If .ListIndex = -1 Then Exit Sub
    
    If IsNumeric(.Value) Then
      cmb2 = Val(.Value)
    Else
      cmb2 = .Value
    End If
  End With
  
  For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
    If c.Value = ComboBox1.Value And c.Offset(, 1).Value = cmb2 Then
      totH = totH + c.Offset(, 2).Value
      totQ = totQ + c.Offset(, 3).Value
    End If
  Next
  TextBox1.Value = totH
  TextBox2.Value = totQ
End Sub

Private Sub ComboBox2_Change()
  Call totals
End Sub

Private Sub UserForm_Activate()
  Dim dic As Object
  Dim c As Range
  
  Set dic = CreateObject("Scripting.Dictionary")
  
  For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
    dic(c.Value) = Empty
  Next
  
  ComboBox1.List = dic.keys
End Sub

I attach my file with the form and the code for you to try.
Select the product, then the number and the totals are calculated automatically.

varios 25jul2023 userform sum.xlsm

drive.google.com

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[yoon]

okays. thank you very much I will try now and if I encountered some problems I will be back to you

 

[DanteAmor]

The "totals" procedure can be simplified this way:

Sub totals()
  Dim c As Range
  Dim totH As Double, totQ As Double
  Dim cmb2 As Variant
  
  If ComboBox1.ListIndex = -1 Then Exit Sub
  If ComboBox2.ListIndex = -1 Then Exit Sub
  
  TextBox1.Value = WorksheetFunction.SumIfs(Range("C:C"), Range("A:A"), ComboBox1.Value, Range("B:B"), ComboBox2.Value)
  TextBox2.Value = WorksheetFunction.SumIfs(Range("D:D"), Range("A:A"), ComboBox1.Value, Range("B:B"), ComboBox2.Value)
End Sub

Im glad to help you. Thanks for the feedback.

 

[yoon]

Hi and Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.


I recommend using combobox for the name of the product and for the process number.
View attachment 95959

Put all the code in the form.

Option Explicit

Private Sub ComboBox1_Change()
  Dim c As Range
  Dim dic As Object
 
  Set dic = CreateObject("Scripting.Dictionary")

  ComboBox2.Clear
  ComboBox2.Value = ""
  TextBox1.Value = ""
  TextBox2.Value = ""
 
  For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
    If c.Value = ComboBox1.Value Then
      dic(c.Offset(, 1).Value) = Empty
    End If
  Next

   ComboBox2.List = dic.keys
End Sub

Sub totals()
  Dim c As Range
  Dim totH As Double, totQ As Double
  Dim cmb2 As Variant
 
  If ComboBox1.ListIndex = -1 Then Exit Sub
 
  With ComboBox2
    If .ListIndex = -1 Then Exit Sub
   
    If IsNumeric(.Value) Then
      cmb2 = Val(.Value)
    Else
      cmb2 = .Value
    End If
  End With
 
  For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
    If c.Value = ComboBox1.Value And c.Offset(, 1).Value = cmb2 Then
      totH = totH + c.Offset(, 2).Value
      totQ = totQ + c.Offset(, 3).Value
    End If
  Next
  TextBox1.Value = totH
  TextBox2.Value = totQ
End Sub

Private Sub ComboBox2_Change()
  Call totals
End Sub

Private Sub UserForm_Activate()
  Dim dic As Object
  Dim c As Range
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
    dic(c.Value) = Empty
  Next
 
  ComboBox1.List = dic.keys
End Sub

I attach my file with the form and the code for you to try.
Select the product, then the number and the totals are calculated automatically.

varios 25jul2023 userform sum.xlsm

drive.google.com

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

hello sorry, I already got your reply and I also already try it but I cannot see in my worksheet after I fill all the userform and it back to the product name. can you help me on this problem?

 

[DanteAmor]

Try with my file or with the following data.

varios 25jul2023 userform sum.xlsm
	A	B	C	D
1	PRODUCT	PROCESS	TOTAL HOURS	TOTAL QTY
2	Pro1	11	76	32
3	Pro1	11	33	59
4	Pro1	11	23	92
5	Pro1	12	30	37
6	Pro1	12	27	41
7	Pro1	13	24	45
8	Pro2	21	50	79
9	Pro2	22	93	80
10	Pro2	22	16	64
11	Pro3	31	43	16
Sheet1

My code doesn't filter, doesn't delete, doesn't hide rows. I don't understand why you don't see the data in your sheet. Do you have another macro running in your sheet?



In the example below you can see that I select the data and calculate the totals, and the data is still on the sheet.

 

[yoon]

Before this I have one macro but I already delete it and I try it back and it still cannot see the data in the sheet

 

[yoon]

btw you file is for view only I cannot try it

 

[DanteAmor]

Try in a new workbook, without the other macros.

Copy my data and try again.

Or try my file that I put in the link in post #2. You must save the file on your computer and save it with another name so that you can edit it.

I can't help you further if I don't know the other macros and what they are doing.

That's why I ask you to work only with the userform code to test the functionality.

Later you can add other macros. For now just try the code I gave you.

 

[DanteAmor]

We start again. Copy the following data into a new workbook.

varios 25jul2023 userform sum.xlsm
	A	B	C	D
1	PRODUCT	PROCESS	TOTAL HOURS	TOTAL QTY
2	Pro1	11	76	32
3	Pro1	11	33	59
4	Pro1	11	23	92
5	Pro1	12	30	37
6	Pro1	12	27	41
7	Pro1	13	24	45
8	Pro2	21	50	79
9	Pro2	22	93	80
10	Pro2	22	16	64
11	Pro3	31	43	16
Sheet1

In that book, you create the userform with 2 combobox and 2 textbox and put all the code and tests there.

Private Sub ComboBox1_Change()
  Dim c As Range
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")

  ComboBox2.Clear
  ComboBox2.Value = ""
  TextBox1.Value = ""
  TextBox2.Value = ""
  
  For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
    If c.Value = ComboBox1.Value Then
      dic(c.Offset(, 1).Value) = Empty
    End If
  Next

   ComboBox2.List = dic.keys
End Sub

Sub totals()
  Dim c As Range
  Dim totH As Double, totQ As Double
  Dim cmb2 As Variant
  
  If ComboBox1.ListIndex = -1 Then Exit Sub
  If ComboBox2.ListIndex = -1 Then Exit Sub
  
  TextBox1.Value = WorksheetFunction.SumIfs(Range("C:C"), Range("A:A"), ComboBox1.Value, Range("B:B"), ComboBox2.Value)
  TextBox2.Value = WorksheetFunction.SumIfs(Range("D:D"), Range("A:A"), ComboBox1.Value, Range("B:B"), ComboBox2.Value)
End Sub

Private Sub ComboBox2_Change()
  Call totals
End Sub

Private Sub UserForm_Activate()
  Dim dic As Object
  Dim c As Range
  
  Set dic = CreateObject("Scripting.Dictionary")
  
  For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
    dic(c.Value) = Empty
  Next
  
  ComboBox1.List = dic.keys
End Sub

I have to go. If you have problems, I'll gladly help you tomorrow.