Use SUMPRODUCT to multiply ranges

Kashflow logo
Share this content

Last month, David H Ringstrom discussed ways in which Excel formulae using SUM functions could be extended by using multiple criteria. This article explains how similar results can be achieved using the SUMPRODUCT function.

Excel's SUMPRODUCT function is one of the spreadsheet program’s less obvious jewels, but those in the know are well aware of its usefulness.

Put very simply, SUMPRODUCT multiplies two ranges together to return a compound total.

As demonstrated below, a single SUMPRODUCT formula can replace the eight formulas listed in Cells D2 through D9. The function works by multiplying the values in Column B by the values in Column C on a row-by-row basis, and then sums the total.

This basic capability is useful, but SUMPRODUCT also can operate like a souped-up version of  VLOOKUP or SUMIF.

About the author
"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for AccountingWEB partner CPE Link and contributes articles on Excel to Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com.

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About David H Ringstrom

Replies

Please login or register to join the discussion.

avatar
09th Aug 2011 13:36

Well-presented article, really clear and easy to understand. Thanks 

Thanks (0)
avatar
10th Aug 2011 08:42

It is worth being aware of the odd bug in Excel 2007

This may have been corrected in Excel 2010 (likewise I think no problems in versions prior to 2007), but if you are wont to chop and change between using a comma separator and multiplier separator willy nilly in 2007 it is possible to run into problems:

http://spreadsheet-toolbox.com/library/peculiarities/sumproduct-bug-in-excel-2007/

I don't think that this is the only problem with 2007, so if you are on that version and need reliable output I would suggest upgrading to 2010 (or downgrading to 2003).  Others may disagree.

With kind regards

Clint Westwood

Thanks (0)
avatar
10th Aug 2011 13:10

Excellent

I had never thought of using Sumproduct in most of these ways

Thanks (0)
avatar
By Evamuse
12th Aug 2011 11:14

 
Very useful article

 

Very useful article

Thanks (0)
avatar
By ACDWebb
12th Aug 2011 15:54

Certainly easier than using Array formulae

Worth noting that SUMIFS does the same thing in Excel 2010 (and possibly 2007, not sure as I want from 2003 to 2010)

Thanks (0)

Related content