Sumproduct formulas not working after editing
 

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.

Sumproduct formulas not working after editing
 

Since you are on Excel 2003, convert the relevant data area into a list
by means of Data|List|Create List then adjust the formulas to refer to
the ranges in the relevant lists.

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.