SUMPRODUCT help

[HondaTiger]

Can someone explain why

=SUMPRODUCT((D4:D7=D10)*(E4:G7))

that formula works (where E4:G7 spans rows and columns)

and this formula does not?

=SUMPRODUCT((C4:C7=C13)*(D4:D7=D13),(E4:G7))

the second formula works if I change it to E4:E7, but I need to
include the entire table not just that one row.

aaa.xls
	B	C	D	E	F	G
3				category1	category2	category3
4		row1	type1	5	1	9
5		row2	type2	6	2	10
6		row3	type1	7	3	11
7		row4	type3	8	4	12
8
9
10	anycategory	anyrow	type1	36
11	anycategory	anyrow	type2	18
12
13	anycategory	row1	type1	#VALUE!
14	anycategory	row2	type1	0
Sheet1

 

[NBVC]

Why not follow the same logic as your first sample and place an * before the last range.

=SUMPRODUCT((C4:C7=C13)*(D4:D7=D13)*(E4:G7))

 

[Andrew Poulsom]

This works:

=SUMPRODUCT((C4:C7=C13)*(D4:D7=D13)*(E4:G7))

In the example you posted there a 2 arrays (because of the comma):

{1;0;0;0}
{5,1,9;6,2,10;7,3,11;8,4,12}

Because these aren't equal in size the formula returns #VALUE!.

In the formula above each array is multiplied to create one array:

{5,1,9;0,0,0;0,0,0;0,0,0}

and in the process the first 2 arrays are used 3 times (the number of columns in the last array).

 

[HondaTiger]

Thanks guys!