How to Summarise Data with SUMIF()
Some of the best functions in Excel are those that help to make sense of indigestible large data sets. One function, which is great at helping turn data into insight, is SUMIF(): a powerful function that adds up numbers across categories.
Imagine you 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:
We can use SUMIF() to summarise this data – for instance, to show the total sales, across all stores and flavours, but 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:
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.
Learn more about Excel
We cover SUMIF() and similar functions in Unit 21 (‘Summarizing Data’) of our online Excel course. Take a look at the syllabus.
If you liked this article, then you’ll love our most popular course, Microsoft Excel. You’ll be an Excel expert in no time!