Sumproduct

M

mendozalaura

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
 
J

Johnny M

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)
 
M

mendozalaura

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Johnny M

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top