Newbie struggling with Sumproduct problem

[sdel_nevo]

Hi Guys

I have been battling with this issue for a while now after inheriting this spread sheet from a previous employee who has since left the company before I started

I will try to explain my problem in plain English as best as I can, here goes.

I have a spread sheet with two workbooks, "IRs" and "price Codes"

on the IRs workbook I need to calculate the price of scrap produced based on the part number being scrapped by month.

the Work book "Price codes" holds the average price of parts by month in this format

C D E F G H I
Months Month Number CU AG Ni Tin Alloy
3 01/01/15 1 1.00 2.00 3.00 4.00 1.00
4 01/02/15 2 1.00 2.00 3.00 4.00 5.00
5 01/03/15 3 0.00 0.00 0.00 0.00 0.00
6 01/04/15 4 0.00 0.00 0.00 0.00 0.00
7 01/05/15 5 0.00 0.00 0.00 0.00 0.00
8 01/06/15 6 0.00 0.00 0.00 0.00 0.00
9 01/07/15 7 0.00 0.00 0.00 0.00 0.00
10 01/08/15 8 0.00 0.00 0.00 0.00 0.00
11 01/09/15 9 0.00 0.00 0.00 0.00 0.00
12 01/10/15 10 0.00 0.00 0.00 0.00 0.00
13 01/11/15 11 0.00 0.00 0.00 0.00 0.00
14 01/12/15 12 0.00 0.00 0.00 0.00 0.00


The work book "Irs" hold various details about the item being scrapped but my problem is this

I Have the Month Number In Column B of the "IRs" Work Book
I have a part number in column G of the "IRs" work book
I have the cost value in Column Q of the "IRs" Work Book

In column Q I have this code
=(IF(OR(LEFT($G8,2)="BB",LEFT($G8,2)="EB",LEFT($G8,2)="CA",LEFT($G8,2)="EC"),SUMPRODUCT(--('Price Codes'!$D$3:$D$14=$B8)*SUM('Price Codes'!$E$4:$E$4)*$J8),"0.00")-$H$8)

This code looks up the month in the Work book "Price codes" ('Price Codes'!$D$3:$D$14=$B8) based on the month of the entry on the "IRs" work book from cell $B8, then gets the correct price from the "price Codes" work book *SUM('Price Codes'!$E$4:$E$4)*$J8, $J8 holds the quantity, if the part does not start with either BB, EB, CA or EC then 0.00 is returned

This code is getting the correct price if I set the cell reference to the correct cell from the "Price codes" work book $E$4:$E$4 but my question is this

what do I need to change to get the correct price from the "Price Codes" work book automatically based on the month

I have been trying to get my head around this problem for a while now, I am used to working with Access VBA but I am totally new to excel and I am struggling with the syntax.

Any help or pointers would be really really appreciated

I am really sorry if this has been asked before

Steve

 

[FormR]

@FormR, I think you missed the bit that checks for LEFT($G8,2)={"BB","EB","CA","EC"}

Maybe, but I guess I'm just not getting it at all (which wouldn't be the first time!)

I thought the OP wanted to return a value from the column on "Price List" sheet that matched the part number in cell G8 on the "Irs" sheet.

 

[gaz_chops]

No, check the 1st 2 characters of the part no. then sumif the matching date, I think!

 

[FormR]

I'm happy to admit I've probably got it wrong