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

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