How do I sum thee? Let me count the ways.....

Do you know what I really love about Excel? The fact that everyone uses it differently. There are so many ways to reach the same answer, and people will use the techniques that they have been taught, or find the easiest.

Take the simple task of adding totals and subtotals to data, for example. I had a debate with my better half the other day about this very subject. He went for  the subtotal function. I went for pivots. It wasn't too heated (because hopefully we aren't that sad yet!), but it made me think about all the ways you could do it.

So here are some examples of how to get sub totals and totals for data with different categories (and bear in mind these are not all of the ways to do it... but we don't have all day!). Please note too that I am going to ignore the most simple and obvious choice of the sum function. Pure snobbery, but we have to have standards...

Our test data for today is riveting...unit sales on widgets, sprockets and gadgets:



Subtotal Formula Function
If we side with my better half, we can get subtotals for each product by doing the following:

 - Select all the data, and sort it by Product.
 - Insert lines between each product type
 - Add the sales for each product grouping by using the subtotal formula function as follows:

Things to note on this method:
 - When typing the Subtotal formula, note that the 9 (or you can use 109) refers to 'adding'. If you type in" =subtotal(" into your spreadsheet, you will notice a little drop down box appears giving you all sorts of lovely options, like average, max etc. The number you type in directly after the open brackets will determine what magic your formula will perfom.

 - The formula in the total line at the bottom will ignore all the subtotals above it. Handy.

Subtotals with grouping

An easier way to do this is click on the data, go to your data tab, and select the Subtotal button. You can then select what type of formula function you want, for which columns and hey presto, you have the same as above. You also get little brackets along the left side of your sheet that you can open and close for ease of viewing the totals, like below:

SUMIF Function

Has anyone seen the issue with this method yet? Well, one of the issues anyway...What happens if we want totals by colour too? You have to rearrange everything. Or, you can do this:

By using the SUMIF function, you can create sub totals by product or by colour, without having to touch the data. I've shown the formula above for clarity, but what it is basically saying is "Look in column B for any cells that match "Widget". Where they do, add up all the corresponding figures in column D". You can then drag this formula across to get the totals for all your products and colours.

The plus side of this is that you don't have to rearrange your data to get totals for different categories, and it is easier to add more data.

Pivot Tables

In my mind, this is still the cleanest option. By using a pivot table to get subtotals and totals, you can cut and slice your data however you choose. A basic pivot of the data would look like this:

By using the Pivot Table Field List, you can then choose whatever options you like for your pivot table by mixing it up.

For example, if you wanted these totalled by colour, simply swop the positions of Product and Colour in the Row Labels box.

This is a basic Pivot Table, because they deserve a whole blog on their own. But if you would like to see how I would mix this data up even more in a pivot, why not subscribe to our newsletter?

At the end of the day, you may say To-may-to and I may say To-mah-to, but we'll both be happy with our way of doing things. I just hope this has shown you a couple of other ways to try add it all up....

Thanks for reading, and happy spreadsheeting!