Sumproduct

We met Sumproduct when we were looking at Sumif (adds data that meets a single criterion) and Sumifs (adds data that meets multiple criteria). When we were looking at Sumifs we saw how we could build conditionality into Sumproduct. This makes Sumproduct a candidate for competing with our regular solutions for picking a single piece of data out of a table (the other main solutions being Vlookup, Choose and Index) but when we were looking at Sumifs we also saw how Sumproduct could compete as a solution to amalgamate data in a list.

A very few of our modellers are in the habit of using Sumproduct as their ‘go to’ data picking solution and there’s a sensible reason why. You might gravitate towards Vlookup because it works for all your data picking problems, and you only need one solution that works. Alternatively you might gravitate towards Index because it makes a bit more logical sense to you and you’ve heard it absorbs less Excel memory. A few of our advanced financial modellers gravitate towards Sumproduct because it can pick data AND amalgamate it – in TWO dimensions. That makes Sumproduct AMAZING. It picks, it amalgamates, it amalgamates over two dimensions: it does more than all the others!

Sumproduct: an example

Here’s a simple set of data. It mirrors a test that was thrown one at of our training delegates as part of a job application for a US hedge fund. The challenge is to produce total annual numbers from quarterly data for each business. Describing the challenge is simple (“Amalgamate the numbers”) but being able to do it using a very short Excel function will reveal how advanced/ experienced the modeller is. You can download the example by clicking on the image below:

Excel's Sumproduct formula

If you didn’t know about the ability to build conditionality into Sumproduct and use it to amalgamate data you’d probably think of doing something else. Perhaps you’d gravitate towards a Sumifs combined with an Offset. Maybe, if you knew about array formulas you’d try one of those. Both could work. We think the conditional Sumproduct (and its awesome power to pick data out of a table and then amalgamate it over two dimensions) makes this Excel function hard to beat. It also explains why some of our most advanced financial modellers can gravitate towards Sumproduct as their preferred data picking solution. They know it works for plucking out data but it’s also got the best ability to amalgamate. Sumproduct triumphs!

My data picking solution

Remember out of all the data picking solutions you only need one. Many modellers would start and end with Vlookup and would concentrate on mastering that one. That’s fine, you’re welcome to stop there. If you’re manipulating an awful lot of data and are running into Excel memory/ spreadsheet size issues, you might want to consider leaning towards Index. If you want a data picking solution that also has an extra super-power in its ability to amalgamate data, you might want to consider starting to practise with Sumproduct.

About this online financial modelling course material

You’re looking at a free online material covering Excel functions – it’s all come out of our regular modelling course training.

 

 

1 comment

...join the conversation...

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

  1. Conditional SumProduct function is amazing, used it 1st time. Very powerful.

    Reply