Excel tips series
An Introduction to SUMPRODUCT - an essential Excel tool for ALL accountants
Dave Sellick Founder, Sidgrove
Excel is still used regularly by accountants yet many are self taught and are missing out on lots of useful tricks!
To help us learn some more useful Excel functions, Dave Sellick will be giving us a great "Excel Top Tip" in each issue so we can learn how to really use Excel well. Dave is a super user of Excel and believes it is an absolute game changer and a central part of every accountant's toolkit - even in 2020.
In this issue, Dave provides an introduction to the SUMPRODUCT function in Excel which is a really useful and essential tool for all accountants.
An Introduction to SUMPRODUCT - an essential Excel tool for ALL accountants
This formula is such an underrated gem 💎 and one that plays a pivotal role in almost every dynamic client report that I create
You need this formula in your workflow so here’s a quick intro & a walkthrough 🔎 to show you how to use it to power 🔥 a dynamic ‘Last 12 Months’ (LTM) P&L 📊
What is SUMPRODUCT and what is it designed to do?
- SUMPRODUCT was designed to be used to, as the name suggests, sum a number of multiplications However, the formula has abilities far beyond this intended use 🚀
- Below 👇🏼 I bring focus to its significant potential to be used as a flexible and powerful tool for applying complex lookup criteria across datasets
- Think of it like SUMIFS, but better 🙌🏼
- Keep reading and I’ll take you through some concrete steps to harness its power in a report that every accountant should be able to produce for their clients 👍🏼
Why is it so powerful?
✅ It applies complex sum lookups effectively across large data arrays
✅It is extremely flexible 🧘🏻♂️: it can be used in combination with many of Excel’s other formula to sum values based on very specific, and dynamic, criteria A really good example of this is embedding the ‘OR’ function into a SUMPRODUCT
✅It prevents having to reference specific columns: This is a key differentiator vs SUMIFS
✅It works seamlessly with ‘named ranges’ to create audit heaven 😇: Using named ranges or structural formulae, you can copy one single formula across your dataset and have confidence that the results will be on point.
- Makes the report creation process simpler & more watertight
- Makes the report output easier to audit for integrity
How do I create a SUMPRODUCT formula?
- Starting with “=SUMPRODUCT(“ You type a series of true/false conditions [e.g. (‘Data source’!$A$6:$A$200=$A6)], which are surrounded by brackets and are multiplied together [e.g. (‘Data source’!$A$6:$A$200=$A6)*(‘Data source’!B$5:L$5=“B$5”)]
💡The multiplication between true/false conditions forces the answers to return an answer in 1/0 format 💡Notice the use of absolute cell references ($) - this ensures that the row/column we are testing do not change, wherever we copy the formula to in the dataset
- Once these ‘questions’ have been populated, they are then applied to a data array by further multiplication - that array being the one from which you want to return the sum of results that meet all criteria, e.g. B6:Z200
💡So in this very simple example, with two conditions, it would look like this: =SUMPRODUCT((‘Data source’!$A$6:$A$200=$A6)*(‘Data source’!B$5:L$5=“B$5”)*‘Data source’!$B$6:$Z$200) 😵😱😲🤯…a little messy right! It’s super hard to understand what the formula is trying to do - this is not a good long term framework when creating Excel reporting…. This could be simplified by naming the arrays in this formula (something we’ll come onto in the dynamic LTM P&L example later in the article 👇🏼) =SUMPRODUCT((P&L_Codes=$A6)*(P&L_Months=“B$5”)*P&L_Data) 👉🏼 i.e if the code in the adjacent column is equal to a code in the P&L source data, then please return the sum of the data values, where the month in the P&L source data is also equal to that of this data value
- This is facilitated by surrounding these questions (e.g. A5:A40= “Yes”) by brackets and multiplying them together, before then multiplying by the dataset, within which you want to return the results that meet all these criteria
- Effectively it operates much like a SUMIFS formula, but without having to reference specific criteria column, you can apply it across an array of columns and rows at the same time
💡 You could apply a single SUMIFS formula across an entire dataset, including multiple columns, but this would involve combining INDEX, MATCH AND INDIRECT formulae alongside SUMIFS 🤯 - i.e. for normal purposes, not feasible
- Have I lost you? Maybe… well let’s work through a specific example to allow some more clarity via its use case in one particular accounting report that I use it in all the time
✨Using SUMPRODUCT to Power a Dynamic Last 12 month set of Financials & Visuals
1. Extract & Input a 3 Year P&L Dataset
- For the purpose of this walkthrough, I’m going to use a 3-year P&L dataset, as output through the Xero Datadear P&L feed into Excel
❗ Here are few notes to pre-empt some of your likely questions 😊: 💡 3 years is required so that we can create an LTM comparative 💡 If I was creating a full 3 way set of accounts I would also run a TB output from Datadear - this isn’t relevant for this particular walkthrough, but just FYI! 💡 If you weren’t using a Datadear feed then you could create a custom 3-year report that, without alteration, could be copied and pasted into the raw dataset input tab as each period elapses. You’d then only need to refresh this report at the end of the financial year
- This dataset looks as follows and includes all P&L data for ‘Deckspin’ (demo company) for the 3 years ended March 2020
(Yes I appreciate it’s hard to make out any meaningful details in this screenshot 🙈, but that’s also the point - let’s turn this into something more focussed & more insightful 💯)
2. Ensure that the Months are set up to allow for a dynamic lookup
- Taking the cell above the earliest Month of the Dataset, we’ll hardcode the last day of the month in full date format as follows:
- Use the EOMONTH formula to quickly populate all periods in the 3 year range to reference the last day in the month:
💡 Why reference the last day in the month? This allows us to have a specific code to lookup on, whilst the EOMONTH formula allows for dynamic functionality (aka automation) when the period date changes. This doesn’t need to be the last day in the month, but it does need to be a specific day in the month & this needs to be applied consistently throughout the report interface 💡EOMONTH: as the name suggests this formula returns the last day in the month of ‘x’ number of months in the future or past. I use this all the time for reporting & financial modelling/ forecasting, so it’s a great one to have in your toolset ✌🏼💯
- Once input, this then needs to be pulled across the full 3 year period
3. Create Range Names for the the Lookup Rows, Columns & Arrays
- Chart of Account Codes (You could use names here if there are no codes)
- Department (if relevant)
- Ranges are created by selecting the full range of data and then typing in a name in the ‘name box’, which can be found in the top left of the screen below the clipboard
- Press ‘Enter’ to confirm the name once you’ve typed it in
- I’m going to select each array of data and give them the following names
💡 These named ranges will be used to help us construct the SUMPRODUCT formula that will power the LTM P&L; as previously alluded to, they are also helpful to create a clear audit trail for inputs into the formula Named Ranges:
- Months: ‘P&L_Months’
- Chart of Accounts: ‘P&L_Codes’
- Dataset: ‘P&L_Data’
4. Create a ‘Period’ named cell
- Find a suitable location to create an input for the period for which you are running the report
- In this instance, I’m going to put it on the ‘Cover’ for the financials, within which this report will sit
💡I have refined the aesthetic of the period date using Excel formatting. However, you don’t need to do this and I don’t want to go off on that tangent in this walkthrough! 💡I have also merged this cell across the page and created a dropdown list. However, this is purely for aesthetics and for user experience - you can put this input into any cell in the report that makes sense ❗ N.B. Ensure the period that you input aligns to the month-end date of whatever month you want to run the report for - super important to ensure the SUMPRODUCT will work!
5. Construct the Last 12 Months P&L report format
- Create a P&L report template similar to the following, being sure to populate it with all of the relevant Chart of Accounts as featured in the raw dataset
6. Populating the Dynamic Period Headers
Populate the Period Date Header via referencing the ‘Period’ named range and use of EOMONTH as appropriate
- This will ensure that the report is dynamic and changes as the ‘Period’ is changed
This is effectively a re-run of part 2) but in reverse order, with the current Period being on the far right hand side of the report
- Therefore instead of using the EOMONTH(Cell Ref,1), you will use EOMONTH(Cell Ref,-1)
- Refer to the video walkthrough or pictures below if you’re unsure here!
Pull the EOMONTH formula across the table to populate all of the period headers
7. Add a reference Column for Chart of Account Number
- Ensure that you have a column on the far left of the report (i.e Column A) that denotes the relevant Chart of Account Code
- Populate this with the code for the line item
- This is for lookup purposes
8. Populate the figures using SUMPRODUCT
- Now we’re going to flex our powerhouse 💪🏼
- Populate the first Revenue Data point with the following formula:
Where: 👉🏼 N5 = the Month 👉🏼 A8 = the Chart of Account Code = SUMPRODUCT((N$5=P&L_Months)*($A5=P&L_Codes)*P&L_Data) 💡 Use brackets to surround each individual argument 💡Use multiplication between each argument - this turns the ‘true/false’ results into 1/0. On this basis results will only be driven when all criteria are met; i.e. if there are any ‘false’ results then this will result in a 0… and, to state the obvious 😲, when you multiply anything by 0, you get 0! N.B. ❗ The ‘strict’ use of the formula does not specify the use of multiplication between criteria, but this is the most practical way to use this formula in my experience 👉🏼 The alternative involves using double negatives🤷🏻♂️ (and I don’t want to go there 😂). When I realised I could just use multiplication between criteria, this completely revolutionised my experience with using this formula 💡 We are hardcoding the 5 in N5 to ensure that the Month row reference only moves sideways 💡 We are hardcoding the A in A5 to ensure the Code column reference only moves up/down 👉🏼 As a result of both of these ‘hardcodings’, only one formula is needed across the whole dataset because it will automatically change as the formula is copied and pasted across the report
- The first expenses data point will be populated with an identical formula but proceeded by a negative sign
- This ensures that all values in the report are positive
👉🏼 For more clarity here, refer to the video walkthrough
- Link the actual & comparative figures to the far right LTM column and second from right LTM column and you should then finally end up with something like this:
OPTIONAL: Add in Graphical Insight - including Prior Year Comparatives
- Use the method laid out above to add in another 12 months of data, from the preceding 12 months and lay this out to the right of the current year LTM data
- Then take advantage of all this dynamic data to create dynamic LTM trend graphs such as the following:
- Refer to the 🎥 video walkthrough for full details as to how to create these
⚡ For more clarity around this process, be sure to check out the videos that I have recorded, which should really help to cement this SUMPRODUCT use case into your toolset. ⚡As alluded to above, in the video I’ll also take you through the process for creating the LTM dynamic graphs
Thanks for reading, I'll be ready with more tips for you next time 🙌🏼
A bit about Dave
- I am the founder of a purpose led accounting practice & consultancy based in London called Sidgrove, working exclusively with founder-owned businesses & scaling startups. I initially trained in audit with PwC in banking, before moving into industry for around 6 years with numerous scaling startups; I’ve been running Sidgrove full time for just over a year.
- I’ve spent more hours than is probably healthy🤦♂️😂 working with Excel 📊as an Accountant over the last 12 years in both industry and practice. During that time I’ve continued to push the boundaries 😇 of how it can be used to deliver efficiencies and superior insights to my clients.
- My Excel journey really took off 🚀during my time as a Head of Finance of various startup businesses, where efficiency🛠, quality commercial insights 📈& creative presentation🌈 was imperative.
- I’ve now taken all this experience into my practice, Sidgrove, 💪🏼where amongst a wide suite of software and hardware tools, it is still my most prized day-to-day tool 🏆and canvas ✒.
- I believe Excel is still an absolute game changer 💯🚀and it needs to stay centre stage in the Accountant’s Toolset, even in 2020.
- However, I know the full functionally of Excel can seem daunting many accountants 😨so I am starting a top tips series to help you get the best out of Excel’s most relevant functionality for Accountancy in the modern-day 😊.