SUMPRODUCT problem

[smallxyz]

The following formula doesn't work. Why??
Thanks.


=SUMPRODUCT({D2;E2;F2},{C5,C6,C7})


=SUMPRODUCT(TRANSPOSE(D2:F2),{C5,C6,C7})

 

[Aladin Akyurek]

Aladin, interestingly enough, the following two formulas turn up the same result with CSE:

=SUM(TRANSPOSE(D2:F2)*C5:C7)

=SUMPRODUCT(TRANSPOSE(D2:F2)*C5:C7)

The * syntax should be avoided with SUMPRODUCT.

If there are no formulas in the ranges of interest, these all work perfectly:

{=SUM(TRANSPOSE(D2:F2)*C5:C7)}

{=SUMPRODUCT(TRANSPOSE(D2:F2)*C5:C7)}

=MMULT(D2:F2,C5:C7)

{=SUMPRODUCT(TRANSPOSE(D2:F2),C5:C7)}

The OP is advised to opt for the MMULT version taxcel rose suggests, which exploits the layout.


In case of formula-ridden ranges, the SUMPRODUCT version with the comma syntax should be the formula of choice (for it would ignore e.g. formula blanks).

 

[smallxyz]

Thank you all. Problem solved!
I once believe the unnecessary need to use Control + Shift + Enter in Sumproduct together with Transpose.
Turns out not the case. Seems that when to use CSE in sumproduct has no general rule?

 

[inactiveuserps07]

You don't need CSE with SUMPRODUCT as long as the ranges involved are parallel AND have the same beginning and ending (e.g., C2:E2 with C5:E5 is OK; C2:C5 with H2:H5 is OK). In other words, if you could draw a neat rectangle around the ranges involved, and they both would either touch the sides, or the top and bottom, then you're OK without CSE. Any other time, you need CSE.

Hope that makes sense.

 

[Aladin Akyurek]

Thank you all. Problem solved!
I once believe the unnecessary need to use Control + Shift + Enter in Sumproduct together with Transpose.
Turns out not the case. Seems that when to use CSE in sumproduct has no general rule?

SUMPRODUCT is an array-processing function like a few others, e.g. LOOKUP and FREQUENCY.

When functions like IF, TRANSPOSE, etc. are nested within another function, we need CSE...

IF(A2:A5="X",B2:B5)

means: yield all B-values which correspond to an X in A.

Let's call the result of these evaluations (every A is tested against being X) ArrayResult. This result will obviously consists of something like if B's are numbers:

{3;FALSE;FALSE;1.5}

If we would want to count those TRUE's (i.e. B-numbers), we could do:

=COUNT(IF(A2:A5="X",B2:B5))

This requires CSE to succeed. Excel is designed such that it requires an explicit confirmation for that ArrayResult.

TRANSPOSE is like the foregoing IF. Excel rquires an explicit confirmation for the ArrayResult which would contain transposition from horizontal to vertical or vice versa. Embedded in a SUMPRODUCT call, we will not succeed without the CSE for SUMPRODUCT <> CSE. That is, it's an array-processing function but it is not CSE.