# SUMIF compensate for number of rows until the next SUMIF

#### muhleebbin

##### Board Regular
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

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

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### jasonb75

##### Well-known Member
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.

Replies
1
Views
90
Replies
1
Views
315
Replies
1
Views
389
Replies
1
Views
122
Replies
10
Views
1K

1,127,341
Messages
5,624,111
Members
416,011
Latest member
chengkoonwing

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back