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.