Average based on multi Criteria

trimiii

Board Regular
Joined
May 15, 2018
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hello,

Are you familiar with the Sumproduct() function ...?
 
Upvote 0
Yes, but any help would be appreciated... please advise.
 
Upvote 0
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
ABCDEFGHIJKLM
1LocDescTimeLoc ADescAverageAverage of TimeDesc
2Loc A1.5.635Loc A1.5.632.5Row Labels1.3.81.5.6Grand Total
3Loc B1.5.662Loc A5032.538.33333333
4Loc B1.5.620Loc B4141
5Loc A1.3.850Grand Total5036.7539.4
6Loc A1.5.630
7
Average
 
Last edited:
Upvote 0
Thanks a lot for reply, appreciate your assistance and support.

Works just fine...
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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