Calculate Average using multiple criteria

[cswildrick]

For Employee A, I need to calculate the average number of days it takes him to fill his positions by year. I try using an average formula, but come up with a zero.

<TABLE style="WIDTH: 296pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=394 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3264" width=102><COL style="WIDTH: 54pt" span=2 width=72><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3456" width=108><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1280" width=40><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 height=21 width=102>Employee</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 width=72>Function</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 width=72>Position</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 width=108># of days to fill</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl29 width=40>Year</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=19>Employee A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">HR</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30>Position 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 x:num> 16</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=19>Employee B</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26>Operations</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>Positioin 2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28 x:num> 73</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=19>Employee A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl32>Finance</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31>Position3 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 x:num> 48</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=19>Employee C</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl32>Sales</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31>Position 4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 x:num> 48</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=19>Employee A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl33>SC-I</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28>Position 5 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 x:num> 32</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=19>Employee A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl32>Executive</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31>Position 6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 x:num> 105</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=20>Employee C</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl32>Marketing</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26>Position 7</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl31 x:num> 64</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>2011</TD></TR></TBODY></TABLE>
Employee A HR Positioin 8 42 2011

 

[Aladin Akyurek]

Care to formulate your query using the values that exist in your exhibit? Position 8 is for example is not there. It's unclear how 42 is calculated...

 

[T. Valko]

For Employee A, I need to calculate the average number of days it takes him to fill his positions by year. I try using an average formula, but come up with a zero.

<TABLE style="WIDTH: 296pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=394 border=0 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3264" width=102><COL style="WIDTH: 54pt" span=2 width=72><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3456" width=108><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1280" width=40><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 77pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=102 height=21>Employee</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72>Function</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72>Position</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 81pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=108># of days to fill</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 30pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=40>Year</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>Employee A</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">HR</TD><TD class=xl30 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Position 1</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>16</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>Employee B</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Operations</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Positioin 2</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>73</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>Employee A</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Finance</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Position3 </TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>48</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>Employee C</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sales</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Position 4</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>48</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>Employee A</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">SC-I</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Position 5 </TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>32</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>Employee A</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Executive</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Position 6</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>105</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl30 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Employee C</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Marketing</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Position 7</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>64</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2011</TD></TR></TBODY></TABLE>
Employee A HR Positioin 8 42 2011

Book1
	A	B	C	D	E
1	Employee	Function	Position	# of days to fill	Year
2	Employee A	HR	Position 1	16	2010
3	Employee B	Operations	Positioin 2	73	2010
4	Employee A	Finance	Position3	48	2010
5	Employee C	Sales	Position 4	48	2010
6	Employee A	SC-I	Position 5	32	2010
7	Employee A	Executive	Position 6	105	2011
8	Employee C	Marketing	Position 7	64	2011
Sheet1

Use cells to hold the criteria:

• A15 = Employee A
• B15 = 2010

Try one of these...

If you're using Excel 2007 or later:

=AVERAGEIFS(D2:D8,A2:A8,A15,E2:E8,B15)

This array formula** will work in any version of Excel:

