SUMIF compensate for number of rows until the next SUMIF

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Thanks for the quick response and checking, i'll post it up in a new thread!
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top