The SUMPRODUCT function can be used to multiply an array of cells and then sum the total of those calculations.
To demonstrate, firstly here is an example of the long way of doing it, without SUMPRODUCT:
To calculate total number of chocolate bars sold, each row is calculated one-by-one and then all the totals summed.
Instead, the total of 88 could have been found with a simple formula of:
SUMPRODUCT becomes even more useful when there are multiple fields involved. Here are two examples, the first calculating the total number of sales completed by the North region and the second calculating number of sales completed in the North region and by salesperson Smith.
For the North the formula was =SUMPRODUCT(–(C5:C14=”North”),E5:E14).
For the North region and Smith it was =SUMPRODUCT(–(C5:C14=”North”),–(D5:D14=”Smith”),(E5:E14)).