SUMIF compensate for number of rows until the next SUMIF

M

muhleebbin

Active Member
Joined
Sep 30, 2017
Messages
252
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
I think that this is going to be impossible to answer without seeing the layout of the sheet. Please post an example using XL2BB (not a screen capture), remember to fictionalise any personal / confidential information, names, ID numbers, etc while retaining an accurate representation so that we can see if things are unique or repeated. I would say that we need at least enough data to cover 2 SUMIF ranges, preferably 3 or 4 if you can do that with 20-30 rows of data.

Please see the link below for details of how to install and use XL2BB.

www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com
 
Upvote 0
SUBMITTED TIME REPORT --08242020 - Mon - 08302020 - Sun v2.xlsm
ABCDEFGH
1DateJobTime InTime OutCost CodeHoursEarn CodePhase
2A Doe
38/24/2020Test7:00 AM3:00 PM1400 - LMT - PTO8REG1 - NO PHASE - Samples
48/25/2020Test7:00 AM3:00 PM1400 - LMT - PTO8REG1 - NO PHASE - Samples
58/26/2020ABC7:00 AM3:00 PM200 - Floor - Tile8REG140 - Hall 1
68/27/2020ABC7:00 AM3:00 PM200 - Floor - Tile8REG140 - Hall 1
78/28/2020ABC7:00 AM2:00 PM200 - Floor - Tile7REG140 - Hall 1
839.00
9OT0.00
10B Doe24
118/24/20201237:00 AM10:00 AM10 - Pickups and Delivery3REG110 - Living Room Fireplace
128/24/202012310:00 AM3:00 PM1202 - T&M - Masonry Fireplace5REG110 - Living Room Fireplace
138/25/20201237:00 AM3:00 PM1202 - T&M - Masonry Fireplace8REG110 - Living Room Fireplace
148/26/2020XYZ7:00 AM11:00 AM1201 - T&M - Slab4REG102 - Crypt
158/26/202012311:00 AM3:00 PM1202 - T&M - Masonry Fireplace4REG110 - Living Room Fireplace
168/27/20201237:00 AM3:00 PM1202 - T&M - Masonry Fireplace8REG110 - Living Room Fireplace
178/28/20201237:00 AM3:00 PM1202 - T&M - Masonry Fireplace8REG110 - Living Room Fireplace
1840.00
190.00
20C Doe
218/24/2020QWERT7:00 AM10:00 AM1100 - LMT Office/Design3REG101 - Pool Mechanical
228/24/2020QWERT10:00 AM12:00 PM1100 - LMT Office/Design2REG107 - BBQ
238/24/2020ASDF12:30 PM2:30 PM1100 - LMT Office/Design2REG109 - Kitchen
248/24/2020QWERT2:30 PM3:30 PM1100 - LMT Office/Design1REG122 - Laundry Room
258/25/2020ASDF7:00 AM12:00 PM1100 - LMT Office/Design5REG109 - Kitchen
268/25/2020YUIO12:30 PM2:30 PM310 - Countertop - Fabrication2REG109 - Kitchen
278/25/2020QWERT2:30 PM3:30 PM900 - LMT Shop - Fabrication1REG102 - Garage Cabana Parapet
288/26/2020QWERT6:30 AM9:00 AM900 - LMT Shop - Fabrication2.5REG102 - Garage Cabana Parapet
298/26/2020ASDF9:00 AM10:30 AM901 - Measure/Estimating1.5REG116 - Powder Room
308/26/2020YUIO10:30 AM12:00 PM310 - Countertop - Fabrication1.5REG109 - Kitchen
318/26/2020YUIO12:30 PM3:30 PM310 - Countertop - Fabrication3REG109 - Kitchen
328/27/2020QWERT7:00 AM12:00 PM900 - LMT Shop - Fabrication5REG102 - Garage Cabana Parapet
338/27/2020QWERT12:30 PM1:30 PM1100 - LMT Office/Design1REG107 - BBQ
348/27/2020YUIO1:30 PM3:30 PM310 - Countertop - Fabrication2REG109 - Kitchen
358/28/2020JKL5:45 AM6:00 AM1100 - LMT Office/Design0.25REG101 - Bathroom 1
368/28/2020QWERT6:00 AM10:00 AM900 - LMT Shop - Fabrication4REG102 - Garage Cabana Parapet
378/28/2020YUIO10:00 AM12:00 PM310 - Countertop - Fabrication2REG109 - Kitchen
388/28/2020QWERT12:30 PM3:30 PM900 - LMT Shop - Fabrication3REG102 - Garage Cabana Parapet
3941.75
401.75
41D Doe
428/24/2020ZXC7:00 AM3:30 PM1200 - T&M - Tile8.5REG103 - West Bathroom
438/25/2020ZXC7:00 AM9:00 AM1200 - T&M - Tile2REG103 - West Bathroom
448/25/2020ZXC9:00 AM12:30 PM1200 - T&M - Tile3.5REG103 - West Bathroom
458/25/2020ZXC12:30 PM3:30 PM1202 - T&M - Masonry3REG120 - T&M
468/26/2020BNM7:00 AM1:00 PM1200 - T&M - Tile6REG155 - Service
478/27/2020ZXC7:30 AM2:00 PM200 - Floor - Tile6.5REG120 - T&M
488/28/2020ZXC7:30 AM1:00 PM200 - Floor - Tile5.5REG120 - T&M
4935.00
500.00
Report
Cell Formulas
RangeFormula
F8F8=SUM(F3:F7)
F9F9=IF(F8>40,F8-SUMIF(E3:E7,"*PTO*",F3:F7)-40,0)
F18,F49F18=SUM(F11:F17)
F19,F50F19=IF(F18>40,F18-SUMIF(E11:E17,"*PTO*",F11:F17)-40,0)
F39F39=SUM(F21:F38)
F40F40=IF(F39>40,F39-SUMIF(E21:E38,"*PTO*",F21:F38)-40,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C10Cell Valuecontains "1 - General Costs"textNO
D10Cell Valuecontains "1 - General Costs"textNO
E391:E392,E2:E360,E362:E388Cell Valuecontains "1 - General Costs"textNO
E1,E393:E407,E411:E1048576Cell Valuecontains "1 - General Costs"textNO
 
Upvote 0
Now that i'm seeing it in this light, I realize another impossibility to the original ask.

The employee that produces this copies and pastes another document into this one. The new one I created has macros that sum by the colored cells cause I found a number of times they didn't calculate all the proper cells. With this new version I manually inputted those sumif's however if they paste a new data set they'll erase those formulas. Is there a way to take the formulas in F9, 19, 40 and 50 and put them somewhere else so they do the original ask however it's an easy cut and paste for the person that does this? I guess the stopping criteria for the expand or collapse would be the next instance of a sum function in F8, 18, 39 and 49
 
Upvote 0
This is probably going to need a few changes but I've based it on what I've seen so far.

I've made a few assumptions as follows.
The hours for the first person start in F3.
There are 2 empty rows for the formulas below the hours, after which there will be one more empty cell in column F (the row with the next persons name).

With these assumptions, the code below will add the formulas to the correct rows and apply the same background colour as used in your example above. Note that if the formulas are already in the sheet then it will not work, this is set up to run on the raw data as pasted in from the other document.

VBA Code: 
Option Explicit
Sub test()
Dim rng As Range, c As Range
Dim lRow As Long, nRow As Long
    Set c = Range("F3") ' start working down from F3 (first cell with hours).
lRow = Cells(Rows.Count, 6).End(xlUp).Row ' find last row with data in column F

Do Until c.Row > lRow
    Set rng = Range(c, c.End(xlDown)) ' find consecutive cells with hours
    nRow = rng.End(xlDown).Row + 1 ' find next empty row
    
    With Cells(nRow, 6) 'add sum formula and colour to first empty row
        .Formula = "=SUM(" & rng.Address & ")"
        With .Interior
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
        End With
    End With
    
    With Cells(nRow + 1, 6) ' add sumif formula and colour to second empty row
        .Formula = "=IF(F" & nRow & ">40,F" & nRow & "-SUMIF(" & rng.Offset(, -1).Address & ",""*PTO*""," & rng.Address & ")-40,0)"
         .Interior.Color = 255
    End With
    
    Set c = rng(rng.Rows.Count, 1).Offset(4) ' set start of next group of hours.
Loop

End Sub
 
Upvote 0
Solution
Jason, thank you very much! The assumptions you made were almost perfect however I just pulled the raw data set from our website and the two rows are inserted each time. I also didn't realize she deleted columns but I can create the code to hide the columns ? (super skilled here).

Could you revise the code to insert two rows and reconfigure for column H?

Just for my own self education, can you tell me if my assumptions are correct?

After your Do Until c.Row > 1Row command would you then set a command to insert 2 rows and that would pretty much be the tweak needed?
I also tried changing every instance of "F3" & F" in your vba code to "H3" etc. but it still inserted in column F, what governs where to place the formulas? If you don't have the time or the inclination to teach, no worries and again truly thankful for this!

*edit is it the Cells("value", 6)? If I changed that to 8 then it would insert into column H correct?

SUBMITTED TIME REPORT -- 08312020 - Mon - 09042020 - Fri.xlsx
ABCDEFGHIJKLMNOPQRS
1DateJobEmployeeTime InTime OutCost CodeDescriptionHoursEarn CodeAmountApproverEquipmentQtySubmitted ByShiftTradePhaseStatusRow Modified On
2TestSubtotal Hours : 30
38/31/2020TestTest7:00 AM11:00 AM1 - General Costs-4REG$0.00Test-0Test--1 - NO PHASEApproved09/03/2020 12:48 am UTC
48/31/2020TestTest11:00 AM4:00 PM300 - Vanity - Fabrication-5REG$0.00Test--Test--102 - Master BathroomPending09/03/2020 4:48 am UTC
58/31/2020TestTest6:00 AM6:30 AM210 - Backsplash - Fabrication-0.5REG$0.00Test--Test--154 - MudroomPending09/03/2020 4:42 am UTC
68/31/2020TestTest6:30 AM7:00 AM210 - Backsplash - Fabrication-0.5REG$0.00Test--Test--101 - Master 2 BathPending09/03/2020 4:43 am UTC
79/1/2020TestTest6:00 AM1:00 PM300 - Vanity - Fabrication-7REG$0.00Test--Test--102 - Master BathroomPending09/03/2020 4:51 am UTC
89/1/2020TestTest1:00 PM4:00 PM310 - Countertop - Fabrication-3REG$0.00Test--Test--131 - Laundry Room/Mud RoomPending09/03/2020 4:52 am UTC
99/2/2020TestTest11:30 AM4:00 PM206 - Shower - Fabrication-4.5REG$0.00Test--Test--102 - Master BathroomPending09/03/2020 4:57 am UTC
109/2/2020TestTest6:00 AM11:30 AM300 - Vanity - Fabrication-5.5REG$0.00Test--Test--102 - Master BathroomPending09/03/2020 4:56 am UTC
11TestSubtotal Hours : 33
128/31/2020TestTest7:00 AM3:00 PM1400 - LMT - PTO-8REG$0.00Test--Test--1 - NO PHASE - SamplesRejected08/31/2020 7:46 pm UTC
139/1/2020TestTest12:30 PM1:30 PM1100 - LMT Office/Design-1REG$0.00Test--Test--111 - Main KitchenRejected09/01/2020 10:39 pm UTC
149/1/2020TestTest9:00 AM12:00 PM1100 - LMT Office/Design-3REG$0.00Test--Test--111 - Main KitchenRejected09/01/2020 10:39 pm UTC
159/1/2020TestTest6:00 AM9:00 AM900 - LMT Shop - Fabrication-3REG$0.00Test--Test--102 - Garage Cabana ParapetRejected09/01/2020 10:38 pm UTC
169/1/2020TestTest1:30 PM3:30 PM1100 - LMT Office/Design-2REG$0.00Test--Test--109 - Kitchen 1Rejected09/01/2020 10:40 pm UTC
179/2/2020TestTest10:00 AM12:00 PM1100 - LMT Office/Design-2REG$0.00Test--Test--111 - Main KitchenPending09/02/2020 10:25 pm UTC
189/2/2020TestTest2:30 PM3:30 PM1100 - LMT Office/Design-1REG$0.00Test--Test--122 - Laundry RoomPending09/02/2020 10:26 pm UTC
199/2/2020TestTest12:30 PM2:30 PM901 - Measure/Estimating-2REG$0.00Test--Test--116 - Powder RoomPending09/02/2020 10:26 pm UTC
209/2/2020TestTest7:00 AM10:00 AM900 - LMT Shop - Fabrication-3REG$0.00Test--Test--102 - Garage Cabana ParapetPending09/02/2020 10:25 pm UTC
219/3/2020TestTest1:00 PM2:00 PM20 - Shop and Equipment Maintenance-1REG$0.00Test--Test--125 - Lander MaintenancePending09/03/2020 10:33 pm UTC
229/3/2020TestTest10:00 AM10:30 AM1100 - LMT Office/Design-0.5REG$0.00Test--Test--122 - Laundry RoomPending09/03/2020 10:30 pm UTC
239/3/2020TestTest8:00 AM9:00 AM1100 - LMT Office/Design-1REG$0.00Test--Test--122 - Laundry RoomPending09/03/2020 10:29 pm UTC
249/3/2020TestTest10:30 AM12:00 PM300 - Vanity - Fabrication-1.5REG$0.00Test--Test--102 - Master BathroomPending09/03/2020 10:31 pm UTC
259/3/2020TestTest12:30 PM1:00 PM300 - Vanity - Fabrication-0.5REG$0.00Test--Test--102 - Master BathroomPending09/03/2020 10:32 pm UTC
269/3/2020TestTest9:00 AM10:00 AM310 - Countertop - Fabrication-1REG$0.00Test--Test--109 - KitchenPending09/03/2020 10:30 pm UTC
279/3/2020TestTest2:00 PM3:30 PM900 - LMT Shop - Fabrication-1.5REG$0.00Test--Test--102 - Garage Cabana ParapetPending09/03/2020 10:34 pm UTC
289/3/2020TestTest7:00 AM8:00 AM900 - LMT Shop - Fabrication-1REG$0.00Test--Test--102 - Garage Cabana ParapetPending09/03/2020 10:28 pm UTC
29TestSubtotal Hours : 32
308/31/2020TestTest11:30 AM3:00 PM1201 - T&M - Install Tile & Coping-3.5REG$0.00Test--Test--1 - Existing PoolRejected09/02/2020 4:52 am UTC
318/31/2020TestTest6:30 AM11:00 AM1201 - T&M - Install Tile & Coping-4.5REG$0.00Test--Test--1 - Existing PoolRejected09/01/2020 2:13 pm UTC
329/1/2020TestTest11:30 AM3:00 PM1201 - T&M - Install Tile & Coping-3.5REG$0.00Test--Test--1 - Existing PoolRejected09/02/2020 4:54 am UTC
339/1/2020TestTest6:30 AM11:00 AM1201 - T&M - Install Tile & Coping-4.5REG$0.00Test--Test--1 - Existing PoolRejected09/02/2020 4:53 am UTC
349/2/2020TestTest11:30 AM3:00 PM1201 - T&M - Install Tile & Coping-3.5REG$0.00Test-0Test--1 - Existing PoolApproved09/03/2020 10:08 am UTC
359/2/2020TestTest6:30 AM11:00 AM1201 - T&M - Install Tile & Coping-4.5REG$0.00Test-0Test--1 - Existing PoolApproved09/03/2020 10:08 am UTC
369/3/2020TestTest11:30 AM3:00 PM1201 - T&M - Install Tile & Coping-3.5REG$0.00Test--Test--1 - Existing PoolPending09/03/2020 11:34 pm UTC
379/3/2020TestTest6:30 AM11:00 AM1201 - T&M - Install Tile & Coping-4.5REG$0.00Test--Test--1 - Existing PoolPending09/03/2020 11:33 pm UTC
38TestSubtotal Hours : 8
398/31/2020TestTest2:00 PM3:00 PM20 - Shop and Equipment Maintenance-1REG$0.00Test--Test--125 - Lander MaintenanceRejected09/01/2020 1:57 pm UTC
408/31/2020TestTest7:00 AM9:00 AM20 - Shop and Equipment Maintenance-2REG$0.00Test--Test--125 - Lander MaintenanceRejected09/01/2020 1:53 pm UTC
418/31/2020TestTest9:00 AM1:00 PM601 - Tub Surround - Slab-4REG$0.00Test--Test--103 - Bathroom 2Rejected09/01/2020 1:54 pm UTC
428/31/2020TestTest1:00 PM2:00 PM10 - Pickups and Delivery-1REG$0.00Test--Test--155 - ServiceRejected09/01/2020 1:55 pm UTC
43TestSubtotal Hours : 41.5
448/31/2020TestTest12:30 PM3:30 PM20 - Shop and Equipment Maintenance-3REG$0.00Test--Test--174 - Equipment Service and RepairRejected09/01/2020 1:30 pm UTC
458/31/2020TestTest7:00 AM12:00 PM20 - Shop and Equipment Maintenance-5REG$0.00Test--Test--174 - Equipment Service and RepairRejected09/01/2020 1:29 pm UTC
468/31/2020TestTest7:00 AM5:00 PM300 - Vanity - Fabrication-10REG$0.00Test-0Test--101 - Bathroom 1Approved08/28/2020 10:44 am UTC
479/1/2020TestTest7:00 AM11:00 AM20 - Shop and Equipment Maintenance-4REG$0.00Test--Test--174 - Equipment Service and RepairRejected09/02/2020 2:02 pm UTC
489/1/2020TestTest11:00 AM1:00 PM500 - Base - Slab-2REG$0.00Test--Test--127 - Pool BldgRejected09/02/2020 2:03 pm UTC
499/1/2020TestTest1:00 PM2:30 PM311 - Countertop - Slab-1.5REG$0.00Test--Test--109 - Kitchen 1Rejected09/02/2020 2:05 pm UTC
509/2/2020TestTest12:30 PM3:30 PM20 - Shop and Equipment Maintenance-3REG$0.00Test--Test--127 - Forest MaintenancePending09/03/2020 1:28 pm UTC
519/2/2020TestTest7:00 AM12:00 PM20 - Shop and Equipment Maintenance-5REG$0.00Test--Test--127 - Forest MaintenancePending09/03/2020 1:28 pm UTC
529/3/2020TestTest12:30 PM3:30 PM20 - Shop and Equipment Maintenance-3REG$0.00Test--Test--127 - Forest MaintenancePending09/04/2020 1:54 pm UTC
539/3/2020TestTest7:00 AM12:00 PM20 - Shop and Equipment Maintenance-5REG$0.00Test--Test--127 - Forest MaintenancePending09/04/2020 1:53 pm UTC
54TestSubtotal Hours : 24.5
558/31/2020TestTest7:00 AM4:00 PM1200 - T&M - Tile-9REG$0.00Test--Test--155 - ServiceRejected09/01/2020 1:27 pm UTC
569/1/2020TestTest7:00 AM2:30 PM1200 - T&M - Tile-7.5REG$0.00Test--Test--155 - ServiceRejected09/02/2020 1:05 pm UTC
579/2/2020TestTest7:00 AM8:00 AM1200 - T&M - Tile-1REG$0.00Test--Test--155 - ServicePending09/03/2020 1:05 pm UTC
589/3/2020TestTest7:00 AM2:00 PM200 - Floor - Tile-7REG$0.00Test--Test--120 - T&MPending09/04/2020 1:05 pm UTC
59TestSubtotal Hours : 38
608/31/2020TestTest12:00 PM2:00 PM1511 - face surface grout-2REG$0.00Test--Test--154 - MudroomRejected09/01/2020 3:17 pm UTC
618/31/2020TestTest8:00 AM12:00 PM400 - Shower - Tile-4REG$0.00Test--Test--104 - Bathroom 3Rejected09/01/2020 3:16 pm UTC
629/1/2020TestTest2:00 PM4:00 PM1501 - DryTile Layout-2REG$0.00Test--Test--104 - Bathroom 3Pending09/03/2020 3:48 pm UTC
639/1/2020TestTest8:00 AM2:00 PM400 - Shower - Tile-6REG$0.00Test--Test--104 - Bathroom 3Pending09/03/2020 3:46 pm UTC
649/2/2020TestTest8:00 AM4:00 PM400 - Shower - Tile-8REG$0.00Test--Test--104 - Bathroom 3Pending09/03/2020 3:48 pm UTC
659/3/2020TestTest1:30 PM4:00 PM1501 - DryTile Layout-2.5REG$0.00Test--Test--104 - Bathroom 3Pending09/03/2020 10:30 pm UTC
669/3/2020TestTest8:00 AM1:30 PM400 - Shower - Tile-5.5REG$0.00Test--Test--104 - Bathroom 3Pending09/03/2020 10:29 pm UTC
679/4/2020TestTest8:00 AM4:00 PM1400 - LMT - PTO-8REG$0.00Test--Test--1 - NO PHASE - SamplesPending09/03/2020 10:31 pm UTC
SUBMITTED TIME REPORT -- 083120
 
Upvote 0
Your assumptions are close, I had to move the check of c.Row > lRow to the end and re-check after each pass of the loop. Doing the check once at the start would mean that the last group gets missed as the rows are moved down and c.Row moves below the original lRow.

I've changed everything to column H, inserted 2 rows for the formulas after each set of hours and hidden the extra columns so that it appears the same as the first example. Think that was everything.

VBA Code: 
Option Explicit
Sub test()
Range("C:C,G:G,J:P,R:S").EntireColumn.Hidden = True
Dim rng As Range, c As Range
Dim lRow As Long, nRow As Long
    Set c = Range("H3") ' start working down from H3 (first cell with hours).
lRow = Cells(Rows.Count, 8).End(xlUp).Row ' find last row with data in column H

Do Until c.Row > lRow
    Set rng = Range(c, c.End(xlDown)) ' find consecutive cells with hours
    nRow = rng.End(xlDown).Row + 1 ' find next empty row
Rows(nRow).Resize(2).Insert
    With Cells(nRow, 8)
        .Formula = "=SUM(" & rng.Address & ")" 'add sum formula and colour to first empty row
        With .Interior
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
        End With
    End With
    
    With Cells(nRow + 1, 8) ' add sumif formula and colour to second empty row
        .Formula = "=IF(H" & nRow & ">40,H" & nRow & "-SUMIF(" & rng.Offset(, -1).Address & ",""*PTO*""," & rng.Address & ")-40,0)"
         .Interior.Color = 255
    End With
    
    Set c = rng(rng.Rows.Count, 1).Offset(4) ' set start of next group of hours.
    lRow = Cells(Rows.Count, 8).End(xlUp).Row ' find last row with data in column H
Loop

End Sub
 
Upvote 0
That worked perfectly, thank you very much Jason!
 
Upvote 0
Jason,

I had to revise the formula for the PTO calculation because i'm an idiot and this go around with payroll it gave me a negative number as the formula was

It has since been revised to:

=IF(H180>40,IF((H180-SUMIF($F$165:$F$179,"*PTO*",$H$165:$H$179)>40),H180-SUMIF($F$165:$F$179,"*PTO*",$H$165:$H$179)-40,0),0)

I subsequently revised the code to:

.Formula = "=IF(H" & nRow & ">40,IF((H" & nRow & "-SUMIF(" & rng.Offset(, -2).Address & ",""*PTO*""," & rng.Address & ")>40),H" & nRow & "-SUMIF(" & rng.Offset(, -2).Address & ",""*PTO*""," & rng.Address & ")-40,0),0)"

It seems to work but if you wouldn't mind just doing a quick double check, that would be greatly appreciated.

Also a totally random ask and should be on a new thread but in the data set above, lines 2, 11, 29 etc has our names in the format of "1 - A Doe", "107 - B Doe", "112 - C Doe", "2 - D Doe", "21 - E Doe", etc. Is there a way to have it automatically sort it in the proper number order? Of course keeping their lines underneath grouped with them?

Thanks
 
Upvote 0
The code looks correct for the formula, the most common error that people make is a missing " or ) which would cause an error when you try to run the code.

Re your random ask, that would need to be a new thread. You can't do what you want with regular worksheet sorting, the data would have to be sorted in vba then written back to the sheet in the required order. It's something that I might be able to fudge together given time but there are other forum members who would be able to do it better and quicker than I would.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
809
arthurbr
arthurbr
welshgasman
  • Solved
Show average only once
Replies
3
Views
258
Fluff
Fluff
F
  • Question
Help with converting shift time to hours worked with lunch deductions
Replies
0
Views
817
Frank94
F
L
  • Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
960
Armstrong_N14
A
R
  • Question
Tracking Quarterly Total Hours
Replies
1
Views
278
RandyD123
R

Forum statistics

Threads
1,214,584
Messages
6,120,385
Members
448,956
Latest member
JPav

Share this page

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
Back
Top