Sumproduct Problem


J

JeffTO

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
 
Ad

Advertisements

D

Don Guillett

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top