Sumifs and the Excel over-engineering problem

Some might say that Excel is just a little bit over-engineered. Like the car manufacturer that brings out a new model each year with a slightly better engine and up-rated suspension, Microsoft (bless it) has been bringing out new versions of Excel for years. The result might be a fantastically high performing machine but not one that necessarily makes it easier to get from A to B. In Excel’s case the result is an awesome product but one that can be a little difficult to navigate. A typical challenge in Excel might have five potential solutions and that makes part of the fun just working out which solution to use.

Today’s challenge: amalgamating data in a list

Right now we’re going to imagine something that seems like it should be a very simple challenge in Excel: picking out a piece of data from a list. We’re going to compare five solutions:

No prizes for guessing but today our favourite solution is probably Sumifs! Sumifs totals data that meet multiple criteria but there are lots of other potential solutions in Excel as well. Here we compare them.

The data for analysis: the John dope over-spend investigation

Imagine we’ve got four employees: John, Paul, Ringo and George. They’re pop stars (did you guess?) and there’s a lot they could spend their money on – you can use your imagination. Today we’re worried that John’s dope spend has gone through the roof, so we want to investigate John’s dope spend. We’ve got 1) a big list of employees’ names and 2) what they’ve spent their money on: those are our two criteria. Today we want to find out how much John has spent on dope.

If you want to follow this exercise you can get started by going to say cell B5 in a blank spreadsheet and entering John, Paul, Ringo, George and then copying down. Then enter say five expense categories (Dope, Rolls Royce, Concorde, Champagne, girls). Enter some dummy data as well say the numbers 1 through 21. You can see what we’ve done in the picture below.

Alternatively, you can download a spreadsheet where we’ve done all the hard work for you: sorting data.

Solution 1: sorting the data

If we didn’t know about everything Excel has to offer we might think about sorting the data (“Data” > “Sort” in Excel). If we sorted first by employee and then by expense category we could group the data and work out how much John spent on dope. But Excel (and you) can do much better than that.

Solution 2: create a pivot table

Some delegates on our training courses love pivot tables. If they’re not that familiar with them, we’re happy to get them started. In this case, you’d need to select the data and go “Insert” > “PivotTable” in Excel). You can find out more here: pivot tables.

Pivot tables are great for sorting and presenting data quickly. They will update as your data changes, but only if you right mouse click and select “Refresh”. for the investigation into John’s expenses, a pivot table would represent a bit of overkill. There are some standard Excel functions that will successfully pick out the correct data from the table. Top of the list today is Sumifs.

Solution 3: Sumifs

Some delegates on our courses know about Sumif: it adds data if it meets a certain condition. We have multiple conditions here though – that’s why we need Sumifs. Download the spreadsheet if you would like to see Sumifs in action: sorting data.

In the picture below we’re hoping you can see the formula we’ve used to get Sumifs working: =SUMIFS(D5:D60,B5:B60,L14,C5:C60,L15). Hooray!

Solution 4: Sumproduct

Elsewhere in this course training material we look at Sumif and Sumproduct. Some of our course delegates know about Sumproduct. It multiplies one list of data by another and sums the result. Fewer of our delegates know that you can build conditionality into Sumproduct. Here’s the formula we’ve used to get it working in the example below: =SUMPRODUCT((B5:B60=L14)*(C5:C60=L15)*D5:D60).

Solution 5: Array functions

Still fewer delegates on our training courses have come across array functions. We think of them as “hidden” functions in Excel. You won’t get much guidance or support from Excel or its help function on those ones. We also think of Array functions as combining two or more functions in one – a “two for the price of one” special if you like. Here we’ve combined Sum with a conditional test and you can see that it gives us the result we want.

Notice the format for the function looks very unusual {=SUM((B5:B60=L14)*(C5:C60=L15)*D5:D60)} and includes the funny “squiggly” brackets at each end. To get an array function to work, you will have to press “Ctrl” “Shift” “Enter” on your keyboard once you’ve typed the function in. Then the brackets will appear and you will know that your array function might be working correctly.

Is there really a best solution here? Who’s for pivot tables?

It’s fun trying to navigate Excel, and its over-engineering! Some people love pivot tables – the way they will sort all your data very quickly and present it nicely. Others appreciate that Excel’s standard functions are perfectly competent and capable of picking out the very piece of data you are interested in.

Sumifs vs. conditional Sumproduct vs. Array formulas

It’s hard to differentiate between these three really. The array functions are definitely a bit tricky to use, so perhaps that just leaves us with two preferred solutions for today’s problem. If you can understand how the conditional Sumproduct works, and pause for a moment to consider how simple that formula looks, then you’d probably be tempted to vote for that. Sumifs is designed exactly to do this job though (total up data that meets multiple conditions) so, if you hadn’t come across that one before, you’d probably be tempted to vote for that. You could almost call this a draw although, if you take the time to look a bit further into Sumproduct with us, you might find that formula has a slight edge over the others!

About this online financial modelling course material

You’re looking at a free online material covering Excel functions – it’s all from our Excel financial modelling course training.

 

 

...join the conversation...

Your email address will not be published. Required fields are marked *