SUM of Columns based on multiple criteria with User Form

[Denny57]

I am looking to populate a field in a User Form with the sum of a range of cells based on two criteria.

Criteria 1 = Range to be determined by matching all rows in a column (Column A) in Sheet 1 that contain the same value as a textbox on the user form.
Criteria 2 = SUM of the corresponding rows in a second column (Column B) in Sheet 1 to be returned to a specific TextBox in the User Form.

I am just looking for help with the coding for one record, I can readily create the full code once I have the best way to gather this information

USER FORM FIELD DETAILS
txtPayPeriod = Value to be matched with records in Sheet 1 Column A
CmdPayPeriod = Command Button to action the sub routine (For Information Only)
txtGrossPay = SUM of Records in Sheet 1 Column B where value of Column A = txtPayPeriod value.

Many thanks

 

[Alex Blakenburg]

Maybe:

    Dim rngDate As Range
    
    Set rngDate = Range("A1", Cells(Rows.Count, "A").End(xlUp))
    txtGrossPay = Format(WorksheetFunction.SumIfs(rngDate.Offset(, 1), rngDate, CDate(txtPayPeriod)), "#,##0.00")

 

[Denny57]

Alex, Thank you for this potential solution. Unfortunately, I do not think I was explicit enough with my requirements.

Step 1 - Select a value from a Combobox (cboDatePeriod) on the User Form as the search criteria and Click the Command Button
Note - All values for this criteria are stored in Sheet1 Column A
Step 2 - SUM the values of Sheet 1 Column B (Currency format) for those records where the requested criteria are in Column A
Step 3 - Display this SUM value into a TextBox (txtGrossPay) on the User Form

I will also need to repeat the same procedure for the values in Column C (Currency format) and Columns D, E & F (number format - 2 decimal places). The same requested criteria from Column A will be used so the match might only need to be coded once . I am hopeful I can manipulate a solution to accomodate the additional requirements.

I have tried to create code that will match the ComboBox Value to a range which can hopefully be generic fof all 5 SUM functions however this appears to be beyond my abilities.

Hopefully you or someone can help with a solution

Thanks again

 

[Denny57]

SUMIF Code for a User Form

I am looking for help with VBA code for a User Form which will return SUM results for 4 different combinations based on a search via a user Form.

The search Criteria is a value from a ComboBox (cboPaymentMonth) which will be one of the values in Column B of a worksheet (Daily Hours Records).

The four Search Combinations are
1) Payment Month & Scheduled Type = Work - Returns SUM Net Hours to User Form Control txtMonthlyWorkHours
2) Payment Month & Scheduled Type = Leave - Returns SUM Net Hours to User Form Control txtMonthlyLeaveHours
3)Payment Month & Scheduled Type = Work - Returns SUM Gross Pay to User Form Control txtMonthlyWorkEarningsGross
4)Payment Month & Scheduled Type = Leave - Returns SUM Gross Pay to User Form Control txtMonthlyLeaveEarningsGross
The results of 3) & 4) need to be totalled & returned to User Form Control txtTotalMonthlyEarningsGross

Finally the Value of the search criteria needs to be displayed in User Form Control txtMonthlyPayMonth

I have attached a file which I hope will help identify my requirements.

 

[Denny57]

Here is the full code so far which might assist with coding suggestions. This covers the input and amendment of information and a search option by Date. The last requirement is for a search option by cboPayMonth.

Dim CurrentRow      As Long
Dim wsDailyHours    As Worksheet
Private Sub UserForm_Initialize()

    Set wsDailyHours = ThisWorkbook.Worksheets("Daily Hours Input")
  
    Call cmdClearForm_Click
         
End Sub

Private Sub cmdInputRecords_Click()
    Dim answer      As VbMsgBoxResult
    Dim AddRecord   As Boolean
  
    AddRecord = Val(Me.cmdInputRecords.Tag) = xlAdd
  
    answer = MsgBox(IIf(AddRecord, "Add New", "Update Current") & " Record?", 36, "Information")
    If answer = vbYes Then
      
        'new record
        If AddRecord Then CurrentRow = wsDailyHours.Range("A" & wsDailyHours.Rows.Count).End(xlUp).Row + 1
      
        On Error GoTo myerror
        With wsDailyHours
            .Cells(CurrentRow, 1).Value = DTPicker1.Value
            .Cells(CurrentRow, 2).Value = cboSchedulingType.Value
            .Cells(CurrentRow, 3).Value = cboLocation.Value
            .Cells(CurrentRow, 5).Value = txtStartTime.Value
            .Cells(CurrentRow, 6).Value = txtFinishTime.Value
            .Cells(CurrentRow, 11).Value = cboPayRate.Value
            .Cells(CurrentRow, 13).Value = CheckBoxPete.Value
            .Cells(CurrentRow, 14).Value = CheckBoxKirsty.Value
            .Cells(CurrentRow, 15).Value = CheckBoxJan.Value
            .Cells(CurrentRow, 16).Value = CheckBoxKelly.Value
            .Cells(CurrentRow, 17).Value = CheckBoxCarla.Value
        End With
      
        MsgBox "Record has been " & IIf(AddRecord, "added", "updated") & " to the database", 64, "Information"
      
    End If

    Call cmdClearForm_Click
    DTPicker1.SetFocus
  
