For example, in the worksheet below, you can use SUMPRODUCT to get the total of all numbers in column F without using column F at all: The "classic" SUMPRODUCT example illustrates how you can calculate a sum directly without a helper column. The bottom line is that SUMPRODUCT is a safer option if a worksheet will be used in any version of Excel before Excel 365, even if the worksheet was created in Excel 365. However, if the same formula is opened in an earlier version of Excel, it will require control + shift + enter. This means you can often use the SUM function in place of SUMPRODUCT in an array formula with the same result and no need to enter the formula in a special way. In Excel 365, the formula engine handles arrays natively. Using SUMPRODUCT means the formulas will work in any version of Excel without special handling. This means if someone forgets to use CSE when checking or adjusting a formula, the result may suddenly change, even though the actual formula did not change. One problem with array formulas is that they usually return incorrect results if they are not entered with control + shift + enter. This is a key reason that SUMPRODUCT has been so widely used to create more advanced formulas. The SUMPRODUCT function can be used to create array formulas that don't require control + shift + enter.
This is a confusing topic, but it must be addressed. Because it will handle arrays gracefully, you can use it to process ranges of cells in clever, elegant ways. But SUMPRODUCT is an amazingly versatile function with many uses. When you first encounter SUMPRODUCT, it may seem boring, complex, and even pointless.
Up to 30 ranges or arrays can be supplied. The SUMPRODUCT function multiplies arrays together and returns the sum of products. If only one array is supplied, SUMPRODUCT will simply sum the items in the array.