Sumproduct separators

[hawaean]

Hi all,

Just a quick Excel question on sumproduct functions. Are there certain cases where you use "," verses "*" between criterias? Does it matter?

 

[joeu2004]

Just a quick Excel question on sumproduct functions. Are there certain cases where you use "," verses "*" between criterias? Does it matter?

Not entirely, most of the time. Often, it is a personal preference. But there are instances where "*" must be used.

The form of the SUMPRODUCT usage is: SUMPRODUCT(array1, array2, array3,....). Each "array" is called a parameter.

Alternatively, we could write SUMPRODUCT(array1*array2, array3), and that is usually equivalent.

Some exceptions:

1. If array2 has nonnumeric text, array1*array2 will result in a #VALUE error. On the contrary, SUMPRODUCT(array1, array2, array3) will work because SUMPRODUCT ignores elements of parameters that are text. Caveat: SUMPRODUCT also ignores numeric text in array2. So if array2 contains only numeric values and numeric text, array1*array2 is the correct choice if you intend for SUMPRODUCT to include the numeric text in the calculation.

2. if array1 is a column, array2 is a row, and array3 is 2-dimensional, we must write SUMPRODUCT(array1*array2, array3) because array1*array2 produces a 2-dimensional array. If we write SUMPRODUCT(array1, array2, array3), that produces a #VALUE error because the parameters are not the same shape.

Some examples where the use of "," or "*" does not matter much:

SUMPRODUCT(--(A1:A100=X1), --(B1:B100>Y1), C1:C100)
or
SUMPRODUCT((A1:A100=X1)*(B1:B100>Y1), C1:C100)

If you prefer the first form, it is usually fine to use what you prefer.

Technically, the second form is more efficient, especially when the ranges are very much larger.

The first form creates 2 temporary arrays, which must persist while SUMPRODUCT does its calculations.

The second form collapses the 2 temp arrays into one array that must persist while SUMPRODUCT does its calculations.

[EDIT] On the other hand, the second form creates 3 temp arrays while processing the first two parameters.

Also, the first form might perform 4 additional arithmetic operations (the double negates) than the second form.

[EDIT] On the other hand, Excel might be clever enough to recognize double negate, and it might not preform an arithmetic operation at all; just the required transformation to numeric values.

That said, there might be a performance trade-off between doing the multiplication during parameter processing v. multiplication internally while SUMPRODUCT does its calculations.

That is why it is better to write the formula according to your preference: it is too difficult and questionable to predict any performance preference.

 

[Aladin Akyurek]

The comma syntax is native to SUMPRODUCT. The star syntax has been probably introduced by Longre by analogy to an array-processing SUM formula:

{=SUM(A2:A10*(B2:B10="X"))}

Thus:

=SUMPRODUCT(A2:A10*(B2:B10="X"))

while respecting the native comma syntax, we would have something like:

either...

=SUMPRODUCT(A2:A10,(B2:B10="X")+0)

or

=SUMPRODUCT(A2:A10,--(B2:B10="X"))

+0 and -- are so-called coercers which change TRUE and FALSE (result) values to 1 and 0. Coercion is necessary because SUMPRODUCT requires numbers.

Note that (B2:B10="X")*1 or 1*(B2:B10="X") or 0+(B2:B10="X") or (B2:B10="X")^1, etc. all can effect the required coercion.

1. What is the most efficient, the comma syntax or the star syntax? Most of the (early) assessments indicate the comma syntax as faster. Some people recently shows signs of a reversal to the star syntax.

2. One obvious disadvantage of the star syntax is that it does not tolerate text (including blanks) in the sum range. That is,

=SUMPRODUCT(A2:A10*(B2:B10="X"))

will fail if A2:A10 has any blank or other text, while SUMPRODUCT natively does not care just like SUM.