Sum Product Formula: Array of Numbers Returns 0 Instead of Sum

Understanding the SumProduct Formula and Array Numbers that Return 0

The SumProduct formula is a powerful tool in Excel that allows users to perform complex calculations on arrays of data. However, there are situations where the formula may return an unexpected result, such as 0. In this article, we will explore the possible reasons why this might happen and provide solutions to the problem.

What is the SumProduct Formula?

The SumProduct formula is used to multiply arrays and then sum the results. It has the following syntax:

Where array1, array2, and so on are arrays of data that you want to multiply and sum. The formula can also include criteria arrays, which allow you to perform conditional calculations.

Why Does the SumProduct Formula Return 0?

The SumProduct formula can return 0 for several reasons. One common reason is that one or more of the arrays being multiplied contain only zero values. In this case, the formula will return 0, even if there are non-zero values in other arrays.

Another reason the SumProduct formula may return 0 is if the arrays being multiplied contain text values. The SumProduct formula only works with numerical data, so if there are text values in the arrays, the formula will return 0.

Using the SumProduct Formula with Array Numbers

In the question provided, the user is using the SumProduct formula with an array of numbers, but the formula is returning 0. The initial formula looks like this:

This formula is designed to sum only the values in the range E3:E53 that are not formulas. The NOT(ISFORMULA()) function returns an array of TRUE/FALSE values, where TRUE indicates that the cell contains a value and FALSE indicates that the cell contains a formula. The * operator then multiplies this array by the range E3:E53, which returns an array of 0s and the values in E3:E53.

However, if there are no formulas in the range E3:E53, the NOT(ISFORMULA()) function will return an array of TRUEs, and the * operator will multiply this array by the range E3:E53, resulting in an array of 0s. The SumProduct formula will then sum this array and return 0.

Solutions to the Problem

To avoid the SumProduct formula returning 0, you can use one of the following solutions:

• Check the data in the range E3:E53 to ensure that there are no text values or zero values. If there are, remove or replace them with numerical data.
• Modify the formula to include a criteria array that filters out the zero values or text values. For example, you can use the following formula:
  =SUMPRODUCT((NOT(ISFORMULA(E3:E53)))*(E3:E53>0)*E3:E53)

  This formula includes an additional criteria array that filters out any values less than or equal to 0. The formula will then only sum the positive values in the range E3:E53.

The SumProduct formula is a powerful tool in Excel, but it can return unexpected results if not used correctly. By understanding the possible reasons why the formula may return 0, you can modify the formula or the data to ensure that the formula returns the expected result. With proper use, the SumProduct formula can help you perform complex calculations on arrays of data quickly and easily.

References

• SumProduct Function
• Sum Only Numbers with SumProduct
• Sum Only Positive Numbers with SumProduct