See my reply in your other post.

--
Biff
Microsoft Excel MVP


"Graham H" wrote in message
...

I am getting a peculiar result when using the formula below to sum up
unique
valus in a database.
The formula in AD3 is
=SUMPRODUCT(($D$15:$D$600=AB3)/COUNTIF($J$15:$J$600,$J$15:$J$600&"")*$J$15:$J$600 )
The table is shown below the summary with the intervening columns blanked
out so it is just showing the Sheet numbers and the numbers beside these
whcih will add up to the columns AC and AD below. Now the totals for all
the
sheets in AC is correct but in AD the totals for Sheet 3 and Sheet 6 are
short by exactly 4.55 in each and I can see no reason why and I have been
over this time and again. If I make the range above in the formula 15:79
instead of 600 I get a number which has many decimal places, and if I
change
it to 15:78 then the correct figure appears. I have copied the figure
below
onto a sepearte spreadsheet and still get the same answer and I am totally
at
a loss but I am sure there is something I must be missing. If anyone can
see
it jumping out at them I would appreciate any help but please don't spend
time on it.
Graham
SheetField AreaEligible
AB AC AD
1 27.5 27.5 <<AD3
2 31.92 31.41
3 42.77 37.61<<Wrong
4 39.31 39.29
5 46.83 46.83
6 43.91 39.22<<Wrong
7 16.56 14.35
8 31.33 31.33







D E F G H I J

1 5.89 5.89 << Row 15
1 5.89 5.89
1 1.34 1.34
1 20.27 20.27
1 20.27 20.27
1 20.27 20.27
1 20.27 20.27
1 20.27 20.27
1 20.27 20.27
1 20.27 20.27
1 20.27 20.27
1 20.27 20.27
2 12.36 12.36
2 12.36 12.36
2 0.74 0.32
2 0.74 0.32
2 5.2 5.2
2 5.2 5.2
2 13.62 13.53
2 13.62 13.53
2 13.62 13.53
2 13.62 13.53
2 13.62 13.53
2 13.62 13.53
3 9.3 9.1
3 9.3 9.1
3 11.21 11.21
3 11.21 11.21
3 11.21 11.21
3 14.39 14.27
3 14.39 14.27
3 14.39 14.27
3 7.87 7.58
3 7.87 7.58
3 7.87 7.58
3 7.87 7.58
4 8.81 8.79
4 8.81 8.79
4 8.81 8.79
4 8.81 8.79
4 8.81 8.79
4 12.84 12.84
4 12.84 12.84
4 17.66 17.66
4 17.66 17.66
4 17.66 17.66
5 17.41 17.41
5 17.41 17.41
5 17.41 17.41
5 17.41 17.41
5 13.48 13.48
5 13.48 13.48
5 13.48 13.48
5 7.2 7.2
5 7.2 7.2
5 7.2 7.2
5 8.74 8.74
5 8.74 8.74
6 16.85 16.75
6 16.85 16.75
6 16.85 16.75
6 9.59 9.59
6 9.59 9.59
6 9.59 9.59
6 9.1 9.1
6 9.1 9.1
6 8.37 8.33
6 8.37 8.33
6 8.37 8.33
7 2.52 0.4
7 2.52 0.4
7 8.86 8.86
7 8.86 8.86
7 8.86 8.86
7 8.86 8.86
7 5.18 5.09
7 5.18 5.09
7 5.18 5.09
8 22.53 22.53
8 22.53 22.53
8 22.53 22.53
8 22.53 22.53
8 22.53 22.53
8 0.48 0.48
8 0.48 0.48
8 8.32 8.32
8 8.32 8.32