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.