myerror:
 If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Private Sub cmdDateSearch_Click()
    'Used to search for records for a specific date
    Dim rng             As Range
    Dim Res             As Variant, myFind As Variant
  
  
    Set rng = wsDailyHours.Range("A:A")
  
    myFind = Me.txtDateSearch.Value
  
    If Not IsDate(myFind) Then Exit Sub
  
    myFind = CDate(myFind)
  
    Res = Application.Match(CLng(myFind), rng, 0)
  
    If Not IsError(Res) Then
      
        CurrentRow = CLng(Res)
      
        With wsDailyHours
      
            DTPicker1.Value = .Cells(CurrentRow, 1)
            cboSchedulingType.Value = .Cells(CurrentRow, 2)
            cboLocation.Value = .Cells(CurrentRow, 3)
            txtStartTime.Value = .Cells(CurrentRow, 5)
            txtFinishTime.Value = .Cells(CurrentRow, 6)
            cboPayRate.Value = .Cells(CurrentRow, 11)
            CheckBoxPete.Value = .Cells(CurrentRow, 13)
            CheckBoxKirsty.Value = .Cells(CurrentRow, 14)
            CheckBoxJan.Value = .Cells(CurrentRow, 15)
            CheckBoxKelly.Value = .Cells(CurrentRow, 16)
            CheckBoxCarla.Value = .Cells(CurrentRow, 17)
            txtWorkDate.Value = .Cells(CurrentRow, 1).Value
            txtDailyPayMonth.Value = .Cells(CurrentRow, 4).Value
            txtDailyEarningsGross.Value = .Cells(CurrentRow, 12).Text
          
            If .Cells(CurrentRow, 2).Value = "Work" Then
                txtDailyWorkHours.Value = .Cells(CurrentRow, 10).Value
            ElseIf .Cells(CurrentRow, 2).Value = "Leave" Then
                txtDailyLeaveHours.Value = .Cells(CurrentRow, 10).Value
            ElseIf .Cells(CurrentRow, 2).Value = "Non-Working Day" Then
                txtDailyNonWorkingDay.Value = "Yes"
            End If
          
        End With
      
        'update submit commandbutton status
        With Me.cmdInputRecords
            .Tag = xlUpdateState
            .Caption = "Update"
            .BackColor = rgbDarkGreen
        End With
      
    Else
      
        MsgBox "Date Not Found", vbInformation, "Date Not Found"
      
    End If
  
End Sub
Private Sub cboPayRate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim payrate As Double
    payrate = cboPayRate.Value
    cboPayRate.Value = Format(cboPayRate.Value, "Currency")
End Sub
Private Sub cmdClearForm_Click()
'Clears the User Form
    DTPicker1.Value = ""
    cboSchedulingType.Value = ""
    cboLocation.Value = ""
    txtStartTime.Value = "00:00"
    txtStartTime.MaxLength = 5
    txtFinishTime.Value = "00:00"
    txtFinishTime.MaxLength = 5
    cboPayRate.Value = ""
    CheckBoxPete.Value = False
    CheckBoxKirsty.Value = False
    CheckBoxJan.Value = False
    CheckBoxKelly.Value = False
    CheckBoxCarla.Value = False
    txtWorkDate.Value = ""
    txtDailyPayMonth.Value = ""
    txtDailyWorkHours.Value = ""
    txtDailyLeaveHours.Value = ""
    txtDailyNonWorkingDay.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyPayMonth.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtMonthlyWorkEarningsGross.Value = ""
    txtMonthlyLeaveHours.Value = ""
    txtMonthlyLeaveEarningsGross.Value = ""
    txtTotalMonthlyEarningsGross.Value = ""
    txtDateSearch.Value = ""
    cboPayMonthSearch.Value = ""
  
    DTPicker1.SetFocus
  
    With Me.cmdInputRecords
        .Tag = xlAdd
        .Caption = "Add Record"
        .BackColor = rgbBlue
    End With
End Sub
Private Sub cmdCloseForm_Click()
'Closes the User Form
    Unload Me
