Lesson 112: How to use the Sumproduct function in Excel

In this article, I will show you how to use the Sumproduct function to calculate the total amount of a product in Excel.

How to use the Sumproduct function in Excel

1. For example, the SUMPRODUCT function below calculates the total amount spent.

Explanation: the SUMPRODUCT function does this calculation: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.

2. The regions must be the same size or Excel will display the #VALUE! error.

3. The SUMPRODUCT function will specify non-numeric cells to have a value of 0.

4. If you provide a unique range, the SUMPRODUCT function will produce the exact same results as the SUM function.

Advanced

The SUMPRODUCT function is an extremely versatile function and can produce the same results as many of Excel’s built-in functions and even array formulas!

1a. For example, the COUNTIF function below counts the number of cells containing “star”.

1b. The SUMPRODUCT function below has the same result.

1 C. The COUNTIF function below counts the number of cells containing the word “star” + 1 character. The question mark (?) represents 1 character.

1d. The SUMPRODUCT function is not perfect! You cannot use wildcard characters (? and *) when using the SUMPRODUCT function.

2 a. The array formula below counts the number of characters in a range of cells.

2b. The SUMPRODUCT function below has the same result.

3a. The array formula below calculates total sales in 2018.

3b. The SUMPRODUCT function below has the same result.

In addition, you can also see many other excel articles here.