SUMIF compensate for number of rows until the next SUMIF

[muhleebbin]

Is there a way to create the following formula to expand or contract to the next instance of a SUMIF above it either through a macro or some other various mean?

=IF(F18>40,F18-SUMIF(E11:E17,"*PTO*",F11:F17)-40,0)

The formula is used to find any overtime for an employee, taking out any Paid Time Off hours so that we accurately pay our employees. There's over 25 employees and the number of rows (E11:E17 and F11:F17) can vary employee by employee and even week over week depending on how many jobs the employee is working on.

If this formula is used as is, you can imagine how tedious it would be to expand/contract columns E & F to each employees new data set each week.

Columns are:
Date, Job, Time In, Time out, Cost Code, Hours, Earn Code, Phase

If there's another better way to calculate the Overtime hours accurately I wouldn't mind scrapping this original idea as well.

 

[muhleebbin]

Thanks for the quick response and checking, i'll post it up in a new thread!

 

[jasonb75]

You're welcome

As long as you can get the formula into a cell in the sheet it is just a matter of comparing it to the original formula. If you have a formula in vba that the code will not enter into the cell because the syntax is incorrect, try removing the = sign from the start of the formula, that way it will be entered as a text string in the cell so that you can check it.