I love working with customers across the world on Excel development projects - particularly football traders with whom I share a passion for the sport.  Our views tend to differ on one thing, however: the role of a specific formula in football data analysis in Excel.
GO TO EXCEL (VBA) FOR FOOTBALL TRADERS

CLICK HERE TO WATCH THE VIDEO

The SUBTOTAL formula is popular in football trading circles, though I’ve never really understood why.  I think, perhaps, it’s because the first way to ‘backtest’ a football system in Excel is simply to apply filters to a dataset.  SUBTOTAL, therefore, is a good option because it allows us to sum up only filtered rows.  It doesn’t feel too onerous for somebody who is used to filtering.  Don’t get me wrong – it’s certainly better than using a calculator!

EXCEL DOWNLOAD FILES

00:00 SUBTOTAL DEMONSTRATION

But the truth is there are better options out there.  Better in the sense they avoid Excel’s cumbersome filter menus.  Better in the sense they lead to more sophisticated analyses.  And better in the sense they improve your Excel formula-building skills.  Give the video a try – I guarantee you’ll be filtering less in your Excel data analysis, saving time, energy and stress …

So, without further ado – here are 3 alternatives to the Excel SUBTOTAL formula.

04:05 SUMIFS
The basic idea is to move away from the filter menus and move towards using cells to control inputs and outputs.  This is what SUMIFS allow us to do – change the value in a cell and instantly view the result.  There you go, no filtering and at least 30 seconds saved!

There is a downside, however.  SUMIFS requires patience and precision – two building blocks in Excel formula-building.  In the video, I build the formula step-by-step (messing up at least once!) pointing out common pitfalls and how to avoid them.

See how we have sped up data analysis by bypassing the filter menus altogether, and using cell inputs?  Hopefully you can feel the power of the Excel SUMIFS formula.  But, the formula itself is long and unwieldy, so might there be another alternative that’s easier to build?

10:50 DSUM

If I could only take one formula with me, what would it be?  ‘Like choosing between your kids!’, somebody once commented on Twitter.  If it wasn’t DSUM, it would certainly be in the top 3.

DSUM gives us the calculation power of SUMIFS with a much shorter formula.  The downside?  Some set up is required, as I demonstrate in the video.  It’s normal for such a powerful formula not to work first time, so in the video I point out what’s likely to go wrong and how to fix it.  As usual, a simple spelling error can be catastrophic … but that’s data analysis!
Once again, we’ve achieved the power of SUBTOTAL without needing to go near the filter menus.  I hope you’ll take forward DSUM, a personal favourite of mine, in your data analysis …

‘But Chris’, you might be thinking, ‘the key advantage of SUBTOTAL is it actually allows us to view the filtered rows – neither of your formulae do?’  Read on …

15:12 FILTER

Only available in Excel 365, FILTER is part of the family of ‘dynamic array’ formulae that are featuring more and more in people’s Excel practice.  The basic idea of these formulae is they ‘spill’ over a single cell into adjacent columns / rows on the spreadsheet.

This presents an intriguing possibility if you’re looking to get away from filtering and the SUBTOTAL formula: could you display ‘filtered’ rows using … an Excel formula?

It sounds crazy, but it’s possible with Excel’s FILTER formula.  Yes, the syntax is counter-intuitive, as I explain in the video, but the dynamism and power of the output could have a game-changing effect for your Excel data analysis.  Make sure you download the Excel file and work along with my example …

21:20 LEARN MORE ABOUT EXCEL (VBA) FOR FOOTBALL TRADERS

If you’ve enjoyed this video and are working with football data in Excel, or just looking for a set of ‘real-world’ Excel projects to boost your data analysis skills, you’ll love our Excel (VBA) for Football Traders community.  It opens for new members only a couple of times a year, and we’re currently open!  Don’t miss your chance to get involved …

GO TO EXCEL (VBA) FOR FOOTBALL TRADERS