SUMPRODUCT Function
The Excel SUMPRODUCT function is a hidden gem. It is used to sum a range of cells that meet multiple criteria. The release of Excel 2007 saw the introduction of the SUMIFS function, but until then you have SUMPRODUCT.
The syntax for the SUMPRODUCT function is:
=SUMPRODUCT(array1, [array2], [array3], ...)
Argument |
Purpose |
|---|---|
Array |
Each range of cells that contains the criteria and the cells you want to sum. Each array must be of the same dimensions |
The SUMPRODUCT function works by multiplying a group of numbers and then adding the results.
The example below shows the SUMPRODUCT function being used to sum all the seafood orders from the USA.
Lets break that function down for a closer look;
- The (A2:A14="USA") part of the function will store a 1 whenever USA is mentioned in the array and a 0 if anything else
- The (C2:C14="Seafood") part of the function will store a 1 whenever Seafood is mentioned in the array and a 0 if anything else
- The D2:D14 part of the function returns all the sales totals
- All together the resulting calculation goes something like this:
(0*0*440 + 0*0*1863.40 + 0*0*1552.60 + 0*1*654.06 + 1*1*3597.90 .....)
This goes on through the complete array and ends up adding only those records that meet the criteria of seafood sold in the USA. You can see the first record being picked up at the end of the calculation above illustrated in bold.
Another alternative to using the SUMPRODUCT function would be to use a PivotTable.
Download the Excel Formulas Ebook for a fast and simple guide to the most useful Excel formulas.
Follow us on
