Simple but deadly amalgamation with Sumproduct

Here's an easily-described but revealing Excel test: "amalgamate the data". I've seen a version of this set for someone who was hoping to intern at a US hedge fund. It looks and sounds simple but it's the perfect test of data amalgamation skills.

Picking and amalgamating

You regularly need to be able to pick and amalgamate data in Excel. For picking I think many people would use Vlookup (that's fine, it works) although most pros would use Index.

For amalgamation I think many people would use SumIF (or its bigger badder cousin SumIFS).

The hedge fund had planted some subtle clues in the spreadsheet test ("Use SumIf to find the?cost saving from an immediate sacking of the junior traders who are due less than a $100k profit-related bonus this financial year") so it was clear that they were fans of SumIF for everything - both picking and amalgamating.

Amalgamating in multiple dimensions

In the example you have to amalgamate by year (across the top) and down the side (by subsidiary). To do that using SumIF you'd have to take the exercise through a couple of steps. That's fine. That would see you scoring a good grade in the test if I were marking it (manually created individual Sum functions would have been a C minus I am sure).

SumIf is perfect. You have to amalgamate by year first (stage 1 above) and then amalgamate by subsidiary.

Sumproduct shines

If you're someone in a hurry and fond of?racing everywhere absolutely as quickly as you can, Sumproduct could have gotten you to the answer straight away (or, if you're on subscription 365 Excel with 'dynamic arrays' included, Sumproduct's near cousin = the array version of Sum). I think Sumproduct would be worthy of an A+ in the test just for being experienced enough to know that Sumproduct has got a very special talent.

I guess perhaps the drawback with Sumproduct is that it's slightly harder to understand than SumIF. And, really, what's the problem in taking a couple of steps to get to the answer (nothing)?

Once you've got the hang of Sumproduct's power it's easy to get carried away with it. It picks (most pros use Index), it amalgamates (like SumIF) but it also amalgamates in multiple dimensions. On the basis that it does everything, nothing beats it. It's Excel's top trump.

You only need one

Excel offers us a bamboozling chocolate box of formulas for picking and amalgamating data. If you work at the US hedge fund you'll likely find yourself using SumIF most of the time just because that's what they do there and they know SumIF picks and it amalgamates.

I don't really mind what you do as long as it works and it's not too complicated. But to cut through all the confusion and if you want a recommendation here it is. Based on what I see the pros around me using, for picking use Index. For amalgamation use SumIF (or maybe SumIFS when you need to filter using multiple constraints).?

Keep Sumproduct up your sleeve for when you're feeling bored one day and want to do something different or maybe just show off - a bit.

More about Sumproduct: it has a day job.

Sumproduct's power to amalgamate data in two dimensions is an Excel secret. Press "F1" help in Excel and under Sumproduct you're not going to find too much about amalgamating data. Sumproduct has a very dull day job. In everyday use Sumproduct I think is designed as the sales-person's friend.

Imagine you worked under a tiered commission structure. For the first tranche of sales you got paid [X]% commission, for the next tranche of sales [Y]% and so on. Sumproduct would be your best pal. It would allow you to work out whether you're going to be able to feed your children and pay your mortgage each month. Sumproduct does a very plain job. It takes a list of numbers (the tranche) and multiplies it by another list of numbers (the commission due on that tranche).

Sumproduct's secret power

When we use Sumproduct to amalgamate data we use Sumproduct in a different and unusual way. Its amalgamation powers are Sumproduct's secret so you have to be careful how you set it up to get it to work.

Notice we are not pointing Sumproduct to lists of numbers separated by commas like we normally would. In this version we set the first constraint (year) using ($C$5:$Z$5=B$28) being careful to put that all in brackets. Then we multiply the first year constraint by the second subsidiary constraint ($A$8:$A$12=$A29). Then finally we multiply by the table of data $C$8:$Z$12. No commas anywhere.

Going large on Sumproduct

Every so often you will find someone using Sumproduct in this whacky way for absolutely everything (calculating their commission, picking, amalgamating across one dimensions, amalgamating across multiple dimensions). Now you'll know what they're doing. Stick with what you know (e.g. Index for picking, SumIF or SumIFS for amalgamation).

Just?being aware of Sumproduct's secret superpower puts you ahead of the other interns at the hedge fund though. Congratulations.