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]

Welcome to the forum!

It's a little confusing, but is this anywhere close?

Excel 2010
	B	G	J	Q
7	Month Number	Part Number	Qty	Cost Value
8	1	NI	50	150
IRs

Cell Formulas
Range	Formula
Q8	=IFERROR((SUMIF('Price Codes'!D3:D14,IRs!B8,INDEX('Price Codes'!E3:I14,0,MATCH(IRs!G8,'Price Codes'!E2:I2,0)))*J8)-H8,0)

Excel 2010
	C	D	E	F	G	H	I
2	Months	Month Number	CU	AG	Ni	Tin	Alloy
3	01/01/2015	1	1	2	3	4	1
4	01/02/2015	2	1	2	3	4	5
5	01/03/2015	3	0	0	0	0	0
6	01/04/2015	4	0	0	0	0	0
7	01/05/2015	5	0	0	0	0	0
8	01/06/2015	6	0	0	0	0	0
9	01/07/2015	7	0	0	0	0	0
10	01/08/2015	8	0	0	0	0	0
11	01/09/2015	9	0	0	0	0	0
12	01/10/2015	10	0	0	0	0	0
13	01/11/2015	11	0	0	0	0	0
14	01/12/2015	12	0	0	0	0	0
Price Codes

 

[gaz_chops]

Try

=(IF(OR(LEFT($G8,2)={"BB","EB","CA","EC"}),SUMIF('Price Codes'!$D$3:$D$14,$B8,'Price Codes'!$E$4:$E$4)*$J8,"0.00")-$H$8)

 

[sdel_nevo]

Hi formR and Gaz_Chops

many thanks for the replys, my god that was quick

I will try the codes sent and will let you know how I get on

Many many thanks

Steve

 

[gaz_chops]

Hi Steve, I forgot to alter the range E4:E4, so
=(IF(OR(LEFT($G8,2)={"BB","EB","CA","EC"}),SUMIF('Price Codes'!$D$3:$D$14,$B8,'Price Codes'!$E$3:$E$14)*$J8,"0.00")-$H$8)

 

[sdel_nevo]

Hi Gaz_Chops

wow that works brill

Many many thanks, forums like this is what makes the internet so great

Thank you so much for your help

Steve

 

[gaz_chops]

Hi Steve, you're welcome, glad it worked.

 

[gaz_chops]

Realised you don't need the OR, so
=(IF(LEFT($G8,2)={"BB","EB","CA","EC"},SUMIF('Price Codes'!$D$3:$D$14,$B8,'Price Codes'!$E$4:$E$4)*$J8,"0.00")-$H$8)

 

[FormR]

So you always want to return a value from column E on the "Price Codes" sheet then?

 

[gaz_chops]

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