Calculating Subtotals

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.

  1. Select Customers, use advanced filter to get the unique list of records and place it on a different location
  2. Do a sumif on the unique customers on the sales amount
  3. In the sumif result table, name the customers range as customers and total as customer_total
  4. Go to special using CTRL F3, tick blanks,
  5. Enter index(customer_total,match(b16,customers,0)) and CTRL enter (I learnt this from Excelisfun), it will populate all the blank cells.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s