Average based on multi Criteria

trimiii

New Member
Joined
May 15, 2018
Messages
48
Office Version
365
Platform
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

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

trimiii

New Member
Joined
May 15, 2018
Messages
48
Office Version
365
Platform
Windows
Yes, but any help would be appreciated... please advise.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,462
Office Version
365
Platform
Windows
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:

trimiii

New Member
Joined
May 15, 2018
Messages
48
Office Version
365
Platform
Windows
Thanks a lot for reply, appreciate your assistance and support.

Works just fine...
 

Forum statistics

Threads
1,089,428
Messages
5,408,166
Members
403,188
Latest member
Sanjana Ramesh

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top