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
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.
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.