Why does this SumProduct get a Value error?

[JenniferMurphy]

Can anyone tell me why the Avg 2 column gets a value error. It seems to me that it should be the same as the one in the Avg 1 column. And a different version in e# also gets a value error. But the one in E6 works. Is SumProduct not capable of multiplying a horizontal range and a vertical one?

SumProduct.xlsx
	B	C	D	E	F	G	H
2	Weight	Ranks
3	1	Rank 1		#VALUE!
4	1	Rank 2
5	1	Rank 3		1	1	1	1
6	1	Rank 4		6
7	4	Weight sum
8
9	Team	Avg 2	Avg 1	Rank 1	Rank 2	Rank 3	Rank 4
10	A	#VALUE!	1.50	1	2	1	2
11	B	#VALUE!	2.25	2	1	3	3
12	C	#VALUE!	2.50	3	4	2	1
13	D	#VALUE!	3.75	4	3	4	4
Table Columns

Cell Formulas
Range	Formula
E3	E3	=SUMPRODUCT(E10:H10,B3:B6)
E6	E6	=SUMPRODUCT(E10:H10,E5:H5)
B7	B7	=SUM(Weights)
C10:C13	C10	=SUMPRODUCT(Table1[@[Rank 1]:[Rank 4]],Weights)/WtSum
D10:D13	D10	=([@[Rank 1]]*WtRank1 + [@[Rank 2]]*WtRank2 + [@[Rank 3]]*WtRank3 + [@[Rank 4]]*WtRank4) / WtSum

Named Ranges
Name	Refers To	Cells
'Table Columns'!Weights	=OFFSET('Table Columns'!WtHdr,1,0):OFFSET('Table Columns'!WtSum,-1,0)	B7, E3, C10:D13
'Table Columns'!WtHdr	='Table Columns'!$B$2	C10:C13, B7
'Table Columns'!WtRank1	='Table Columns'!$B$3	B7, E3, C10:D13
'Table Columns'!WtRank2	='Table Columns'!$B$4	B7, E3, C10:D13
'Table Columns'!WtRank3	='Table Columns'!$B$5	B7, E3, C10:D13
'Table Columns'!WtRank4	='Table Columns'!$B$6	B7, E3, C10:D13

 

[JenniferMurphy]

I got it to work with Transpose. I thought I tried it and failed, but now it works. Sorry for the bother.

SumProduct.xlsx
	B	C	D	E	F	G	H
2	Weight	Ranks
3	1	Rank 1
4	1	Rank 2
5	1	Rank 3
6	1	Rank 4
7	4	Weight sum
8
9	Team	Avg 2	Avg 1	Rank 1	Rank 2	Rank 3	Rank 4
10	A	1.50	1.50	1	2	1	2
11	B	2.25	2.25	2	1	3	3
12	C	2.50	2.50	3	4	2	1
13	D	3.75	3.75	4	3	4	4
Table Columns

Cell Formulas
Range	Formula
B7	B7	=SUM(Weights)
C10:C13	C10	=SUMPRODUCT(Table1[@[Rank 1]:[Rank 4]],TRANSPOSE(Weights))/WtSum
D10:D13	D10	=([@[Rank 1]]*WtRank1 + [@[Rank 2]]*WtRank2 + [@[Rank 3]]*WtRank3 + [@[Rank 4]]*WtRank4) / WtSum

Named Ranges
Name	Refers To	Cells
'Table Columns'!Weights	=OFFSET('Table Columns'!WtHdr,1,0):OFFSET('Table Columns'!WtSum,-1,0)	B7, C10:D13
'Table Columns'!WtHdr	='Table Columns'!$B$2	C10:C13, B7
'Table Columns'!WtRank1	='Table Columns'!$B$3	B7, C10:D13
'Table Columns'!WtRank2	='Table Columns'!$B$4	B7, C10:D13
'Table Columns'!WtRank3	='Table Columns'!$B$5	B7, C10:D13
'Table Columns'!WtRank4	='Table Columns'!$B$6	B7, C10:D13

 

[Peter_SSs]

Can anyone tell me why the Avg 2 column gets a value error.

The first bullet point in 'Remarks' of the SUMPRODUCT Help covers it.

 

[JenniferMurphy]

The first bullet point in 'Remarks' of the SUMPRODUCT Help covers it.

That bullet point just says that it cannot do it. It unhelpfully does not provide any help for ways to get it to work, such as using Transpose.

 

[Peter_SSs]

That bullet point just says that it cannot do it.

The bullet point tells you why you got the #VALUE, which is your thread title and your initial question that I quoted in my post & the one I was providing an answer to.