Try this array formula** :

List the sites in a range of cells:

H1 = XH
H2 = XI
H3 = XJ
H4 = XK

=AVERAGE(IF((A2:A100=1)*(ISNUMBER(MATCH(B2:B100,H1 :H4,0))),C2:C100))

You must be using Excel 2007. I would not use entire columns as range
references unless you absolutely must.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
I have three columns of data, one is years (e.g., 1, 2, 3, 4, etc), one is
sites (e.g., XA, XB, XC, XD, etc), and the other is the values I would
like
to average (e.g., 1.2, 1.6, 1.5, 2.1, etc). I have attempted to use
criteria
with AVERAGEIFS to average only those values in year 1 at sites XH, XI,
XJ,
XK. Here is the function I am using:
=AVERAGEIFS(Sheet3!H:H,Sheet3!C:C,"=1",Sheet3!D:D, "XH",Sheet3!D:D,"XI",Sheet3!D:D,"XJ",Sheet3!D:D,"X K")

However, it returns the #DIV/0! error. What is the appropriate function
for
average only the data in year 1 at sites XH, XI, XJ, XK?

Thank you.