Imagine you had a monthly or quarterly model with lots of columns and you wanted to produce sub-totals for each year. The wrong thing to do would be to insert individual sum functions as sub-totals. Then you’d lose the ability to work quickly by filling from left to right – each time you tried you’d write over your annual sub totals. A better solution would see you placing the annual totals in a separate summary area of your spreadsheet, making use of another Excel formula that you could fill left to right automatically.

Amalgamating Excel data – which formula to use?

An advanced Excel user might gravitate towards Sumifs or Offset to amalgamate data. As we’ll see shortly though, Sumproduct has a clear advantage over both of these.

Sumifs: an example

Below we’ve created an example that should help you see how Sumifs works. You can click on the image to download the example. Once you’ve downloaded the spreadsheet it may help to click on cell B6 and press “Shift” and then “F3” both at the same time – that will bring up the insert formula dialog box and enable you to see what’s going on.

The trick here has been putting some sensible column headings across the top of the model that allow Sumifs to do its work. Sumifs will look across the column headings trying to find those that match the year of interest. If the year matches, then Sumifs will create a sub-total for you.

(Note: we always prefer Sumifs over Sumif. Sumif only handles one condition.
Sumifs handles one or multiple conditions, making Sumif redundant).

Applying Offset to the data amalgamation problem

Offset seems perfectly designed for the data amalgamation problem. What we do in the next example below is place a Sum function around Offset. Offset ends up ‘jumping’ the source data for that formula four columns to the right each time.

An example of Excel Offset

You can download the reference example by clicking on the image below. Once you have Excel open, it may help to click on cell C6 in the spreadsheet. Then, in the formula bar, position your cursor next to the word “Offset”. Then press “Shift” “F3”. That should help you see what’s going on.
It’s the third section of the Offset formula that’s doing the hard work – jumping everything four columns to the left.

Sumproduct: the Excel data amalgamation champion

We think Sumproduct beats Sumifs and Offset for data amalgamation. Imagine our data set had grown (it may help to click on the image below and download the example) so that we wanted to amalgamate by column and by row. A Sumifs or Offset solution is quickly going to become long and complex – but Sumproduct tackles the job perfectly efficiently. Sumproduct can amalgamate data in two dimensions at the same time. It can amalgamate data based on column headings and also row names – it can combine columns and rows.

What’s Sumproduct doing?

This time “Shift” “F3” for the insert formula dialog box is not going to help us. There’s little internal guidance from Excel on Sumproduct’s usefulness in amalgamating data. It’s an Excel secret – advanced users just ‘know’ about it – that’s why we’ve created the example above for your reference. It may help to click on cell B6.

Sumproduct is working through the column headings looking for a match on the year. Then it’s working down the rows looking for a match there. Where it finds data in the middle it adds it up. We think the reference example above will be helpful for you in the future.

Sumproduct: the Excel data amalgamation champion

Because you can use Sumproduct to combine data across rows and columns (at the same time) we think it’s the data amalgamation champion, triumphing over other standard solutions like Sumifs or Offset. Excel’s Sumproduct formula is the data amalgamation champion!

Read more stories

You’re reading stories from a world we love: the heady world of financial modelling.

Read more stories

Excel has hundreds of formulas on offer. That can present a challenge: you need to become fluent in the formulas that are going to be most helpful in financial modelling.

We’re happy to provide you with an Excel modelling tune up – for free. The program covers all the formulas attendees on our financial modelling course ask about and find the most useful of all.

We’ll send you one email a day for a month – each email should take only around ten minutes a day to work through – and you can unsubscribe at any time.

Sign up online

Here you can sign up online for the free Excel course covering the formulas most helpful for financial modelling.

Sign up online here