This is an array formula that must be entered/edited using ctrl+shift+enter
vs just enter
=AVERAGE(IF((D2:D6=2002)*(E2:E60),E2:E6))

--
Don Guillett
SalesAid Software

"Randy" wrote in message
...
I'm trying to come up with a formula that will sum the values in any of
these
columns based on the year column. For example, I have the following
formula
that sums the values in the Create column if the Year column = 2002. The
second part of the formula needs to then count how many numbers were added
so
that an average can be calculated. That's the part I cannot figure out
because I need to only count cells with a value 0 for whatever year I use
in
the formula. Can this be done via a formula or do I have to resort to a
macro? I wouldn't be surprised if someone told me a totally different
formula will get the result I need.

Thanks!

Randy

=SUMIF($R2:$R6,"2002",M2:M6)/COUNTIF(M2:M6,"0")


CREATE ESTIMATE APPROVE PENDING ACTIVE COMPLETE YEAR
284.69 2002
52.34 721.87 2003
52.31 79.73 263.14 2002
230.25 515.83 2002
2002
34.96 45.09 0.17 2002
42.16 3.02 35.02 190.88 256.52 2004
54.08 8.81 20.94 395.09 475.49 2002
54.08 8.8 20.94 395.13 475.49 2005
54.08 8.82 5.99 56 419.24 293.49 2002
54.07 8.81 6 410.11 370.48 2002