Sumproduct error
 

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

Sumproduct error
 

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

Sumproduct error
 

Think I spotted something myself which still confuses me. Under sheet 3 there
are two entries of 9.1 and under sheet 6 there are two entries of 9.1, half
of whcih is the 4.55 which is the difference I was finding in the totals. It
would suggest that if there are duplicate numbers within different sheet
groups, 1,2,3 etc where there may be 11,11,4,3 in sheet 1 and ,14,6 11 11 in
sheet 3 that the formula cannot cope with that situation.? I may be wrong but
grasping at straws he)

Graham

"Graham H" wrote:


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