Average based on multi Criteria

[trimiii]

Fellas,

I have spreadsheet as follows:

Column A - location for 8 different maintenance workshop
Column B - description ID for work completed (ID#1.5.6) / example tire change = 1.5.6, rim change is = 1.5.7... etc
Column C - labor hours for each ID that work has been completed (up to 25000 line items) based on ID... example ID#1.5.6=35 mins, ID 1.5.7=56 mins


I am struggling to find average of the hours complete (column C) based on location (Column A) and based on description ID (column B).

Any idea or help would be appreciated fro all.

 

[James006]

Hello,

Are you familiar with the Sumproduct() function ...?

 

[trimiii]

Yes, but any help would be appreciated... please advise.

 

[Peter_SSs]

Two other options are the AVERAGEIFS function (G2 below) or Excel's built-in Pivot Table feature (see the Insert ribbon tab) which could produce all the averages for you (columns I:L below)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Loc	Desc	Time		Loc A	Desc	Average		Average of Time	Desc
2	Loc A	1.5.6	35		Loc A	1.5.6	32.5		Row Labels	1.3.8	1.5.6	Grand Total
3	Loc B	1.5.6	62						Loc A	50	32.5	38.33333333
4	Loc B	1.5.6	20						Loc B		41	41
5	Loc A	1.3.8	50						Grand Total	50	36.75	39.4
6	Loc A	1.5.6	30
7
Average

 

[trimiii]

Thanks a lot for reply, appreciate your assistance and support.

Works just fine...

 

[Peter_SSs]

Thanks a lot for reply, appreciate your assistance and support.

Works just fine...

Glad to help.