Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Duplicate P/N and sums
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!! |
#2
|
|||
|
|||
"Steve )"
om wrote in message ... 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 |
#3
|
|||
|
|||
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)) "Gordon" wrote: "Steve )" om wrote in message ... 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 |
#4
|
|||
|
|||
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.) Steve ) wrote: 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)) "Gordon" wrote: "Steve )" om wrote in message ... 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 -- Dave Peterson |
#5
|
|||
|
|||
OMG, You are a life saver!!!!
Thank You SOOO much!! Steve "Dave Peterson" wrote: 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.) Steve ) wrote: 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)) "Gordon" wrote: "Steve )" om wrote in message ... 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cummelative sums | Excel Discussion (Misc queries) | |||
Excell will not add multiple sums together, what do I have turned. | Excel Discussion (Misc queries) | |||
How come some worksheets automaticly change sums and others don't. | Excel Discussion (Misc queries) | |||
Replace Letter "E" with Letter "C" for 200 Different Sums in a Col | Excel Worksheet Functions | |||
LOOKUP FUNCTION WITH SUMS VALUES | Excel Discussion (Misc queries) |