Named ranges which seem to reference old workbooks/worksheets
 

My array formula is {=sum((park=$A5)*(type=4100)*(Qty))}
Park, Type, and Qty are named ranges.
I can't seem to get the appropriate items recognized. I am trying to sum
the values in the Qty range for park and type matching the criteria. When I
start from scratch, this works exactly as I plan, but when using already
established data, the is not working and/or giving me error messages.
Sometimes I get something, but not correct. When I get errors, it seems to
center on the park name. Evaluation leads to "ACAD" = (whatever the value is
in cell $A5). I have copied the data in the named ranges from other
workbooks but have attempted to sever the links. When I seem to be getting
something, it asks me to update and references a different file with a popup
window to select a file (which I cancel).

Is there a way I can clear all the references from old data so that I can
use it the way I want without entering it all again? I don't even know if I
am asking the right question. I have tried copypastewith values only to
new workbooks, renamed ranges etc.
MSOffice Excel 2003

Named ranges which seem to reference old workbooks/worksheets
 

Try Sumproduct:

=Sumproduct((park=$A5)*(type=4100)*(Qty)

Make sure that your named ranges are all the same size to avoid getting an
error returned.

"mhudsonak" wrote:

My array formula is {=sum((park=$A5)*(type=4100)*(Qty))}
Park, Type, and Qty are named ranges.
I can't seem to get the appropriate items recognized. I am trying to sum
the values in the Qty range for park and type matching the criteria. When I
start from scratch, this works exactly as I plan, but when using already
established data, the is not working and/or giving me error messages.
Sometimes I get something, but not correct. When I get errors, it seems to
center on the park name. Evaluation leads to "ACAD" = (whatever the value is
in cell $A5). I have copied the data in the named ranges from other
workbooks but have attempted to sever the links. When I seem to be getting
something, it asks me to update and references a different file with a popup
window to select a file (which I cancel).

Is there a way I can clear all the references from old data so that I can
use it the way I want without entering it all again? I don't even know if I
am asking the right question. I have tried copypastewith values only to
new workbooks, renamed ranges etc.
MSOffice Excel 2003

Named ranges which seem to reference old workbooks/worksheets
 

I don't want sumproduct, I just want the QTY summation for records which meet
the first two criteria....I know my formula is correct, I am having trouble
with the named ranges being recognized by it....

"ChristopherTri" wrote:

Try Sumproduct:

=Sumproduct((park=$A5)*(type=4100)*(Qty)

Make sure that your named ranges are all the same size to avoid getting an
error returned.

"mhudsonak" wrote:

My array formula is {=sum((park=$A5)*(type=4100)*(Qty))}
Park, Type, and Qty are named ranges.
I can't seem to get the appropriate items recognized. I am trying to sum
the values in the Qty range for park and type matching the criteria. When I
start from scratch, this works exactly as I plan, but when using already
established data, the is not working and/or giving me error messages.
Sometimes I get something, but not correct. When I get errors, it seems to
center on the park name. Evaluation leads to "ACAD" = (whatever the value is
in cell $A5). I have copied the data in the named ranges from other
workbooks but have attempted to sever the links. When I seem to be getting
something, it asks me to update and references a different file with a popup
window to select a file (which I cancel).

Is there a way I can clear all the references from old data so that I can
use it the way I want without entering it all again? I don't even know if I
am asking the right question. I have tried copypastewith values only to
new workbooks, renamed ranges etc.
MSOffice Excel 2003