"Days", Weeks", and "Months" are bad choices for Function names, So I will Use "DailyRateTimes", WeeklyRateTimes" and MonthlyRateTimes" As Functions in a Module. You will need to verify all range addresses yourself
Public Function DailyRateTimes(Target As Range) As Currency
Dim DailyRate As Currency
DailyRate = Sheets("Tier").Range("C2")
DailyRateTimes = DailyRate * Target
End Function
Public Function WeeklyRateTimes(Target As Range) As Currency
Dim WeeklyRate As Currency
WeeklyRate = Sheets("Tier").Range("C2") * 7
WeeklyRateTimes = WeeklyRate * Target
End Function
Public Function MonthlyRateTimes(Target As Range) As Currency
Dim MonthlyRate As Currency
MonthlyRate = Sheets("Tier").Range("D2")
MonthlyRateTimes = MonthlyRate * Target
End Function
Copy those three Functions in to a Module in the workbook. The Formula in D2, the first cell under the "Comm Amt to Charge' cell will be
=SUM(DailyRateTimes(A2),WeeklyRateTimes(B2),MonthlyRateTimes(C2))
Drag that formula down as far as needed