Using the SUMPRODUCT function in Excel

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:

 

sumproduct2

 

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(B5:B7,C5:C7)

 

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.

 

sumproduct3

 

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)).

Leave a Reply

Your e-mail address will not be published. Required fields are marked *