Simon Hurst turns his attention to standard Excel functions such as SUMIF and SUMPRODUCT that make it easier to produce summary totals.
Having covered some of the major new Excel tools for accessing and manipulating accounting data in previous articles, this time we will concentrate on some standard Excel functions and how they can be used to summarise accounting transactions by date.
We'll start with the SUMIF() function. SUMIF() has the advantage of being available in all recent versions of Excel, unlike the similar SUMIFS() function which was only introduced in Excel 2007.
As the name of the function suggests, SUMIF() will sum a range of cells if a condition is met. If we had a set of transactions with dates and we wanted the total for all the transactions on a particular date, the formula would be relatively straightforward. SUMIF() uses either 2 or 3 arguments. If you want to base your condition on the same data that you want to sum, then you only needed to use the first 2 arguments. Often you will want to sum one range dependent on the values in a different range and for this you will also need to use the optional third, [sum_range], argument.
In this example we have created an External Data Range linked to four columns from the Northwind Invoice table. Because the External Data Range is created as an Excel Table in Excel 2007 and later, we can use Table structured references. We have used the Table Name: option in the Table Tools, Design ribbon tab to give our Table the name Invoices. When we start typing the name of our Table in an Excel formula, Excel will display an AutoComplete list including functions, Range Names and Table names that match what has been typed. Having selected our Table name from the list, we can enter an opening square bracket to display the column names available in our Table.
Register for free with AccountingWEB and log in to read the full article, which also covers:
- Boolean logic
- SUMPRODUCT() and array formulae v. SUMIF() and SUMIFS()
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.