The formula sums the quantity in col. B for the specific
day of the month, and then divides by the total *unique*
dates that fall on the that day of the month. 1/10/05,
1/10/05, 2/10/05, 2/10/05, 3/10/05 = 3 unique dates for
the 10th.

It works in my test. If you want a sample workbook, email
me (replace OPPOSITEOFCOLD with you know what) with the
subject line: complex count question.

HTH
Jason
Atlanta, GA

-----Original Message-----
Jason,

Thanks for the suggestion. I tried to use frequency()
as well, but couldn't
get it right. I think you may be clsoe to the solution,
but the proposed
function evaluates to #N/A. I've taken it apart and
found that the problem
is in the denominator (surprise.) I'll work with it a
little and see if I
can figure it out following your suggestion.

"Jason Morin" wrote:

Copy this into D1 (watch the wrap), press ctrl + shift
+
enter, and fill down:

=SUM((DAY($A$1:$A$1000)=D1)*($B$1:$B$1000))/SUM(--
(FREQUENCY(IF(DAY($A$1:$A$1000)=D1,MATCH
($A$1:$A$1000,$A$1:$A$1000,0)),MATCH
($A$1:$A$1000,$A$1:$A$1000,0))0))

HTH
Jason
Atlanta, GA

-----Original Message-----
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.
--
Jim
.


.