Sumproduct formulas not working after editing
 

Good Afternoon:

I have input the ranges as "M2:M102"; this seems to work. For whatever
reason the sumproduct formulae don't like the named ranges, which are defined
as "M2:M965". Large ranges to allow for growth.

I will need to edit the ranges every month.

Thanks for input.

"dave roth" wrote:

Good Afternoon:

I have several Sumproduct formulae (from this board) that worked on my
calendar 2005 worksheet with named ranges which no longer work on the 2006
worksheet with named ranges. See below for examples:

=SUMPRODUCT(--(Sex_2005="Male"),--(InjuryType1_2005="Gunshot")); this works
fine on the worksheet named "2005_Data". I have new data on a new worksheet
named "2006_Data" in the same workbook; the formulae have been edited to
reflect the new named ranges, and return the #VALUE! See an example below:

=SUMPRODUCT(--(Ethnic_2006="Hispanic"),--(InjuryType1_2006="Gunshot")).

Still running MSExcel 2003, the ranges are, for example,
='2006_Data'!$O$2:$O$965 is defined as "Ethnic_2006", "InjuryType1_2006",
etc. Simple countif and Average formulae continue to calculate on the new
worksheet.

The sheet was added to the workbook as Insert--Worksheet, then the data was
pasted from another workbook (.txt).

All assistance is greatly appreciated. Many Thanks in advance.