Averaging values in a column with conditions in two other columns

[DPECK]

I have a 57 column, 8832 row spread sheet that I'm extracting data from and creating tables and charts. I'm wanting to calculate the average number of days it took to complete each work order. I have a column with the "days to complete" (col AE) and the conditions that I want to meet are status (col H) = Comp and CrewId (this will change with the crew. Elect, I&C, FireSys... )(col AH). How do I write the VBA code for this?

 

[DPECK]

bbott,
I modified the sheet and range references to the workbook that the data is in and tried the code. It runs until the 1st For loop and locks up. I tried all that I know and can't get it to work. Thanks for your efforts.


Sub CrewAve() Dim x As Long Application.ScreenUpdating = False Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = "Crew Averages" Range("A1") = "Crew ID" Range("B1") = "Average Days" For x = 2 To Sheets(1).Range("H" & Rows.Count).End(xlUp).Row If Sheets(1).Range("H" & x) <> Sheets(1).Range("H" & x).Offset(-1, 0) Then Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0) = Sheets(1).Range("H" & x) End If Next x For x = 2 To Range("A" & Rows.Count).End(xlUp).Row Range("B" & x) = WorksheetFunction.AverageIfs(Sheets(1).Range("F:F"), _ Sheets(1).Range("H:H"), Range("A" & x), _ Sheets(1).Range("C:C"), "Comp", _ Sheets(1).Range("B:B"), "<>PM", _ Sheets(1).Range("B:B"), "<>PdM") Next x Columns("A:B").AutoFit Application.ScreenUpdating = True End Sub</pre>