Forum Discussion
Excel SUMIF with OR or something?
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)
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 Replies
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)
