How to Look up account number in one sheet and compute total work hours from multiple workorders

[jsadler04]

I am very new to excel and have been working on this problem for 10 hours but can not seem to a understand vlookup and sumif, and how to properly apply if. Have a spreadsheet with two sheets named Customers and WorkOrderTime. Need to compute total hours worked for each account by matching the account number to each work order then adding the total hours worked from all orders.

Customers Sheet
[TABLE="width: 607"]
<tbody>[TR]
[TD]Account Name[/TD]
[TD]AccountNumber[/TD]
[TD]Total Hours[/TD]
[/TR]
[TR]
[TD]Customer1[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer2[/TD]
[TD]12345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer3[/TD]
[TD]123456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Custoimer4[/TD]
[TD]176134[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer5[/TD]
[TD]219872[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer6[/TD]
[TD]219926[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer7[/TD]
[TD]484336[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer8[/TD]
[TD]552300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer9[/TD]
[TD]598106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer10[/TD]
[TD]600058[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

WorkOrderTime

[TABLE="width: 886"]
<tbody>[TR]
[TD]AccountNumber[/TD]
[TD] WO Type[/TD]
[TD]Travel[/TD]
[TD]Labor[/TD]
[TD]TotalHours[/TD]
[TD]Date Worked[/TD]
[TD]Billable Status[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]123434[/TD]
[TD]Service[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]6/21/2016[/TD]
[TD]Fee For Service[/TD]
[TD]Repair[/TD]
[/TR]
[TR]
[TD]234567[/TD]
[TD]Service[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]6/21/2016[/TD]
[TD]No Charge[/TD]
[TD]Repair[/TD]
[/TR]
[TR]
[TD]344565[/TD]
[TD]Service[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]6/20/2016[/TD]
[TD]Fee For Service[/TD]
[TD]Repair[/TD]
[/TR]
[TR]
[TD]489023[/TD]
[TD]Service[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]2.50[/TD]
[TD="align: right"]2.50[/TD]
[TD="align: right"]6/21/2016[/TD]
[TD]Fee For Service[/TD]
[TD]Repair[/TD]
[/TR]
[TR]
[TD]598106[/TD]
[TD]Service[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]12/15/2016[/TD]
[TD]Fee For Service[/TD]
[TD]Repair[/TD]
[/TR]
[TR]
[TD]598106[/TD]
[TD]Service[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]11/7/2016[/TD]
[TD]Fee For Service[/TD]
[TD]Repair[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Implementation[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]11/30/2016[/TD]
[TD]Professional Service[/TD]
[TD]Software Install & Configuration[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Implementation[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]11/28/2016[/TD]
[TD]Professional Service[/TD]
[TD]Software Install & Configuration[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Implementation[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]11/23/2016[/TD]
[TD]Professional Service[/TD]
[TD]Software Install & Configuration[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Implementation[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]11/21/2016[/TD]
[TD]Professional Service[/TD]
[TD]Infrastructure Installation[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Implementation[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]11/9/2016[/TD]
[TD]Contract[/TD]
[TD]Upgrade[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Implementation[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]10/19/2016[/TD]
[TD]Contract[/TD]
[TD]Upgrade[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Implementation[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]10/18/2016[/TD]
[TD]Warranty[/TD]
[TD]Upgrade[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Implementation[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]10/17/2016[/TD]
[TD]Warranty[/TD]
[TD]Upgrade[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Implementation[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]6/28/2016[/TD]
[TD]Professional Service[/TD]
[TD]Software Install & Configuration[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Service[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]2.50[/TD]
[TD="align: right"]2.50[/TD]
[TD="align: right"]6/21/2016[/TD]
[TD]Fee For Service[/TD]
[TD]Repair[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Service[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]6/21/2016[/TD]
[TD]No Charge[/TD]
[TD]Repair[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Service[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]6/21/2016[/TD]
[TD]Fee For Service[/TD]
[TD]Repair[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Service[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]6/20/2016[/TD]
[TD]Fee For Service[/TD]
[TD]Repair[/TD]
[/TR]
[TR]
[TD]600058[/TD]
[TD]Service[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]6/20/2016[/TD]
[TD]Fee For Service[/TD]
[TD]Consulting[/TD]
[/TR]
[TR]
[TD]3454324[/TD]
[TD]Service[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]6/20/2016[/TD]
[TD]Fee For Service[/TD]
[TD]Consulting[/TD]
[/TR]
</tbody>[/TABLE]


I am using the following function: =SUMIF(WorkOrderTime,VLOOKUP(B11,WorkOrderTime,5,FALSE)) and getting an invaild number or it will give me the first value but it will not total all values associated to the account number. I am hoping someone could point me in the right direction, thanks.

 

[L. Howard]

Maybe this, where it returned 82.5.

You may want to use some absolute referencing on the cell ranges.

Howard

=SUMIF(A17:A37,B11,E17:E37)

 

[jsadler04]

Howard,

I will definitely try this out today and let you know how it works out. Really hope this is the solution because my original spreadsheet has over 20,000 records. Guess, I should stop volunteering for stuff like this but it is a learning experience.

 

[L. Howard]

With 20,000 rows you may want to try this snippet.

Does 100,000+ rows in about 1.5 seconds on my computer.

Adjust sheet names to your workbook and I used your posted data format on sheets 7 and 8.
If the data on sheet 8 is a fixed range, then you can use the formula line that is commented out. Otherwise, use the one with lRow8 it.
The : .Value = .Value returns the formula value only, there will be no formulas on the worksheet.

Copy to a standard module.

Howard

Option Explicit

Sub my_Long_Column()

  Dim lRow7&
  Dim lRow8&
  
  lRow7 = Sheets("Sheet7").Cells(Rows.Count, "B").End(xlUp).Row ''Note is column B Sheet7 for row count
  lRow8 = Sheets("Sheet8").Cells(Rows.Count, "A").End(xlUp).Row ''Note is column A Sheet6 for row count
  
   'MsgBox lRow7 & " " & lRow8
  
  With Sheets("Sheet7").Range("C2").Resize(lRow7 - 1) ''Returning values in column C, Sheet 7
  
    '.Formula = "=SUMIF(Sheet8!$A$2:$A$22,B2,Sheet8!$E$2:$E$22)": .Value = .Value
    .Formula = "=SUMIF(Sheet8!$A$2:$A$" & lRow8 & ",B2,Sheet8!$E$2:$E$" & lRow8 & ")": .Value = .Value
    
  End With

End Sub

 

[jsadler04]

Howard,

Thanks for the information, I will try this today and let you know the outcome. Looks like I really need to take a good excel class to be able to this on my own. any suggestions?

 

[welshgasman]

As far as I can see there is no need for the VLOOKUP?, just use the SUMIF ?