Excel SUMPRODUCT Function: Cells Containing Double Quotes
The SUMPRODUCT function in Microsoft Excel is a powerful tool for performing complex calculations on arrays. In this article, we will focus on using the SUMPRODUCT function to calculate the sum of the product of two arrays, where one array contains cells with double quotes.
Context
In Excel, cells that contain double quotes are considered to be text values. When working with arrays that contain both numbers and text, the SUMPRODUCT function can be used to exclude cells with double quotes from the calculation. This is done by using the -- operator to convert the text values to zero, and then multiplying this array by the array of numbers. The result is that cells with double quotes are effectively ignored in the calculation.
Example
Consider the following example:
=SUMPRODUCT(A2:B2; $$29:$B$29) / SUMPRODUCT(--(A2:B2<>""); $$29:$B$29)
In this example, the SUMPRODUCT function is used to calculate the sum of the product of two arrays: A2:B2 and $$29:$B$29. However, before the multiplication takes place, the second array is modified using the -- operator and the != comparison operator. This results in an array of ones and zeros, where the zeros correspond to the cells in the first array that contain double quotes. By dividing the first SUMPRODUCT calculation by the second, the effect of the cells with double quotes is effectively removed from the calculation.
Simplified Version
A simplified version of this formula is as follows:
=SUMPRODUCT(A2:B2; $$29:$B$29) / SUMPRODUCT(--(A2:B2<>""))
In this version, the second SUMPRODUCT calculation only includes the array of ones and zeros, rather than the second array of numbers. This has the same effect of excluding cells with double quotes from the calculation, but with a simpler formula.
The SUMPRODUCT function in Excel is a powerful tool for performing calculations on arrays. By using the -- operator and the != comparison operator, cells with double quotes can be excluded from the calculation, resulting in a more accurate result. The simplified version of the formula provides a more straightforward way of achieving the same result.
References
- Microsoft Excel Help: SUMPRODUCT function
- ExcelJet: SUMPRODUCT with IF and ISNUMBER
- Chandoo: 10 examples of using SUMPRODUCT formula in Excel