Excel tip: using Sumproduct to pick and amalgmate data from a table

What's the best way to pick and amalgamate data from a table in Excel? This question was asked recently by a delegate on a financial modelling course. He'd recently been set an Excel test by a potential future employer. The test looked simple (picking and amalgamating data from a list) but sometimes something that seems that it should be simple in Excel turns out to be just a little tricky. And that trickiness is compounded by the fact that Excel always provides us with multiple solutions to a problem. With Excel sometimes half the battle is navigating your way through all of its potential solutions.

Picking data from a table

What about using Excel to pick the correct piece of data out of a list? That’s simple enough. We could use something like a Vlookup or an Index function.

Click on the picture above to download the example.

Amalgamating data within the table

What if we wanted to amalgamate data within a table? The challenge itself is easy enough to describe, but the trickiness starts when we decide that we'd like to amalgamate data using a short elegant function. You might first think of picking out the pieces that you need using e.g. your Vlookup function and adding those all together, but that could result in some pretty long cumbersome functions.

The conditional Sumproduct function

We’d like to introduce you to a little known Excel fact. Conditionality can be built into the humble Sumproduct function. That makes the function perfect for amalgamating data the way we’re imagining here.

Regular use of the Sumproduct function

In its regular day-to-day use Sumproduct kind of does what it says on the tin. If you’ve got two lists of data, you can multiply one list by the other and sum up those products.

Here’s an example for you to download (click on the picture). Imagine you’re a sales person. Some jobs you get paid a certain % commission. Other jobs you get a different commission. You could use Sumproduct to calculate your total commission.

Building conditionality into Sumproduct

Most Excel users are not aware that you can build conditionality into Sumproduct. You can use Sumproduct to pluck data from a list. As an alternative to Vlookup or Index it’s often a shorter neater solution (and we think that means it's better).

You need to be very careful how you construct the function (Excel’s regular help function doesn’t give you any guidance on this - so it's like an 'Excel secret') and we’ve shown you how to do that in the attached spreadsheet. Click on the picture above to download it.

Using Sumproduct to amalgamate data

Better still, you can use Sumproduct to amalgamate data. For example, imagine your data was presented quarterly and you wanted to calculate the total for the year. The conditional Sumproduct function could help you do that really rather neatly. It’s hard to think of a shorter neater solution to solving the quarterly data amalgamation problem!

Excel secret discovered! You can build conditionality into a regular Sumproduct. You can use that conditionality to amalgmate data really quickly.

About the author: Financial Training Associates Ltd

FTA Ltd is a provider of financial training courses. FTA’s financial modelling course runs regularly and includes coverage of helpful Excel tips and tricks.