JBoulton,

Please note that my formula differs slightly from those provided by both
Ron and Jason. Consider the following...

8/10/04 1000
9/10/04 1500
9/10/04 1250
1/5/05 1750
1/5/05 1800
3/5/05 2250
4/5/05 2500
4/5/05 1900
4/5/05 2300
10/5/05

Fifth day of the month average:

My formula --- 4166.67

Other formulas --- 3125

As you can see, 10/5/05 is not taken into consideration until a number,
including zero, is entered in the corresponding cell in Column B.

I don't know if this makes a difference or whether this is an issue, but
I thought I'd bring it to your attention.

To get the same results as the other formulas...

=SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/MAX(1,SUM(IF(FREQUENCY(IF(DAY($A$
1:$A$100)=D1,$A$1:$A$100),IF(DAY($A$1:$A$100)=D1,$ A$1:$A$100))0,1,0)))

Hope this helps!

In article ,
"JBoulton" wrote:

Domenic,

Perfect! Thanks for the help.

"Domenic" wrote:

Try...

E1, copied down:

=SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/MAX(1,SUM(IF(FREQUENCY(IF((DAY($A
$1:$A$100)=D1)*($B$1:$B$100<""),$A$1:$A$100),IF(( DAY($A$1:$A$100)=D1)*($
B$1:$B$100<""),$A$1:$A$100))0,1,0)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"JBoulton" wrote:

All,

a1:a1000 contains a list of dates including duplicates and b1:b1000
contains
amounts. I need to calculate the average amount by day of the month.
For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is
6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should
contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how
to
get the denominator.