06 Jan 2014

## Video 00062 Learn Excel: Add Sporadic Totals

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.

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