Duplicate P/N and sums

[Guest]

Ok, I'll try to explain this as best I can. I have my open order report and
sort it by Customer and then part number. The same part numbers can show up
once or multiple times depending on how many orders there are for that part
number.

Also, I have to customers that have an inventory/open order report that both
use the VLOOKUP functions to pull then open orders from the open order
report. My problem is that the vlookup will find the part number and show
the open qty listed to the right and stop rather than show the sum for all of
that pat number.

Someone please help me!!

 

[Gordon]

"Steve ([email protected])"

Ok, I'll try to explain this as best I can. I have my open order report
and
sort it by Customer and then part number. The same part numbers can show
up
once or multiple times depending on how many orders there are for that
part
number.

Also, I have to customers that have an inventory/open order report that
both
use the VLOOKUP functions to pull then open orders from the open order
report. My problem is that the vlookup will find the part number and show
the open qty listed to the right and stop rather than show the sum for all
of
that pat number.

Someone please help me!!

Use SUMIF instead of a VLOOKUP

 

[Guest]

How would I type it out? Here is what I use now:
=IF(ISNA(VLOOKUP($P2,'I:\Customer\Dynamics\[Dynamics Open Order
Report.XLS]Dyn
Stock'!$A$4:$E$64998,5,FALSE)),"0",VLOOKUP($P2,'I:\Customer\Dynamics\[Dynamics Open Order Report.XLS]Dyn Stock'!$A$4:$E$64998,5,FALSE))

 

[Dave Peterson]

The bad news is =sumif() won't work when you close your other workbook.

You could use =sumproduct() instead, though.

=SUMPRODUCT(--('[Dynamics Open Order Report.XLS]Dyn stock'!$A$4:$A$64998=$P2),
'[Dynamics Open Order Report.XLS]Dyn stock'!$E$4:$E64998)

Build the formula with that workbook open. Then you can verify that it works.
When you close the workbook, excel will add the path and folder.

In fact, I'd let excel do the work...

type
=sumproduct(--(

and point to A4:A64998 of the dyn stock worksheet.

(Do you really need all those rows? It could take a while to recalc.)

How would I type it out? Here is what I use now:
=IF(ISNA(VLOOKUP($P2,'I:\Customer\Dynamics\[Dynamics Open Order
Report.XLS]Dyn
Stock'!$A$4:$E$64998,5,FALSE)),"0",VLOOKUP($P2,'I:\Customer\Dynamics\[Dynamics Open Order Report.XLS]Dyn Stock'!$A$4:$E$64998,5,FALSE))

"Steve ([email protected])"


Use SUMIF instead of a VLOOKUP

 

[Guest]

OMG, You are a life saver!!!!

Thank You SOOO much!!
Steve

The bad news is =sumif() won't work when you close your other workbook.

You could use =sumproduct() instead, though.

=SUMPRODUCT(--('[Dynamics Open Order Report.XLS]Dyn stock'!$A$4:$A$64998=$P2),
'[Dynamics Open Order Report.XLS]Dyn stock'!$E$4:$E64998)

Build the formula with that workbook open. Then you can verify that it works.
When you close the workbook, excel will add the path and folder.

In fact, I'd let excel do the work...

type
=sumproduct(--(

and point to A4:A64998 of the dyn stock worksheet.

(Do you really need all those rows? It could take a while to recalc.)

How would I type it out? Here is what I use now:
=IF(ISNA(VLOOKUP($P2,'I:\Customer\Dynamics\[Dynamics Open Order
Report.XLS]Dyn
Stock'!$A$4:$E$64998,5,FALSE)),"0",VLOOKUP($P2,'I:\Customer\Dynamics\[Dynamics Open Order Report.XLS]Dyn Stock'!$A$4:$E$64998,5,FALSE))

"Steve ([email protected])"
Ok, I'll try to explain this as best I can. I have my open order report
and
sort it by Customer and then part number. The same part numbers can show
up
once or multiple times depending on how many orders there are for that
part
number.

Also, I have to customers that have an inventory/open order report that
both
use the VLOOKUP functions to pull then open orders from the open order
report. My problem is that the vlookup will find the part number and show
the open qty listed to the right and stop rather than show the sum for all
of
that pat number.

Someone please help me!!

Use SUMIF instead of a VLOOKUP