We think Sumproduct is probably the super-hero of Excel financial modelling formulas.
We’re not sure Sumproduct really squeezes itself into tights and goes out fighting crime at night, but we think it definitely has some extreme powers that would see it coming out ahead of other Excel formulas in a fight.
Vlookup vs. Sumproduct
The Vlookup formula seems to be the most popular of ‘data picking’ solutions amongst delegates on our financial modelling course training. Often in Excel you want to ‘pick’ data out of a table of data with column headings across the top and row categories down the side. Most attendees on a training course would gravitate towards the Vlookup formula or perhaps the emminently able Index formula for this job.
But Sumproduct can happily pick data out of a table and it definitely has some powers Vlookup and Index don’t have. We think that makes Sumproduct a winner.
Sumproduct’s day job
Sumproduct’s day job is to take sets of numbers, multiply each one (e.g. £ sales amounts) by the other (e.g. % commission) and add up the total (= £ total commission due). That’s OK but ever-so-slightly staid. Sumproduct has some hidden super powers that means it can do much more than other Excel formulas.
You can click on the image above to download an example showing how to use Sumproduct for its regular job. It may help to click on cell F14 and press “Shift” “F3” on your keyboard to bring up the insert formula dialog box.
How Sumproduct's like Batman
We think Sumproduct is a bit like Bruce Wayne = Batman. During the day Bruce has a regular job (businessman, philanthropist) and no-one suspects what he gets up to at night. Sumproduct has a day job too: taking two sets of numbers, multiplying one by the other and adding up the result.
Of course Bruce Wayne has a secret. At night he squeezes himself into his tights and goes out fighting crime. Sumproduct’s secret is that you can build conditionality into it. It’s definitely a secret because you don’t get any guidance about that from Excel’s own almost unfailingly comprehensive “F1” help. It’s something advanced practitioners have probably learnt by looking at what’s going on in other modelling work. They just ‘know’ about it.
The ability to build conditionality into Sumproduct means it can go head to head with Vlookup or Index as a data picking solution.
But Sumproduct does even more…
Sumproduct can pick data like Vlookup or Index
A few attendees on our financial modelling course training know that you can build conditionality into Sumproduct, using logic tests to sort data.
This means Sumproduct can compete with Vlookup and Index to pick data out of a table. But it deals with multiple criteria pretty efficiently too:
And Sumproduct does even more…
Sumproduct’s superpower: an example amalgamating quarterly data
As well has being able to ‘pick’ data out of table like Vlookup or Index, Sumproduct can also add up and amalgamate data.
Here’s an example that mirrors an open-book test that was emailed to one of our training course delegates as part of a job application for a US hedge fund. The test and the test question were deceptively simple: “Amalgamate the data”. But you’ll find that the formulas you might be tempted to use could become long and unwieldy very quickly. The candidate’s answer to the question will quickly reveal something about how much advanced Excel modelling experience they have.
If you click on the example you can download it. You should be able to see that Sumproduct is being used in a very different way than normal. This is how we’d explain it:
- First Sumproduct is working down the first column to see whether it can find a match on the row categories;
- Then Sumproduct is working working across to see whether it can find a match on the column headings;
- Finally it adds together all the results where it finds a match on column headings and row categories.
Book-marking this reference example could prove useful should you find you need to amalgamate data like this in the future. The beauty of the Sumproduct solution is that the formulas you see (in the summary table in the example) can be filled across and down and they stay short and simple.
Excel’s Sumproduct formula wins!
We think all this means there’s a risk that, as you grow up, you graduate from Vlookup through Index and on to Sumproduct. Sumproduct really does come out ahead. It picks, it amalgamates, it amalgamates over multiple criteria and it adds up over those multiple rows and columns. Sumproduct triumphs over all!
Read more stories
You’re reading stories from a world we love: the world of financial modelling. It’s a heady world. It might see you deciding to use Sumproduct and fight crime in your spare time.
Give yourself an Excel financial modelling tune up for free
We can give you an Excel modelling tune up in around ten minutes a day – for free. We’ll send you one email a day for a month – each email should take only around ten minutes a day to work through.
The material is extracted from our regular Excel financial modelling course training and is well proven with other people taking the program. The program covers all the formulas attendees on our financial modelling course ask about and find the most useful of all.
Sign up online for the Excel formulas course
Excel has hundreds of formulas on offer. That can present a bit of a challenge: you need to become fluent in the formulas that are going to be most helpful in financial modelling.
We think that, by sending you one email a day for a month, in just ten minutes a day we can check you’re on top of critical Excel financial modelling formulas. All for free.
Here you can sign up online for the free Excel formulas course.
Excel modelling formulas for free
You can sign up here online for the free course covering the Excel formulas most useful for financial modelling