Sumproduct formula

Early in the course we take the care to introduce you to a bunch of data picking solutions. You only need to master one but perhaps, as you get into more advanced modelling, you’ll find that each of the data picking solutions is going to have some strengths or provide a shorter neater solution in different applictions. The challenge always in modelling is finding the shortest neatest solution to the problem. So, as you progress with your modelling, you may want to try a few different data picking solutions as part of a continuous quest to simplify.

The solutions so far

Early on we meet the Vlookup formula (the preferred data picking solution for most people taking our financial modelling course training), the Choose formula (for a small data set) the Index formula with Match (has a logic that some people find easier to get their heads around and is less memory hungry) and Offset.

Relax: you only need one

Remember you can relax because you just need one go-to preferred data picking for your day to day modelling work. It’s helpful to have seen some of the other solutions though so that, when you encounter those solutions in someone else’s model you recognise them for what they are: they’re just some other spreadsheet jockey’s preferred data picking solution.

The strengths of other data picking solutions

In this next lesson we complete our coverage by looking at Sumif, Sumifs and Sumproduct. They can all be used as data picking solutions. Sumif can be used to pick out data because it picks out (Sums) data that meets a particular criterion. Sumifs is more powerful though because it can consolidate data based on one, two or multiple criteria. That must leave Sumif feeling pretty inadequate and wondering about its role in life. In our next example we conduct a witch-hunt amongst our band members (= criterion one) looking at the ridiculous amounts of money they’ve spent on dope (= criterion two). We’re looking to sum up the amounts of money band-member John (criterion one) has spent on marijuana (criteron two). Do you see how rock and roll Excel modelling can be? Sumifs was built for that job: grooving on down and amalgamating data over multiple criteria.

Excel sumifs formula

The power of Sumproduct – it’s a winner!

In this lesson we also introduce you to Sumproduct. We think there’s a real risk that, as you grow up, you move from Vlookup through Index and adopt Sumproduct as your preferred data picking solution. Sumproduct is a bit like Bruce Wayne = Batman. During the daytime Bruce has a regular job and no-one suspects what he gets up to at night. Sumproduct’s day job is to take two sets of numbers, multiply each one (e.g. £ sales amounts) by the other (e.g. % commission percentages) and add up the total (= £ total commission due). That’s OK but ever-so-slightly staid.

Of course Bruce Wayne has a secret. At night he squeezes himself into sheer black tights and goes out fighting crime. The secret with Sumproduct is that you can build conditionality into it. It’s definitely a secret because you don’t get any guidance about that using the “Shift” “F3” insert formula or from Excel’s own unflappably-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.

Today we introduce you to the superpower = the ability to build a conditional Sumproduct function. We think this puts Sumproduct ahead of any of the other data picking solutions. It can pick out data like Vlookup or Index. It can add up data for you like Sumifs. Unlike Sumifs though, it can consolidate up data over multiple rows and columns. This can make the conditional Sumproduct a very elegant solution to data amalgamation.

Today’s lesson will see us amalgamating annual data with quarters across the top and subsidiaries down the side. The challenge mirrors an Excel modelling test sent to one of our training course delegates as part of a job application for a US hedge fund. The test and the test question is deceptively simple: “Amalgamate the data” but you’ll find that the formulas you could be tempted to use could become long and unwieldy very quickly. In this lesson we think you’ll see Sumproduct totally triumph: it picks, it amalgamates, it amalgamates over multiple criteria, it amalgamates over multiple rows and columns. Sumproduct wins!

Excel sumproduct formula

Start the Excel course

This course lesson is really a 3-in-1 special covering Sumif, Sumifs and Sumproduct – so, apologies in advance, it may take you a bit longer than the usual 10 minutes. It does however carry the promise of seeing you decide to strap yourself into sheer black tights with Sumproduct.

Start the Excel formulas course.