SUMPRODUCT is perfect formula if we have two or more arrays (range of cells in column/row) and you want to find the total value of the columns multiplied together.
Syntax: =SUMPRODUCT(array1, [array2], [array3],...,[array255])
- array1: Data in the first column/row
- array2: Data in the second column/row and so on.
SUMPRODUCT formula can accommodate up to 255 arrays.
IMPORTANT NOTES:
- All the arrays should have same number of data entries. You can't have array1 containing 13 cells in a column, and array2 having only 12 cells. This will return #VALUE! error.
- Cells containing text OR blank cells are treated as zeroes by SUMPRODUCT function.
