G
Graham H
I am getting a peculiar result when using the formula below to sum up unique
valus in a database.
The formula in AD3 i
=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
valus in a database.
The formula in AD3 i
=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