Help with formula

jasedc5r

New Member
Joined
Apr 26, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
1616551977852.png


Sheet 2
1616551764827.png
 

Attachments

  • 1616551660316.png
    1616551660316.png
    13.1 KB · Views: 5

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

hajiali

Active Member
Joined
Sep 8, 2018
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
can you set an example for few of them?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top