Sumproduct Adjustment

[Ellen G]

Hi there --

I have an extensive Excel spreadsheet that requires SUMPRODUCT in many of my
formulas. I have had success so far, but am now stumped. Any help would be
appreciated.

Here's a sample layout of the portion of the spreadsheet I'm calculating from:

Col A Col B (hours) Col C (bill rate)
FT 46 $95
FT-OT 48 $85
FT-OT 40 $75
FT 36 $65

So here's the issue:
1. If Col A is FT (full time), simply multiple Col B by Col C.
2. If Col A is FT-OT (overtime allowed) and over 40 hours, multiple 40
times Col C, multiple hours over 40 times (Col C * 1.5) for time-and-a-half.
3. If Col A is FT-OT and <= to 40, simply multiple Col B by Col C.

1 and 3 are not a problem at all. I can handle that. However, my problem is
with 2. It is a bit complicated communicating what I've tried, so I won't
include that here. But if anyone could provide the formula to accomplish
this, I would be VERY APPRECIATIVE.

Thanks so much. Let me know if you need any additional info.

Ellen

 

[bpeltzer]

I'd think about it in two pieces that get added together. The first is just
rate*hours. The second, the overtime premium, is "IF the type if FT-OT, AND
the hours>40, (hours-40)*rate/2.

So if your range extends to row 100, then the formula is

=sumproduct(b2:b100,c2:c100) +
sumproduct(--(a2:a100="FT-OT"),--(b2:b100>40),(b2:b100-40),(c2:c100))/2.

 

[Bill Kuunders]

=IF(AND(A1="FT-OT",B1>40),40*C1+(B1-40)*1.5*C1,B1*C1)

Greetings from New Zealand

 

[Ellen G]

PERFECT! That is exactly what I needed. I was trying to overcomplicate the
formula. My brain simply wasn't computing the rate/2. THANK YOU SO MUCH!!!

Ellen

 

[Ellen G]

Thanks, Bill. But I'm afraid that due to other aspects of my formula, I must
use SUMPRODUCT. See bpeltzer's response -- his formula is exactly what I need.

Thanks again for taking the time to respond.

Ellen