Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT PROBLEM
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT PROBLEM
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 "laurie g" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT PROBLEM
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT PROBLEM
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 "Roger Govier" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem using ADDRESS() in SUMPRODUCT() | Excel Discussion (Misc queries) | |||
Frustrating SUMPRODUCT problem. | Excel Discussion (Misc queries) | |||
SUMPRODUCT Problem | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
SUMPRODUCT problem | Excel Worksheet Functions |