Sumproduct

[Guest]

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

 

[Guest]

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)

 

[Guest]

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

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)

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

 

[Guest]

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. ?

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

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)

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