I modified it include all 65536 and it works great!! I would appreciate it
if you could break the formula down and explain how it works.

Randy

"Don Guillett" wrote:

Did you try it?

--
Don Guillett
SalesAid Software

"Randy" wrote in message
...
Don,

This works as well but my real data is several thousand rows and the
AVERAGE
statement is limited to 20.

Randy

"Don Guillett" wrote:

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