The Problem

Subtotals in Excel, you have annoyed me for the last time. It's ridiculous how much you interfere with my ability to sum grand totals across an entire column. One of us is going down.

Seriously though, let's say you have a simple spreadsheet like this:

Spreadsheet with subtotals and grand totals at the bottom

Spreadsheet with subtotals and grand totals at the bottom

The subtotals are just sums, like =SUM(C3:C4), and that's fine. But for the grand total you usually end up adding the subtotals to a sum expression by hand. In this case it would be: =SUM(C5,C10)

This leads to chaos and defeat. Let's say the spreadsheet gets just a little bigger, maybe it grows into a 2 page spreadsheet with sections constantly changing. The grand total expression might end up looking like: =SUM(C5,C10, C15, C23, C30, C42, C45, C50, C60)... That's already too much room for human error; if I miss a single subtotal the whole thing will be wrong and I won't know.

So, rather than risk acting on bad data, I end up double-checking the spreadsheet by hand or resorting to other inelegant workarounds. 

The Solution

I wrote some VBA for Excel that sums only cells with a certain background color. So in this case, the grand total expression is just: =COLORSUM(C1:C10,C3)... This tells Excel to sum the C column, but only cells with the same background color as C3. If you have alternating row colors, the C3 part can be a range and that works fine.

You can also leave the C3 parameter off entirely, and then it only sums items that have no background color at all. That's a very common use case and probably the cleanest way to to use this tool.

I also made a ColorCount() function that works the same way.

Get Started

Getting VBA set up in Excel for the first time requires a bit of fiddling... I think Microsoft wants to make it so that people who tend to open virusy Excel documents from China won't be able to get through it.

I might expand on these steps if people struggle, but this should do the trick:

  1. Get the Module
  2. Import the Module. Open up your Excel spreadsheet and hit Alt-F11 go go into Developer Mode. This is probably a scary screen if you're not a developer. All you have to do is find Modules on the left, right click, choose Insert,  and choose the file that you downloaded in Step 1. Close the screen and you're done.
  3. Enable VBA in Excel. Just follow along with this video
  4. Save your Spreadsheet as a Macro-Enabled Workbook. Go to Save As and select 'Excel Macro-Enabled Workbook' from the list. It will save as an .xlsm file; the only difference between this and a normal workbook is that macros are enabled.
  5. Restart Excel. Possibly unnecessary, but I seem to recall that I had to do this.

And now you should be good to go!



Comment