Recently, I came across three videos on calculating sub totals in Excel. According to Mr Excel, “You have random chunks of data and need to add a total to each chunk”.
The first one is a VBA based solution by Bob Umlas, an Excel MVP.
The second one is from Kevin Lehrbrass, a former EFL teacher. In his own words: “Teaching English as a foreign language has taught me to break things down into smaller concepts that people can understand“. His solution is quite ingenious. He uses patterns in the data, and employs nestedIF’s, AND statements to accomplish this feat. I have a feeling Mike Girvin from Excelisfun would have done it in a similar way.
The third one is by Chandoo. who has an MBA from IIM Indore and Computer Science degree from Andhra University. He says “I am passionate about Data Analytics and Visualization and this blog is my platform for sharing what I learn about Microsoft Excel and new ways of presenting data”. He uses a simple trick to insert the subtotals in the desired place.
I tweaked Chandoo’s solution using Advanced Filter, SumIF, Match and Index. File: File can be accessed at http://goo.gl/cfYzAv.
- Select Customers, use advanced filter to get the unique list of records and place it on a different location
- Do a sumif on the unique customers on the sales amount
- In the sumif result table, name the customers range as customers and total as customer_total
- Go to special using CTRL F3, tick blanks,
- Enter index(customer_total,match(b16,customers,0)) and CTRL enter (I learnt this from Excelisfun), it will populate all the blank cells.
