Accountancy resources

By

Accountancy resources

How to summarise data with SUMIF() in Microsoft Excel

2 Dec 2013
aat comment

The SUMIF() is a powerful function in Microsoft Excel that adds up numbers across categories. Never heard of it? Paolo Lenotti of Excel with Business is on hand to show you how

If you are studying AAT, you will almost certainly come across the SUMIF() function in Excel. The best way to explain how it works is to use an example. In this case I am going to imagine we are in charge of three shops selling soft drinks. The drinks are sold in different flavours and pack sizes, and we have a database showing their sales – by flavour, by pack size and by store location:

How to use SUMIF() to summarise data

In this example we might want to show the total sales, across all stores and flavours, for a particular pack size. To do this we just need to give the SUMIF() function three inputs:

  • The range of cells containing the pack-size information
  • The particular pack-size we want to find the total for (let’s say it’s a one litre bottle, labelled ‘1L’ in the data)
  • The range of cells, corresponding to the pack size information but containing the sales data.

Generally the syntax for SUMIF() is:

SUMIF(<range of entries to match against>,<match text>,<range of numbers to sum>)

So in this case we’d use the formula:

This adds the Sales figures in column D if the text in column B matches the “1L” specified in the formula in F2.

The function comes into its own when you replace the criteria text – ‘1L’ in this case – with a cell containing the text. Then it’s quick and simple to produce a summary table from the data:

The same sort of summary table, showing total sales for each pack size, could have been produced as quickly using a PivotTable. But sometimes PivotTables are too flexible – their dimensions change with the data, and they are harder to format consistently with the rest of a spreadsheet.

Excel with Business online training

AAT members have free access to this and many other Excel tips through their MyAAT account. Excel with Business‘s online course is designed to teach the features of Microsoft Excel that are useful to AAT members, saving time and offering a tailored learning experience.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.

Related articles