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(D25,0,6-$D2))

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

--
Regards

Roger Govier


"laurie g" wrote in message
...
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