SUMIFS Not Working for Currency Values

Denny57

Active Member
Joined
Nov 23, 2015
Messages
257
Office Version
  1. 365
Platform
  1. Windows
I am confused why the same code which works in one file is not working in another

Previous File

Column L contains the Gross Pay amounts (Cell Format = Currency")
Value of items in Column L obtained by simple multiplication of the values of 2 cells in the worksheet.
VBA Code:
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")

Dim x As Double, y As Double
    x = txtMonthlyWorkEarningsGross.Value
    y = txtMonthlyLeaveEarningsGross.Value

   
    txtTotalMonthlyEarningsGross.Value = Format(x + y, "Currency")
   
End Sub
Any suggestions would be most welcome
New File

Column M contains the Gross Pay amounts (Cell Format = Currency")
Value of items in Column M uploaded to cells in Currency format

VBA Code:
Private Sub cmdMonthSearch_Click()
txtPayMonthMonth.Value = cboSearchByPayMonth.Value
txtPayableHoursWork = Format(WorksheetFunction.SumIfs(Columns("K"), Columns("D"), "Work", Columns("C"), cboSearchByPayMonth.Value), "#,##0.00")
txtGrossPayWork = Format(WorksheetFunction.SumIfs(Columns("M"), Columns("D"), "Work", Columns("C"), cboSearchByPayMonth.Value), "£#,##0.00")
txtPayableHoursLeave = Format(WorksheetFunction.SumIfs(Columns("K"), Columns("D"), "Leave", Columns("C"), cboSearchByPayMonth.Value), "#,##0.00")
txtGrossPayLeave = Format(WorksheetFunction.SumIfs(Columns("M"), Columns("D"), "Leave", Columns("C"), cboSearchByPayMonth.Value), "£#,##0.00")

Dim x As Double, y As Double
    x = txtGrossPayWork.Value
    y = txtGrossPayLeave.Value

    txtTotalGrossPay.Value = Format(x + y, "Currency")
   
 Set wsPayPeriods = ThisWorkbook.Worksheets("Pay Periods")
   
txtPayDate.Text = Application.WorksheetFunction.VLookup(cboSearchByPayMonth.Text, Sheet5.Range("A2:D30"), 2, False)

End Sub

NOTE: The SUMIFS works for the txtPayableHoursWork & txtPayableHoursLeave lines of code
New File
Dropbox
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
It is not a good idea to pass an amount along with a currency symbol to TextBox controls. Theoretically you can, but it creates problems about which below. When passing a number to a TextBox, it becomes text. By passing this numeric text to a cell, an implicit conversion at the VBA-Excel boundary can occur and a number can be stored in the cell. However, this does not always happen. If the numeric text contains thousandth and decimal separators according to the system settings (in Windows), then passing to the cell will convert the text to a number. If these separators do not match, most often the numeric text will remain text in the cell. In the case where you have a “number” with a currency symbol in the TextBox, directly passing this text to the cell will result in text being stored in the cell. See the screenshot of your sheet where I set the alignment to General. This way you can see where the text is and where the number is in the cell. The text is docked to the left, the numbers are docked to the right edge (My decimal separator is a comma).
How to solve the problem?
Pass the number without the currency symbol to the TextBox. If the symbol is necessary, then put it, for example, in Label next to the TextBox. When you pass numeric text from a TextBox to a cell, always force the appropriate data type using conversion functions, e.g. CDate, CLng, CDbl, CCur.
I have these two rules:
1. when passing a number from a cell to a TextBox, I use the Format function without formatting code, which will cause the number to be displayed in the control according to my system format. This applies to numbers and dates (which are basically numbers).
VBA Code:
Me. TextBox1.Value = Format(Range("A1").Value)
2.When passing the numeric text from the control to the cell, I force the expected data type.
VBA Code:
Range("A1").Value) = CDbl(Me.TextBox1.Value)
Range("A2").Value) = CDate(Me.TextBox2.Value)
Range("A3").Value) = CCur(Me.TextBox3.Value)

It is a mistake to pass, for example, a date in the format you specify from a control to a cell, because by forcing the format you are actually passing text (the Format function always returns text!). And you should pass a number. On the other hand, if you expect a different date format in the cell, you should format the cell accordingly.

Artik
 

Attachments

  • NumbeFormat.png
    NumbeFormat.png
    8.4 KB · Views: 2
Upvote 0
Solution
Artik

Thank you for providing this extremely useful information which I will print and refer to in futurfe projects
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,136
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top