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

Account Name	AccountNumber	Total Hours
Customer1	0
Customer2	12345
Customer3	123456
Custoimer4	176134
Customer5	219872
Customer6	219926
Customer7	484336
Customer8	552300
Customer9	598106
Customer10	600058	#VALUE!

<tbody>
</tbody>

WorkOrderTime

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

<tbody>
</tbody>

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 ?