Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct Error?
I am trying to use the SUMPRODUCT function but can't
understand why I am getting the #N/A error? This is the syntax that I am using: =SUMPRODUCT((NAICS_Industry="Electric Services")* (Auditors="PWC")) NAICS_Industry and Auditors are named ranges. They are both valid because I can do a COUNTIF using both ranges and it returns the correct value. What makes this more frustrating is that if I replace the NAICS_Industry range with a range called SIC_Industry, it works. Does anyone have a clue why this would do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct Error?
Hi John
ranges in SUMPRODUCT have to be of equal size - so if you go into insert / name / define and do your two ranges have the same number of rows? additionally, =SUMPRODUCT(--(NAICS_Industry="Electric Services"),--(Auditors="PWC")) might be a more efficient way of writing the function - check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html for more details -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "john" wrote in message ... I am trying to use the SUMPRODUCT function but can't understand why I am getting the #N/A error? This is the syntax that I am using: =SUMPRODUCT((NAICS_Industry="Electric Services")* (Auditors="PWC")) NAICS_Industry and Auditors are named ranges. They are both valid because I can do a COUNTIF using both ranges and it returns the correct value. What makes this more frustrating is that if I replace the NAICS_Industry range with a range called SIC_Industry, it works. Does anyone have a clue why this would do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct Error?
With Reference to JulieD's comment, Given that the ranges are the same
number of rows (but for me, not having the same number of rows gives a #Value error), does the NAICS_Industry range contain a cell that has an #N\A error in it (perhaps it is produced with a lookup formula). If so, there's your huckleberry. In most cases, Errors in the source range get transmitted to the function and dominate the output. If that is the case, You could try =SUMPRODUCT(If(IsError(NAICS_Industry="Electric Services"),0,(NAICS_Industry="Electric Services))* (Auditors="PWC")) -- Regards, Tom Ogilvy "john" wrote in message ... I am trying to use the SUMPRODUCT function but can't understand why I am getting the #N/A error? This is the syntax that I am using: =SUMPRODUCT((NAICS_Industry="Electric Services")* (Auditors="PWC")) NAICS_Industry and Auditors are named ranges. They are both valid because I can do a COUNTIF using both ranges and it returns the correct value. What makes this more frustrating is that if I replace the NAICS_Industry range with a range called SIC_Industry, it works. Does anyone have a clue why this would do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#N/A error in sumproduct | Excel Discussion (Misc queries) | |||
SUMPRODUCT Error | Excel Worksheet Functions | |||
error sumproduct #value! | Excel Discussion (Misc queries) | |||
Why an error on Sumproduct? | Excel Discussion (Misc queries) | |||
SUMPRODUCT ERROR | Excel Discussion (Misc queries) |