Formula for Calculating Overtime and Premium Time

[JenMcG]

Hello,

I'm looking for a formula that will multiply a value based on the time codes of Regular (no multiplier), Overtime (1.5 multiplier) and Premium(2 multiplier).

The download from our accounting software displays as follows:

[TABLE="width: 117"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] Type [/TD]
[TD] Hours [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Regular [/TD]
[TD]0.50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Regular [/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Regular [/TD]
[TD]0.50[/TD]
[/TR]
</tbody>[/TABLE]

Can an IF statement be developed for this?

Thank you,

Jen

 

[Joe4]

What are you multiplying? The number of hours?
Try this (for data found in A2:B10):

=[COLOR=#0000ff](SUMPRODUCT(--(A2:A10="Regular"),--(B2:B10)))[/COLOR]+[COLOR=#008000](SUMPRODUCT(--(A2:A10="Overtime"),--(B2:B10))*1.5)[/COLOR]+[COLOR=#ff0000](SUMPRODUCT(--(A2:A10="Premium"),--(B2:B10))*2)[/COLOR]

 

[JenMcG]

I'm multiplying the value in the hours column by the multiplier based on the pay "type." Example: Pay Type- Overtime, Hours- .50, formula to multiple .50 by 1.5 to return a value of .75.

 

[Joe4]

I'm multiplying the value in the hours column by the multiplier based on the pay "type." Example: Pay Type- Overtime, Hours- .50, formula to multiple .50 by 1.5 to return a value of .75.

OK. The formula I posted should be doing exactly that.
I color-coded the formula so you can see each of the three parts of it.