Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Problem
Hi All
I am using Excel 2003 I am having a problem with the following Sum Product formula: =SUMPRODUCT((terms_job_rptg_code="Subs207")*(terms _job_band={"01","02","03","04"})*(terms_job_type={ "Voluntary","Retirement"})*(terms_job_count)) I am getting #N/A If I remove "Voluntary" or "Retirement" (or 1 element from the 3 branch of the sumproduct) it works fine If I remove "01" and "02" (or 2 elements from the 2nd branch of the sumproduct) it works fine There are about 9900 rows of data in my ranges - I tried to cut that in half to see if it would work and it had no effect Is there some sort of limitations to the sumproduct formula for the number of variables that you can use? Any thoughts or assistance on this would be greatly appreciated Thanks, Jeff |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Problem
Are your named ranges the same SIZE?
Are "01" etc actually 1,2,3? Try with the -- as shown below Here is one I have used in the past -SUMPRODUCT(--(TRIM(Colc)={"e","w","s","g"})*--(ColD)) On Feb 9, 11:46*am, JeffTO wrote: Hi All I am using Excel 2003 I am having a problem with the following Sum Product formula: =SUMPRODUCT((terms_job_rptg_code="Subs207")*(terms _job_band={"01","02","03" ,"04"})*(terms_job_type={"Voluntary","Retirement"} )*(terms_job_count)) I am getting #N/A If I remove "Voluntary" or "Retirement" (or 1 element from the 3 branch of the sumproduct) it works fine If I remove "01" and "02" (or 2 elements from the 2nd branch of the sumproduct) it works fine There are about 9900 rows of data in my ranges - I tried to cut that in half to see if it would work and it had no effect Is there some sort of limitations to the sumproduct formula for the number of variables that you can use? Any thoughts or assistance on this would be greatly appreciated Thanks, Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct problem | Excel Worksheet Functions | |||
Problem with SUMPRODUCT | Excel Discussion (Misc queries) | |||
Problem with SUMPRODUCT | Excel Discussion (Misc queries) | |||
Problem with a sumproduct | Excel Discussion (Misc queries) | |||
SUMPRODUCT Problem | Excel Discussion (Misc queries) |