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

 

[dmt32]

Unfortunately I cannot afford the £95 fee for an individual Dropbox account.

You can get a limited free account that most use

Dropbox free account

Dave

 

[Denny57]

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.

Alex

Hopefully this link will allow you access to the file

Dropbox

www.dropbox.com

I am only looking for additional code for the Search By Pay Month Command to return the 6 controls in the bottom right of the User Form.

Dave

 

[Alex Blakenburg]

Does this format not give you what you need ?
(add or remove criteria as required)

    txtMonthlyWorkHours = Format(WorksheetFunction.SumIfs( _
                                Columns("J"), _
                                Columns("B"), cboSchedulingType.Value, _
                                Columns("C"), cboLocation.Value, _
                                Columns("D"), cboPayMonthSearch.Value), _
                            "#,##0.00")

 

[Denny57]

Does this format not give you what you need ?
(add or remove criteria as required)

    txtMonthlyWorkHours = Format(WorksheetFunction.SumIfs( _
                                Columns("J"), _
                                Columns("B"), cboSchedulingType.Value, _
                                Columns("C"), cboLocation.Value, _
                                Columns("D"), cboPayMonthSearch.Value), _
                            "#,##0.00")

Alex

Thank you, I have eventually managed to code the Command Button so the code works with a couple of exceptions.

Private Sub cmdPayMonthSearch_Click()

txtMonthlyPayMonth.Value = cboPayMonthSearch.Value
txtMonthlyWorkHours = Format(WorksheetFunction.SumIfs(Columns("J"), Columns("B"), "Work", Columns("D"), cboPayMonthSearch.Value), "#,##0.00")
txtMonthlyWorkEarningsGross = Format(WorksheetFunction.SumIfs(Columns("L"), Columns("B"), "Work", Columns("D"), cboPayMonthSearch.Value), "#,##0.00")
txtMonthlyLeaveHours = Format(WorksheetFunction.SumIfs(Columns("J"), Columns("B"), "Leave", Columns("D"), cboPayMonthSearch.Value), "#,##0.00")
txtMonthlyLeaveEarningsGross = Format(WorksheetFunction.SumIfs(Columns("L"), Columns("B"), "Leave", Columns("D"), cboPayMonthSearch.Value), "#,##0.00")
txtMonthlyEarningsGross = txtMonthlyWorkEarningsGross.Value + txtMonthlyLeaveEarningsGross.Value
End Sub

txtMonthlyWorkEarningsGross and txtMonthlyLeaveEarningsGross are populated with Currency (£) totals. If there a simple cahnge that can show the values as currency.
Also
txtMonthlyEarningsGross = txtMonthlyWorkEarningsGross.Value + txtMonthlyLeaveEarningsGross.Value does not work and should provide the total of these two textboxes

Thank you again for you invaluable help

Dave

 

[Denny57]

Alex

Thank you, I have eventually managed to code the Command Button so the code works with a couple of exceptions.

Private Sub cmdPayMonthSearch_Click()

txtMonthlyPayMonth.Value = cboPayMonthSearch.Value
txtMonthlyWorkHours = Format(WorksheetFunction.SumIfs(Columns("J"), Columns("B"), "Work", Columns("D"), cboPayMonthSearch.Value), "#,##0.00")
txtMonthlyWorkEarningsGross = Format(WorksheetFunction.SumIfs(Columns("L"), Columns("B"), "Work", Columns("D"), cboPayMonthSearch.Value), "#,##0.00")
txtMonthlyLeaveHours = Format(WorksheetFunction.SumIfs(Columns("J"), Columns("B"), "Leave", Columns("D"), cboPayMonthSearch.Value), "#,##0.00")
txtMonthlyLeaveEarningsGross = Format(WorksheetFunction.SumIfs(Columns("L"), Columns("B"), "Leave", Columns("D"), cboPayMonthSearch.Value), "#,##0.00")
txtMonthlyEarningsGross = txtMonthlyWorkEarningsGross.Value + txtMonthlyLeaveEarningsGross.Value
End Sub

txtMonthlyWorkEarningsGross and txtMonthlyLeaveEarningsGross are populated with Currency (£) totals. If there a simple cahnge that can show the values as currency.
Also
txtMonthlyEarningsGross = txtMonthlyWorkEarningsGross.Value + txtMonthlyLeaveEarningsGross.Value does not work and should provide the total of these two textboxes

Thank you again for you invaluable help

Dave

Alex

Thank you for all invaluableyour help

I have finally managed to generate VBA code that will return Currency values in the required textboxes and also to sum these boxes and return the total in a third textbox.

I will store the code should I require to use similar in the future.

Best Regards

Dave