How do I get averages based on specific fields.

[cozzmo1]

Given data such as below,
Is there and easy way to find and display in a single cell:
what is the average time (Hours:mins) for all tickets of Complete
status.
and
what is the average Hours:mins for all tickets of status=picked AND of
the type FWSnap, ?

Thanks,
Crzzy1

ticket Date Time Status TYPE
Hours:mins
44738 11/2/2008 4:45 Opened
FWRules 0:00
44738 11/2/2008 6:41 Picked
FWRules 1:56
44738 11/2/2008 7:14 Approved
FWRules 2:29
44738 11/3/2008 0:46 Complete FWRules
17:32
44739 11/3/2008 2:43 Opened
FWSnap 0:00
44739 11/3/2008 3:15 Picked
FWSnap 0:32
44739 11/3/2008 3:20 Approved
FWSnap 0:37
44739 11/3/2008 8:25 Complete
FWSnap 5:42
44740 11/3/2008 6:38 Opened
OtherRout 0:00
44740 11/3/2008 7:27 Picked
OtherRout 0:49
44740 11/3/2008 7:28 Approved
OtherRout 0:50
44740 11/3/2008 7:40 Complete
OtherRout 1:02
44741 11/3/2008 9:27 Opened
FWUser 0:00
44741 11/3/2008 9:28 Picked
FWUser 0:01
44741 11/3/2008 9:28 Approved
FWUser 0:01
44741 11/3/2008 10:17 Complete
FWUser 0:50
44742 11/3/2008 9:35 Opened
OtherRout 0:00
44742 11/3/2008 9:36 Picked
OtherRout 0:01
44742 11/3/2008 10:04 Approved
OtherRout 0:29
44742 11/3/2008 10:13 Complete
OtherRout 0:38
44743 11/3/2008 9:59 Opened
FWRules 0:00
44743 11/3/2008 10:04 Picked
FWRules 0:05
44743 11/3/2008 10:04 Approved
FWRules 0:05
44743 11/3/2008 10:11 Complete
FWRules 0:12

 

[Bernard Liengme]

If column D has Status, E and Type and C has times
=SUMPRODUCT(--(D1100="picked"), --(E1:E100="FWSnap"), C1:C100)
Adjust ranges but do not use full-column references (D) unless you have
Excel 2007
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[Bernard Liengme]

Oh dear, that was half-done. It gives the sum, you want average
=SUMPRODUCT(--(D1100="picked"), --(E1:E100="FWSnap"),
C1:C100)/=SUMPRODUCT(--(D1100="picked"), --(E1:E100="FWSnap"))
But if you have Excel 2007, look at AVERAGEIFS
best wishes