Multiply 2 SUMPRODUCT arrays with a 3rd array of different size

[cgcamal]

Hi everyone,

I know if I multiply arrays of different size the result is an error, but in this case is something like to try to generate
an array of the same size that those within the SUMPRODUCT formula.

I have the data and formula as you can see below. The formula looks for values in column D that match within column A and
sums their respective value in column B. The formula I have in G2 does that fine, but I still don't know how to multiply the
respective Quantity in column E by value in B of selected Type that is matched in A.

In the example below, the result should be {3;5;2}{2;1;4}=19 as in H2, the thing is the original arrays in SUMPRODUCT
contain 10 elements and not 3.

* The values in D and E change depending which Types select users and how many of each one they buy(quantity).

Excel Workbook
	A	B	C	D	E	F	G	H
1	Type Available	Value		Types Selected	Quantity		Sub Total	Total
2	Type1	1		Type2	2		10	19
3	Type2	3		Type5	1
4	Type3	4		Type9	4
5	Type4	1
6	Type5	5
7	Type6	1
8	Type7	5
9	Type8	6
10	Type9	2
11	Type10	1
...

Excel 2010

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(--(ISNUMBER(MATCH(INDEX($A$2:$B$11,,1),D$2:D$4,0))),INDEX($A$2:$B$11,,2))

Many thanks in advance for any help.

 

[mikerickson]

In G2, the formula =SUMPRODUCT(SUMIF(A2:A11,D2:D4,B2:B11))
In H2, the formula =SUMPRODUCT(SUMIF(A2:A11,D2:D4,B2:B11), E2:E4)

 

[Aladin Akyurek]

Hi everyone,

I know if I multiply arrays of different size the result is an error, but in this case is something like to try to generate
an array of the same size that those within the SUMPRODUCT formula.

I have the data and formula as you can see below. The formula looks for values in column D that match within column A and
sums their respective value in column B. The formula I have in G2 does that fine, but I still don't know how to multiply the
respective Quantity in column E by value in B of selected Type that is matched in A.

In the example below, the result should be {3;5;2}{2;1;4}=19 as in H2, the thing is the original arrays in SUMPRODUCT
contain 10 elements and not 3.

* The values in D and E change depending which Types select users and how many of each one they buy(quantity).

Excel Workbook
	A	B	C	D	E	F	G	H
1	Type Available	Value		Types Selected	Quantity		Sub Total	Total
2	Type1	1		Type2	2		10	19
3	Type2	3		Type5	1
4	Type3	4		Type9	4
5	Type4	1
6	Type5	5
7	Type6	1
8	Type7	5
9	Type8	6
10	Type9	2
11	Type10	1
...

Excel 2010

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(--(ISNUMBER(MATCH(INDEX($A$2:$B$11,,1),D$2:D$4,0))),INDEX($A$2:$B$11,,2))

Many thanks in advance for any help.

Try...

=SUMPRODUCT(SUMIF(D2:D4,A2:A11,E2:E4),B2:B11)

 

[cgcamal]

Many thanks for your help Mike and Aladin, much much more easy that I was trying and thinking .

Much appreciated.

Best regards.