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

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: