Help with formula

[jasedc5r]

Hi All,

I hope someone could help me with a formula which have spent a few hours trying to formulate, however, without any success (beginner level).

I have two worksheets (Sheet 1 and Sheet 2). Cell highlighted in yellow, I need to formulate the total quantity of parts required for the month based of the number of cars from the schedule plan (Sheet 2). The schedule plan is setup based on the job plans.

So the formula I need is - job plan from column B to I (sheet 1), lookup job plans in (sheet 2), find month, the value of number of cars and multiple by 'Qty per job' value (Sheet 1, column A) for each job plan entry from column B to I.

Thanks in advance.

Sheet 1

Sheet 2

 

[hajiali]

can you set an example for few of them?

 

[Marcelo Branco]

Maybe...

Formula in Sheet1 J3 copied across and down
=SUMPRODUCT(SUMIFS(INDEX(Sheet2!$B$4:$V$7,0,MATCH(J$2,Sheet2!$B$2:$V$2,0)),Sheet2!$A$4:$A$7,$B3:$I3))*$A3

M.

 

[jasedc5r]

Works a treat. Thanks Marcelo

 

[Marcelo Branco]

You're welcome. Glad to help.

M.