SUMPRODUCT PROBLEM

[Guest]

Assistance much appreciated
My worksheet as follows

col A col B col C col D col E
Name Club Age Group Place Points
Fred aaa 16 1 5
Harry bbb 25 2 4

Col E is a result of =IF($D2=1,â€5â€, IF($D2=2,â€4â€, IF($D2=3,â€3â€,
IF($D2=4,â€2â€, IF($D2=5,â€1â€,â€â€)))))

Using =SUMPRODUCT(--($B$2:$B$25=â€aaaâ€)*($E$2:$E25)) in H5 I get a #Value#
error if any cell in col E is blank within the range.

Need to sum by club the total number of points. My range would include
people with no placing leaving the result in col E blank.

Many Thanks

 

[Guest]

First, results in E will be text not numbers as a result of your formula -
removw "" arounf results

=IF($D2=1,5, IF($D2=2,4, IF($D2=3,3, IF($D2=4,2, IF($D2=5,1â€â€â€)))))

which can be simplified to

=6-D2

Or

=if(D2="","",6-D2)

SUMPRODUCTworks OK or you could try

=SUMIF(B2:B25,"aaa",E2:E25)

HTH

 

[Guest]

First, results in E will be text not numbers as a result of your formula -
removw "" arounf results

=IF($D2=1,5, IF($D2=2,4, IF($D2=3,3, IF($D2=4,2, IF($D2=5,1â€â€â€)))))

which can be simplified to

=6-D2

Or

=if(D2="","",6-D2)

SUMPRODUCTworks OK or you could try

=SUMIF(B2:B25,"aaa",E2:E25)

HTH

 

[Roger Govier]

Hi Laurie

Unless there is any reason why you are wanting the numbers in column E
to be text, you don't need " " around the numbers, but that isn't your
real problem.
Change the final "" (null) to a zero
=IF($D2=1,5, IF($D2=2,4, IF($D2=3,3,IF($D2=4,2, IF($D2=5,1,0)))))
or this might be able to be simplified to
=IF($D2="",0,IF(D2>5,0,6-$D2))

The Sumproduct seems to work fine.
If you don't want the zero's to show on the empty lines, then
Tools>Options>General>untick Zero values

 

[Roger Govier]

Hi Laurie

Unless there is any reason why you are wanting the numbers in column E
to be text, you don't need " " around the numbers, but that isn't your
real problem.
Change the final "" (null) to a zero
=IF($D2=1,5, IF($D2=2,4, IF($D2=3,3,IF($D2=4,2, IF($D2=5,1,0)))))
or this might be able to be simplified to
=IF($D2="",0,IF(D2>5,0,6-$D2))

The Sumproduct seems to work fine.
If you don't want the zero's to show on the empty lines, then
Tools>Options>General>untick Zero values

 

[Guest]

Thanks Roger your info has resolved my problem, However to make my SUMPRODUCT
cell more efficient to my needs I would like to do the following:-

For cell G5,G6,G7 etc to return a unique name from col B so my SUMPRODUCT
function in H5,H6,H7 etc can ref G5 etc this would allow me to automate the
result without have to edit my formula as it would be referencing the result
in G5,G6,G7 etc.

Thankyou
Laurie g

 

[Guest]

Thanks Roger your info has resolved my problem, However to make my SUMPRODUCT
cell more efficient to my needs I would like to do the following:-

For cell G5,G6,G7 etc to return a unique name from col B so my SUMPRODUCT
function in H5,H6,H7 etc can ref G5 etc this would allow me to automate the
result without have to edit my formula as it would be referencing the result
in G5,G6,G7 etc.

Thankyou
Laurie g