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