Below is an example of the SUMPRODUCT function. SUMPRODUCT multiplies corresponding cells from multiple arrays and returns the sum of the products. It does both a sum and a product.

Here is another example that is more simple than the one above.

Counting Duplicates Between Two Lists

We want to know how many duplicates there are when we compare the two lists. We’ll use COUNTIF() and SUMPRODUCT() to do this.

SUMPRODUCT can take a single cell, an array or many arrays, as shown by the examples below.

SUMPRODUCT can even take a single cell.

SUMPRODUCT is similar to DAX’s SUMX function.