how to use SUMPRODUCT with 2 arrays that are different in range size

[Coffeetoday]

Hello all!

New to MrExcel and hoping to get answer to my question

Have a monthly sales worksheet with the dates in top row and products in first column.

I the sample of the sheet below is clear.

What I am trying to do is figure out the use of "Type" on a daily basis.
To do that I have to multiply the QTY*Shots then add the result for each "Type".

After long search and using the SUMPRODUCT, I was able to get the following 2 formulas to work.
(To sum the QTY of specific Type for Mar 1
SUMPRODUCT((Sales!F190:CT190=A3)*(B2=Sales!C192:C282)*Sales!F192:CT282)

(To Sum the number of shots for the same specific type)
SUMPRODUCT((Sales!C192:C282=B2)*Sales!D192:D282)

However, when I combine them together as below, I get #VALUE! error
=SUMPRODUCT((Sales!F190:CT190=A3)*(Sales!C192:C282=B2)*Sales!F192:CT282,(Sales!C192:C282=B2)*Sales!D192:D282)

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1-Mar-2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item[/TD]
[TD]Type[/TD]
[TD]Shots[/TD]
[TD]SP[/TD]
[TD]Quantity[/TD]
[TD]Discount[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]PRD1[/TD]
[TD]TD-1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PRD2[/TD]
[TD]TD-3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PRD3[/TD]
[TD]TD-4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PRD4[/TD]
[TD]TD-4[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PRD5[/TD]
[TD]TD-1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PRD6[/TD]
[TD]TD-3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 630"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please help!

 

[barry houdini]

Hello Coffeetoday, welcome to MrExcel

Does this work for you?

=SUMPRODUCT((Sales!F190:CT190=A3)*(B2=Sales!C192:C282)*Sales!D192:D282,Sales!F192:CT282)

 

[Coffeetoday]

Hello Coffeetoday, welcome to MrExcel

Does this work for you?

=SUMPRODUCT((Sales!F190:CT190=A3)*(B2=Sales!C192:C282)*Sales!D192:D282,Sales!F192:CT282)

Worked GGGReat!

Many thanks!