Sometimes our data isn’t perfect and we just have to deal with it. In this post you’ll see an awkward data-set from Mr Excel with a VBA solution from Bob Umlas and a formula solution from me.


The Challenge and The Data

How can we add subtotals per group?

The numbers we want to add per group can be either in column C or column D. The amount of blank rows between groups of data varies.


Automated Solution

We don’t want to repeat a bunch of manual steps every time. We want to automate the solution.


VBA Solution

Bob Umlas solved this using VBA.

Bob explains his vba code here on Bill Jelen’s video.





Formula Solution

Do you prefer formulas? I used helper columns to break the solution into steps. Download my file here.


What are the ingredients to my solution?

I combined these functions to create my solution:  IF, ISNUMBER, AND, NOT, SUMIF


Quick Non-Automated Solution

If your data is cleaner (all numbers are in one column) and it’s a one time solution then watch Chandoo’s method!

 YouTube Video  Chandoo’s post.




Contextures Newsletter

Debra Dalgleish’s comment about sporadic totals from her newsletter:

“Apparently it was sporadic totals week, and nobody told me!”





Subscribe to my YouTube channel and learn more!


About Me

KevinInMunich_My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.

Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and blog posts.

Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.


Post a comment