Forum Discussion
Solved
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 multi...
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)
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)
