Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,522
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct problem Chris Excel Worksheet Functions 2 October 27th 09 12:45 AM
Problem with SUMPRODUCT Tasha Excel Discussion (Misc queries) 6 August 28th 09 09:27 PM
Problem with SUMPRODUCT Sasikiran Excel Discussion (Misc queries) 5 June 29th 09 09:22 AM
Problem with a sumproduct Sasikiran Excel Discussion (Misc queries) 4 June 24th 09 04:36 PM
SUMPRODUCT Problem Mestrella31 Excel Discussion (Misc queries) 2 December 21st 04 08:01 PM


All times are GMT +1. The time now is 10:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"