sumproduct two tables, multiple conditions

[nileshp]

I have a products table with raw material proportions. 2nd table with raw material rates.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product name[/TD]
[TD]RM names[/TD]
[TD]%age[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]RM2[/TD]
[TD]49%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]RM3[/TD]
[TD]51%[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]RM1[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]RM3[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]RM2[/TD]
[TD]20%[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]RM names[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]01-04-2017[/TD]
[TD]RM1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]01-04-2017[/TD]
[TD]RM2[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]01-04-2017[/TD]
[TD]RM3[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]01-05-2017[/TD]
[TD]RM1[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]01-05-2017[/TD]
[TD]RM2[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]01-05-2017[/TD]
[TD]RM3[/TD]
[TD]75[/TD]
[/TR]
</tbody>[/TABLE]

Not all RM is used in all products. I want to get product cost by multiply rates to %age for given product and date.

Thanks for reading

Nilesh

 

[nileshp]

An it be solved with excel formula. If not, pl help with VBA solution

Nilesh

 

[Peter_SSs]

Try this, copied down.

00 Old HTML Conversions 2020-04-08 1.xlsm
	A	B	C	D	E	F	G
1	Product name	RM names	%age		Product	Date	Cost
2	Product1	RM2	49%		Product2	1/04/2017	75
3	Product1	RM3	51%		Product1	1/04/2017	60.2
4	Product2	RM1	30%		Product2	1/05/2017	79.5
5	Product2	RM3	50%		Product1	1/05/2017	60.3
6	Product2	RM2	20%
7
8	Date	RM names	Rate
9	1/04/2017	RM1	100
10	1/04/2017	RM2	50
11	1/04/2017	RM3	70
12	1/05/2017	RM1	110
13	1/05/2017	RM2	45
14	1/05/2017	RM3	75
two tables multiple conditions

Cell Formulas
Range	Formula
G2:G5	G2	=SUMPRODUCT(SUMIFS(C$9:C$14,B$9:B$14,B$2:B$6,A$9:A$14,F2),--(A$2:A$6=E2),C$2:C$6)

 

[nileshp]

Works perfectly. Thanks a lot.

 

[Peter_SSs]

Works perfectly. Thanks a lot.

You're welcome.

 

[MaddAddaM]

Try this, copied down.

Hi Peter,
Your answer seems to be rendering as HTML code instead of text. Is there a way to adjust that? I think your answer to NilesHp may be the solution I'm looking for on my problem.
Thanks!
Aaron

 

[Fluff]

That's due to the migration to the new software, which does not allow HTML. There are plans to deal with it, but will take some time.
In the mean time it looks like the formula is
SUMIFS(C$9:C$14,B$9:B$14,B$2:B$6,A$9:A$14,F2),--(A$2:A$6=E2),C$2:C$6)

 

[MaddAddaM]

That's due to the migration to the new software, which does not allow HTML. There are plans to deal with it, but will take some time.
In the mean time it looks like the formula is
SUMIFS(C$9:C$14,B$9:B$14,B$2:B$6,A$9:A$14,F2),--(A$2:A$6=E2),C$2:C$6)

Thanks so much. Did the ranges in the formula refer to an Excel table that had been attached. Or are they referencing how the original post is laid out above?

 

[Peter_SSs]

I think it was actually like this.

A_MrExcel.xlsm
	A	B	C	D	E	F	G
1	Product name	RM names	%age		Product	Date	Cost
2	Product1	RM2	49%		Product2	1/04/2017	75
3	Product1	RM3	51%		Product1	1/04/2017	60.2
4	Product2	RM1	30%		Product2	1/05/2017	79.5
5	Product2	RM3	50%		Product1	1/05/2017	60.3
6	Product2	RM2	20%
7
8	Date	RM names	Rate
9	1/04/2017	RM1	100
10	1/04/2017	RM2	50
11	1/04/2017	RM3	70
12	1/05/2017	RM1	110
13	1/05/2017	RM2	45
14	1/05/2017	RM3	75
Sumproduct

Cell Formulas
Range	Formula
G2:G5	G2	=SUMPRODUCT(SUMIFS(C$9:C$14,B$9:B$14,B$2:B$6,A$9:A$14,F2),--(A$2:A$6=E2),C$2:C$6)