End Sub
Private Sub txtStartTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(txtStartTime.Value) And Len(txtStartTime.Text) = 5 Then
    Else
        MsgBox "Input Start Time as for example 09:15"
        txtStartTime.Text = ""
    End If
End Sub
Private Sub txtEndTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(txtEndTime.Value) And Len(txtEndTime.Text) = 5 Then
    Else
        MsgBox "Input End Time as for example 09:15"
        txtEndTime.Text = ""
    End If
End Sub

Private Sub txtDateSearch_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Cancel = InValidDate(Me.txtDateSearch)
End Sub

'common function
Function InValidDate(ByVal Box As Object) As Boolean
    Const DateFormat As String = "dd/mm/yyyy"
    With Box
        If Len(.Value) > 0 Then
            If IsDate(.Value) Then
                'format textbox date
                .Value = Format(CDate(.Value), DateFormat)
             Else
                InValidDate = True
            End If
        End If
    End With
    If InValidDate Then MsgBox "Invalid Date Entry", 48, "Invalid Date"
End Function

 

[Alex Blakenburg]

Why don't you get your SUMIFS formulas working in Excel first and I can then help you convert them to VBA ?
PS: If you are going to use a picture to show data you need to include the column and row references.

 

[Denny57]

Why don't you get your SUMIFS formulas working in Excel first and I can then help you convert them to VBA ?
PS: If you are going to use a picture to show data you need to include the column and row references.

Alex
I apologise for providing vague and unsuitable information and also for the delay in replying. I work on these as a hobby to try to keep my mind active as pension age approaches and often other commitments mean there are sometimes there are long periods between working on projects.

I would have used dropbox to load the sample file but I could not trust the free version.

Here is the SUMIFS formula sampled to a single cell.

=SUMIFS(J:J,B:B,"Work",D:D,D2) This returns the correct value to the cell, however, there are variables which I do not understand how to code.

Columnn J holds details of the hours to be summed - (Column L holds the details of the Gross Pay to be summed)
Column B holds details of the scheduling type - the value will either be "Work" or "Leave"
Column D contains the pay month detail This detail will be requested in the Command Button action (cmdPayMonthSearch) and will be a value in a list used by a ComboBox (cboPayMonthSearch). Additionally, the cells in column D show the result of a VLOOKUP formula)

The SUMIFS formula only works in my sample because Criteria 2 has been allocated a single cell (D22) and not the value in a range of cells in column D. Additionally, Criteria 2 will actually be those cells whose VLOOKUP result value matches the value of cboPayMonthSearch on the User Form.
Incidentally, the code will not work if I type the value of Cell D2, presumably as this cell contains a formula)

VLOOKUP Formula in Cell D2 =IFNA(VLOOKUP(A2,'Pay Month Dates'!$A$1:$B$358,2,FALSE),"") - copied throughout the range in column D.

Finally the User Form Control txtMonthlyPayMonth needs to show the same value as cboPayMonthSearch

Hopefully, there is a solution where a range of cells contain a formula.

If there is a solution for returning a value for one combination of criteria, I am sure that I can recreate this for each of the four combinations, although I suspect that there will be one set of code that will seek out the required criteria and return all four combination requirements, including a zero return.

The last requirement is for the totals of txtMonthlyWorkEarningsGross & txtMonthlyLeaveEarningsGross to be totalled and retuned to txtTotalMonthlyEarningsGross

Hoping you can help with a solution

Regards

Dave

 

[Alex Blakenburg]

If you use the sample data you have already posted I can't see why you would be too worried about using Dropbox.
It would be useful to have some sample data and your working forms.
When you do use an image you need to include the Row and Column references.
In your first image (Post #3) you have Scheduled Type in column C but in your formula it's in Column B.
What control has the value "Work" in the form and what control has the value for D2. For that matter what is in column D since the code indicates Location is column C.

Your formula in in VBA would look something like this:
You would substitute "Work" and Range("D2") with the Control_Name.Value

    txtMonthlyWorkHours.Value = Format(WorksheetFunction.SumIfs( _
                                Columns("J"), _
                                Columns("B"), "Work", _
                                Columns("D"), Range("D2").Value), _
                            "#,##0.00")

 

[Denny57]

Alex

I Have created a sample file with the user form and the current code.

Please can you suggest a secure and free file sharing service (or a link) which you might be able to acccess and where I can upload the file. Unfortunately I cannot afford the £95 fee for an individual Dropbox account.

Regards

Dave

 

[Alex Blakenburg]

I’ve seen quite a few people use and suggest box.
Other options might be Google Drive or onedrive. You do need the permissions to be set to anyone with link.