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