=AVERAGE(IF(A2:A8=A15,IF(E2:E8=B15,D2:D8)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Robert Mika]

Hi and welcome to the forum.

If your data are in cells A1 to E9

Try this formula:

=AVERAGE(IF(A2:A9=A11,IF(E2:E9=2011,D2:D9)))
(array formula confirm CTRL+SHIFT+ENTER)

Where A11 is Employee A.

 

[cswildrick]

=AVERAGE(IF($B$2:$B$9="HR",IF($B$2:$B$9="Finance",IF($B$2:$B$9="SC-I",IF($B$2:$B$9="Executive",IF(YEAR($E$2:$E$9)=2010,$D$2:$D$9))))))

<TABLE style="WIDTH: 282pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=376 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3456" width=108><COL style="WIDTH: 54pt" span=2 width=72><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2208" width=69><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1760" width=55><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 25.5pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl26 height=34 width=108>Employee

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl27 width=72>Function</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl27 width=72>Position</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl28 width=69># of days to fill</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #666699 1pt solid" class=xl29 width=55>Year</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl31 width=72>HR</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Position 1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>16</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num> 2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee B</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Operations</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Positioin 2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>73</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num> 2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Finance</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72 x:str="Position3 ">Position3 </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>48</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num> 2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee C</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Sales</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Position 4</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>48</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num> 2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>SC-I</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72 x:str="Position 5 ">Position 5 </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>32</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num> 2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Executive</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Position 6</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>105</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num> 2011</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee C</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Marketing</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Position 7</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>64</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num> 2011</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>HR</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Position 8</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num>42</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num> 2011</TD></TR></TBODY></TABLE>

 

[T. Valko]

=AVERAGE(IF($B$2:$B$9="HR",IF($B$2:$B$9="Finance",IF($B$2:$B$9="SC-I",IF($B$2:$B$9="Executive",IF(YEAR($E$2:$E$9)=2010,$D$2:$D$9))))))

<TABLE style="WIDTH: 282pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=376 border=0 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3456" width=108><COL style="WIDTH: 54pt" span=2 width=72><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2208" width=69><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1760" width=55><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 81pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 25.5pt; BACKGROUND-COLOR: white" width=108 height=34>Employee


</TD><TD class=xl27 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=72>Function</TD><TD class=xl27 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=72>Position</TD><TD class=xl28 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: silver; WIDTH: 52pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=69># of days to fill</TD><TD class=xl29 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: silver; WIDTH: 41pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=55>Year</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" width=108 height=19>Employee A</TD><TD class=xl31 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>HR</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Position 1</TD><TD class=xl32 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 52pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" align=right width=69 x:num u1:num>16</TD><TD class=xl33 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 41pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=55 x:num u1:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" width=108 height=19>Employee B</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Operations</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Positioin 2</TD><TD class=xl32 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 52pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" align=right width=69 x:num u1:num>73</TD><TD class=xl33 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 41pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=55 x:num u1:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" width=108 height=19>Employee A</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Finance</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72 x:str="Position3 ">Position3 </TD><TD class=xl32 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 52pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" align=right width=69 x:num u1:num>48</TD><TD class=xl33 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 41pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=55 x:num u1:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" width=108 height=19>Employee C</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Sales</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Position 4</TD><TD class=xl32 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 52pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" align=right width=69 x:num u1:num>48</TD><TD class=xl33 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 41pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=55 x:num u1:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" width=108 height=19>Employee A</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>SC-I</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72 x:str="Position 5 ">Position 5 </TD><TD class=xl32 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 52pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" align=right width=69 x:num u1:num>32</TD><TD class=xl33 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 41pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=55 x:num u1:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" width=108 height=19>Employee A</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Executive</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Position 6</TD><TD class=xl32 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 52pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" align=right width=69 x:num u1:num>105</TD><TD class=xl33 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 41pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=55 x:num u1:num>2011</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" width=108 height=19>Employee C</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Marketing</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Position 7</TD><TD class=xl32 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 52pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" align=right width=69 x:num u1:num>64</TD><TD class=xl33 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 41pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=55 x:num u1:num>2011</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" width=108 height=19>Employee A</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>HR</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 54pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=72>Position 8</TD><TD class=xl32 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 52pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" align=right width=69 x:num>42</TD><TD class=xl33 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 41pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: white" width=55 x:num>2011</TD></TR></TBODY></TABLE>

Use cells to hold all of the function criteria:

• A15 = HR
• A16 = Finance
• A17 = SC-I
• A18 = Executive

Then, array entered**:

=AVERAGE(IF(ISNUMBER(MATCH(B2:B9,A15:A18,0)),IF(E2:E9=2010,D2:D9)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I'm assuming that column E contains year numbers and not full dates formatted to display just the year number.

 

[Aladin Akyurek]

=AVERAGE(IF($B$2:$B$9="HR",IF($B$2:$B$9="Finance",IF($B$2:$B$9="SC-I",IF($B$2:$B$9="Executive",IF(YEAR($E$2:$E$9)=2010,$D$2:$D$9))))))

<TABLE style="WIDTH: 282pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=376 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3456" width=108><COL style="WIDTH: 54pt" span=2 width=72><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2208" width=69><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1760" width=55><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 25.5pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl26 height=34 width=108>Employee


</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl27 width=72>Function</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl27 width=72>Position</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl28 width=69># of days to fill</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #666699 1pt solid" class=xl29 width=55>Year</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl31 width=72>HR</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Position 1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>16</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee B</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Operations</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Positioin 2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>73</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Finance</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72 x:str="Position3 ">Position3 </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>48</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee C</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Sales</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Position 4</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>48</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>SC-I</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72 x:str="Position 5 ">Position 5 </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>32</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num>2010</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Executive</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Position 6</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>105</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num>2011</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee C</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Marketing</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Position 7</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num u1:num>64</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num u1:num>2011</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 81pt; HEIGHT: 14.25pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 height=19 width=108>Employee A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>HR</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=72>Position 8</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 52pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl32 width=69 align=right x:num>42</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 41pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl33 width=55 x:num>2011</TD></TR></TBODY></TABLE>

Control+shift=enter, not just enter:

=AVERAGE(
    IF(ISNUMBER(MATCH($B$2:$B$9,{"HR","Finance","SC-I","Executive"},0)),
    IF(YEAR($E$2:$E$9)=2010,
      $D$2:$D$9))))

If you create a range for the function criteria, named as FList and let K2 house the year criterion like 2010...

Control+shift=enter, not just enter:

=AVERAGE(
    IF(ISNUMBER(MATCH($B$2:$B$9,List,0)),
    IF(YEAR($E$2:$E$9)=2010,
      $D$2:$D$9))))

 

[cswildrick]

Using the formula below got me the results I needed. THANK YOU!!
=AVERAGE(
IF(ISNUMBER(MATCH($B$2:$B$9,List,0)),
IF(YEAR($E$2:$E$9)=2010,
$D$2:$D$9))))

 

[Aladin Akyurek]

Using the formula below got me the results I needed. THANK YOU!!
=AVERAGE(
IF(ISNUMBER(MATCH($B$2:$B$9,List,0)),
IF(YEAR($E$2:$E$9)=2010,
$D$2:$D$9))))

You are welcome. Thanks for providing feedback.