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.
We don’t want to repeat a bunch of manual steps every time. We want to automate the solution.
Bob Umlas solved this using VBA.
Bob explains his vba code here on Bill Jelen’s video.
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!
Debra Dalgleish’s comment about sporadic totals from her newsletter:
“Apparently it was sporadic totals week, and nobody told 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 at McKinsey & Company.
Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.