## SUMPRODUCT v SUMIFS

David H Ringstrom has written a series of articles for the Excel section of www.accountingweb.co.uk. One such page, which is well worth reading, is the one on SUMPRODUCT: http://www.accountingweb.com/topic/excel/some-unlikely-uses-sumproduct. In that article David shows us how SUMPRODUCT can in reality emulate the powerful but often misunderstood VLOOKUP and HLOOKUP functions.

However, I think that whilst there is nothing intrinsically wrong with David’s approach I would recommend everyone who is using Excel 2007 to start thinking about the new SUMIFS function.

David’s example is contained in an Excel 2007 spreadsheet that I have built from the information provided in David’s article and that you can download by clicking the link at the end of this article. I have taken David’s SUMPRODUCT ideas and programmed them using his information. In parallel I have also programmed my version, the SUMIFS version.

The Excel 2007 write ups tell us that their new functions, such as SUMIFS require less space, less memory and so on and are therefore more efficient. The comparison between the two functions I am discussing here are clearly shown when I asked the two functions to add together sales based on

Region and City and Chain and Product

Here are my results:

=SUMPRODUCT((A2:A19=A22)(B2:B19=B22)(C2:C19=C22)(D2:D19=D22)E2:E19)

=SUMIFS(E2:E19,A2:A19,A25,B2:B19,B25,C2:C19,C25,D2:D19,D25)

You can see the programming for SUMIFS is shorter and perhaps easier to manage than the programming for SUMPRODUCT: the asterisk in this function is often mistaken for multiplication rather than the addition or adding that it really represents.

Click sumprod_sumifs to download my Excel file relating to this page.

Duncan Williamson

### 6 Responses to “SUMPRODUCT v SUMIFS”

1. Duncan,

Thanks for the great comparison! I wholeheartedly agree that SUMIFS is superior and easier to craft than SUMPRODUCT. However, I wrote-up SUMPRODUCT for situations where you don’t know categorically if an end-user might still be using Excel 2003 or earlier.

David

2. Fair point David and thanks for taking the time to come here and comment.

3. Judy Says:

One other thing to note is that Sumproduct can be used in situation where Sumifs cannot.

4. David Says:

I agree with Judy. SUMIF and SUMIFS work in your example with just one column of numbers, and I agree that they are preferable to using SUMPRODUCT.

Where SUMPRODUCT comes into its own is when you want to find the product of two or more columns, but want to use one or more criteria to decide which rows to include, as shown in this article on SUMPRODUCT:

http://fiveminutelessons.com/learn-microsoft-excel/use-sumproduct-multiply-two-columns-and-then-add-results

5. SUMPRODUCT can also be used in instances where you want to validate your source eg use only part of the data like entries beginning with a certain character, which again you can use an reference for.
I use IF functions as one of the criteria in SUMPRODUCT. So crucially your source of comparisons for validating your arrays to multiply do not have to be explicitly stated. You can make the conditions as complicated as you like.
I also use Dynamic Named Ranges to make the formulae react to additions to the fields. I have often seen VLOOKUP of SUMIF used to refer to entire columns. The formula will then work to the last row which has been active, the”end” of the sheet. This means an accidental entry a million lines down slows the whole thing down to a crawl, and nobody can see why. The dynamic named ranges stop the lookups at the end of the data, and prevent this kind of fault. They also help keep the formula short and easy to read.

6. koenvdg Says:

Also, sumif and sumifs require the reference to either be in the same workbook or open (if it is another workbook); sumproduct does not have that limitation

This site uses Akismet to reduce spam. Learn how your comment data is processed.