To count and sum values of different codes with last figures same.

[nirtel]

Dear All,
The attached file shows a picking list which we use to place purchase order. There are 2 sheets, Picking List and Code & Description List, where Picking list is actual list of purchase order and Code & Description List contributes as a source for lookup and drop down menu.

Now, I am looking for help on 2 main issues:

1. Image below shows dark green cells with sumproduct functions, which calculates quantity of item (clips) depending on post (another item) length and quantities.
Now if you go to Codes & Description sheet, there are 6 codes:
P50253H100
P50503H100
P50253H100V
P50503H100V
P50253H75
P50503H75

Question: Is it possible to count P50253H100 and P50503H100 as one item and incorporate their lengths and quantities in existing if(sumproduct) function?

2. In the same image, you will see #N/A which is due to lookup error.

Question: Is it possible to get rid of those #N/As, if all dark green cells are empty? I tried using ISBLANK, but it seems to be not working.

Thanks for taking time to answer my questions.

Regards,
Nirav

 

[Hui]

Q2.
in B27, instead of:
=IF(ISBLANK(A27),"",VLOOKUP(A27,'Code & Description List'!$R$3:$T$8,2,FALSE))
use:
=IFERROR(VLOOKUP(A27,'Code & Description List'!$R$3:$T$8,2,FALSE),"")

Q1:

If the first 3 characters of the product code is enough to grouop the products together: ie: P50 are all the same type of product, then yes, they can be accumulated easily using a formula

 

[nirtel]

Hi Hui,
Thanks for your answers.

In regards to Q1, I am looking for a formula/syntax of sumproduct to combine H100, H100V and H75. Now these are last number/alphabet of the code. In what way shall I modify this formula,

=IF(SUMPRODUCT(--(A8:A25="P50253H100")),"P50253H100","")

, to achieve the result.
Please advice.

Regards,
Nirav

 

[Hui]

Nirav

You can do something like:
=SUMPRODUCT((($A$8:$A$25=A25)+($A$8:$A$25=A26)) *$D$8:$D$25*$E$8:$E$25)

or
=SUMPRODUCT((($A$8:$A$25="P50253H100")+($A$8:$A$25="Pxxxxxxx")) *$D$8:$D$25*$E$8:$E$25)