Which Fuction is use?

  • Thread starter Thread starter shital
  • Start date Start date
S

shital

I have 2 data sheet.
IN sheet1
Name of co., Product Name, Qty, Month,
D & Q Pen 100 April
PRG Pen 100 April
D & Q Ink 550 April
D & Q Ink 400 April
D & Q Pen 100 April
PRG Ink 100 May
D & Q Ink 300 May
D & Q Pen 100 May
PRG Ink 200 May

IN Sheet2

NAME OF CO
.. /PRODUCT, PEN, INK
D & Q 5 2
PRG 3 4

With this 2 sheet I want to out like

Name of co. D & Q
Product Name April May Total
Pen 200 100 300
Ink 950 300 1250

Now I want to lookup & match name of co. & product name
with this data total & multiply D & Q with PEN 5 that is
300 * 5 = 1500 and D & Q with INK 2 that is
1250 * 2 = 2500

I have try with advance filter & Index, Match function but
some how I am not able to do it is any help .

Shital Shah
 
Hi
try the following formula in your third sheet
=SUMPRODUCT(--('sheet1!'$A$1:$A$100="D & Q"),--('sheet1'!
$B$1:$B$100=$A2),--('sheet1'!$D$1:$D$100=B$1),'sheet1'!
$C$1:$C$100)*INDEX('sheet2'!$A$1:$C$10,MATCH("D &
Q",'sheet2'!$A$1:$A$10,0),MATCH(B$2,'sheet2'!$A$1:$C$1,0))

Assumptions:
- row one on the third sheet is a heading row
- column A on this sheet stores the items
- cell B2 is the first data row for this formula
 
Back
Top