Share this content
Tags:

Use SUMPRODUCT to multiply ranges

9th Aug 2011
Share this content
AIA

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.

Excel SUMPRODUCT function

Excel's VLOOKUP function allows you to look up data from a table based on criteria that you specify, and has four arguments:

  • lookup_value – This is the data that you want to look for in the first column of the table array.
  • table array – The table array is a range of two or more columns.
  • col_index_num – This argument allows you to specify the column within the table array for which you want to return data.
  • range_lookup – In this position you indicate FALSE if you're seeking an exact match for the lookup_value, or TRUE if you want an approximate match. For instance, you'd use FALSE to look up the price of an inventory item, or TRUE if you're determining which tax bracket an income level falls into.

 VLOOKUP allows you to find a single value based on a single criteria

In this example, VLOOKUP returns $85,106 from Column E for the first instance of Apples that appears in Column D. The additional instances of Apples are ignored. If you need to add up multiple values based on specific criteria, then SUMIF is a better choice. This function has three arguments:

  • range – This is a column or row where you want to look for specified criteria.
  • criteria – This is the same as the lookup_value for VLOOKUP.
  • sum_range – This is a column or row from which you want to add up numbers whenever the criteria is found in the range.

SUMIF adds up multiple items, but is limited to a single criteria

Istead of using SUMIF in this way, however, SUMPRODUCT can be used to return the same results:

SUMPRODUCT can product the same result as SUMIF

In the SUMIF example (previous picture), the result is $396,495. In the lower SUMPRODUCT example, every time the formula found a match in Column D on the word Apples, it added up the corresponding value in Column E. Like VLOOKUP, SUMIF can only search based on a single criteria. Fortunately, SUMPRODUCT allows you to add up values based on multiple criteria.

However, let's first use SUMPRODUCT to match based on a single criteria, as shown in Figure 4. In this case it returns the same result as SUMIF because we only provided a single criteria.

In this next example, SUMPRODUCT returns $272,584 because we specified that we only want sales for apples sold in North Georgia:

SUMPRODUCT can return results based on multiple criteria

This SUMPRODUCT formula looks at Cells A2 through A19 for the words North GA, and at Cells D2 through D19 for the word Apples. When both criteria are met, SUMPRODUCT adds up the corresponding values from Cells E2 through E19.

SUMPRODUCT can return the number of matches that meet specified criteria

In the final example, if you only specify criteria and omit the range to sum, then SUMPRODUCT returns the number of items that match the criteria that you specify.

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.
Tags:

Replies (5)

Please login or register to join the discussion.

avatar
By DazedByTheLight
09th Aug 2011 13:36

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

Thanks (0)
avatar
By nogammonsinanundoubledgame
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
By henryg591
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)