Sumproduct

%3CLINGO-SUB%20id%3D%22lingo-sub-2349962%22%20slang%3D%22en-US%22%3ESumproduct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2349962%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20keep%20getting%20a%20%23Value%20error%20with%20a%20simple%20Sumproduct%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20I%20doing%20wrong%3F%26nbsp%3B%20Does%20the%20order%20of%20the%20arrays%20matter%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2349962%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2350182%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2350182%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047084%22%20target%3D%22_blank%22%3E%40MichaelJames652%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20SUMPRODUCT%20the%20array%20arguments%20must%20have%20the%20same%20dimensions.%20You%20are%20trying%20to%20multiply%20and%20sum%20an%20arrays%20of%209x9%20with%20on%20of%201x9%20and%20one%20of%209x1.%20Hence%2C%20%23VALUE!%3C%2FP%3E%3CP%3EPerhaps%20the%20following%20formula%20in%20G12%20does%20what%20you%20need.%20Copy%20down%20and%20across.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(Source!%24H%2413%3A%24P%2421%2CMATCH(%24D12%2CSource!%24C%2413%3A%24C%2421%2C0)%2CMATCH(G%2410%2CSource!%24H%249%3A%24P%249%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

 

I keep getting a #Value error with a simple Sumproduct formula.

 

What am I doing wrong?  Does the order of the arrays matter?

 

Thank you.

2 Replies

@MichaelJames652 

With SUMPRODUCT the array arguments must have the same dimensions. You are trying to multiply and sum an arrays of 9x9 with on of 1x9 and one of 9x1. Hence, #VALUE!

Perhaps the following formula in G12 does what you need. Copy down and across.

=INDEX(Source!$H$13:$P$21,MATCH($D12,Source!$C$13:$C$21,0),MATCH(G$10,Source!$H$9:$P$9,0))

 

@MichaelJames652 

If with SUMPRODUCT()

=SUMPRODUCT(
  Source!$H$13:$P$21,
  MMULT(--(Source!$C$13:$C$21=$D12),
        --(Source!$H$9:$P$9=G$10))
)