I have a part number in row a, and I want to look up the part # on an
inventory list in a separate worksheet, and have excel return the total
number of that part # in stock.
Right now I have:

=SUMPRODUCT(--('c:\Database\[FINISHED GOODS.XLS]Finished
Goods'!$A$2:$A$260=A85),'c:\Database\[FINISHED GOODS.XLS]Finished
Goods'!$C$2:$C$200)

A85 being the cell that contains the part # to be looked up
C:\Database\[FINISHED GOODS.XLS]Finished Goods'! being the spreadsheet to
lookup on, A2:A200 being the column with the part # in finished goods, and
c2:c200 being the column with the number of units in stock.

It is returning a #value message

I hope I am not confusing you all too much. I really could use some help

Thanks in advance

Hi
Could you not just use sumif in this case? eg =sumif(range where part nubers
sold are listed=a85,range where quantoy in stock is)

"mendozalaura" wrote:

I have a part number in row a, and I want to look up the part # on an
inventory list in a separate worksheet, and have excel return the total
number of that part # in stock.
Right now I have:

=SUMPRODUCT(--('c:\Database\[FINISHED GOODS.XLS]Finished
Goods'!$A$2:$A$260=A85),'c:\Database\[FINISHED GOODS.XLS]Finished
Goods'!$C$2:$C$200)

A85 being the cell that contains the part # to be looked up
C:\Database\[FINISHED GOODS.XLS]Finished Goods'! being the spreadsheet to
lookup on, A2:A200 being the column with the part # in finished goods, and
c2:c200 being the column with the number of units in stock.

It is returning a #value message

I hope I am not confusing you all too much. I really could use some help

Thanks in advance

The problem appears to be that the part # is the wrong data type. The part
number is alpha-numeric and excel is looking for a value to lookup. I am
throughly confused.

HELP

"Johnny M" wrote:

Hi
Could you not just use sumif in this case? eg =sumif(range where part nubers
sold are listed=a85,range where quantoy in stock is)

"mendozalaura" wrote:

I have a part number in row a, and I want to look up the part # on an
inventory list in a separate worksheet, and have excel return the total
number of that part # in stock.
Right now I have:

=SUMPRODUCT(--('c:\Database\[FINISHED GOODS.XLS]Finished
Goods'!$A$2:$A$260=A85),'c:\Database\[FINISHED GOODS.XLS]Finished
Goods'!$C$2:$C$200)

A85 being the cell that contains the part # to be looked up
C:\Database\[FINISHED GOODS.XLS]Finished Goods'! being the spreadsheet to
lookup on, A2:A200 being the column with the part # in finished goods, and
c2:c200 being the column with the number of units in stock.

It is returning a #value message

I hope I am not confusing you all too much. I really could use some help

Thanks in advance

Hi, can you use the "Text To Columns" function from the Data drop down menu
to format both columns of part numbers the same way ie as text. ?

"mendozalaura" wrote:

The problem appears to be that the part # is the wrong data type. The part
number is alpha-numeric and excel is looking for a value to lookup. I am
throughly confused.

HELP

"Johnny M" wrote:

Hi
Could you not just use sumif in this case? eg =sumif(range where part nubers
sold are listed=a85,range where quantoy in stock is)

"mendozalaura" wrote:

I have a part number in row a, and I want to look up the part # on an
inventory list in a separate worksheet, and have excel return the total
number of that part # in stock.
Right now I have:

=SUMPRODUCT(--('c:\Database\[FINISHED GOODS.XLS]Finished
Goods'!$A$2:$A$260=A85),'c:\Database\[FINISHED GOODS.XLS]Finished
Goods'!$C$2:$C$200)

A85 being the cell that contains the part # to be looked up
C:\Database\[FINISHED GOODS.XLS]Finished Goods'! being the spreadsheet to
lookup on, A2:A200 being the column with the part # in finished goods, and
c2:c200 being the column with the number of units in stock.

It is returning a #value message

I hope I am not confusing you all too much. I really could use some help

Thanks in advance