Excel SUMIF with OR or something?

[ShazzieH]

Hi everyone.  I hope you can help.  I need to calculate the number of hours worked on a spreadsheet sent to me by a client.  The worksheet calculates the number of working days in the sheet and multiplies it by 9 to get the total hours for the month.  If the hours worked are more than the monthly hours, then those hours need to go to the Overtime column.  HOWEVER, if the staff member has taken Leave, Sick Leave or Family Leave that is regarded as paid so the spreadsheet needs to count the number of days marked A, S or F and mutliply that by 9 hours as well.  If the hours including A,S or F are more than the hours for the month, then the overtime hours need to kick in.

I used the following formula and it's not working.  I am tearing my hair out!! HELP.............

Calculate total hours worked: =IF(SUM(SUMIF($A$1:$AE$1,{2,3,4,5,6},$A4:$AE4)+SUM(COUNTIFS($A4:$AE4,{"A","S","U","F"})*9))>=$AF$2,$AF$2,SUM(SUMIF($A$1:$AE$1,{2,3,4,5,6},$A4:$AE4)+SUM(COUNTIFS($A4:$AE4,{"A","S","U","F"})*9)))

Calculate Overtime:  =IF(SUM(SUMIF($A$1:$AE$1,{2,3,4,5,6},$A4:$AE4))<=$AF$2,0,SUM(SUMIF($A$1:$AE$1,{2,3,4,5,6},$A4:$AE4))-$AF$2)

[#2 · Accepted solution]

@ShazzieH may I suggest a different approach to use MIN and MAX to constrain those output instead:

Calculate total hours worked:

=MIN(SUM(SUMIF($A$1:$AE$1,{2,3,4,5,6},$A5:$AE5)+SUM(COUNTIFS($A5:$AE5,{"A","S","U","F"})*9)),$AF$2)

Calculate Overtime: 

=MAX(SUM(SUMIF($A$1:$AE$1,{2,3,4,5,6},$A5:$AE5))+SUM(COUNTIFS($A5:$AE5,{"A","S","U","F"}))*9-$AF$2,0)

[#3]

@mtarler many thanks.  I will try this tomorrow and let you know.  Thank you for the suggestion

Regards, Sharon

[#4]

Oh wow! I don't know why I didn't think of MIN and Max but it WORKS :) Thank you very much for you help. I really appreciate it :)