Sumproduct

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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...
 
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...
 
Back
Top