“More than the sum of its parts” – cliche?  Sure.  Have I used it?  Heck ya.  I’ve always loved how the saying has a ring to it.  And how it suggests that collaboration is the way to get stuff done.  There’s only one problem – when you’re doing basic addition and subtraction in a spreadsheet, ideally your total adds up to EXACTLY the sum of its parts – no more; no less!  When you’re setting up your Sums, Subtotals, SumIfs (and more…) – you might end up with more than you bargained for.  Here to help you make sense of it all, the Spreadsheet Whisperer.  (Thanks, Eileen F., for inspiring this post!)

 

Put one and one together and there will be better weatherSum2.PNG

GoogleSheets is a pretty amazing mathlete as long as you set up your data properly!

Here are some silly cartoon volunteers who have been supporting our social movement organizations.  We need to figure out how many total hours were volunteered.

You can see in the example that there are THREE different addition formulas (plus one extra trick!) that you can use to get to get to the same answer:  54.  Let’s break that down.

“I will always plus you”

If you need to add two cells together, the formula “= CELL + CELL” is your best bet.  You don’t need to remember anything special.  It’s just like you learned in elementary school!  You can see an example up in cell B10 in the screenshot above (I changed my GoogleSheets settings to “view all formulas” for these screenshots – you might want to do that, too, if you are ever trying to troubleshoot a formula issue).

However, if you are adding up LOTS and LOTS of individual numbers or cells, you might get to a point where you don’t want to click (or type) each and every cell.  (That’s a good way to end up with more than the sum of your parts — you might accidentally click a cell twice and double count it!)  If that happens, you’ve outgrown the “+” and you need to move up to the “=SUM( )” function.  Don’t worry, you got this!

SUM( ) like it hot

“Function” may sound intimidating, but trust me, the SUM() function is a breeze.  All you need to do is type, =SUM( and then start selecting cells.  You can select your cells in any of three ways:

  • Select cells using your mouse or trackpad to highlight all of the cells you want to add together (they will be surrounded by a blue border) (See cell B11 above or below)
  • Select one cell at a time, this time separated by COMMAS (no plus sign in sight!)
  • Type in your cell codes (for example, A1, B2) separated by commas

Selection biassum3.PNG

This one is my favorite – but then again, maybe I’m biased 😉

All you need to do is select all of the cells that you want added together, and GoogleSheets will show you an auto-sum in the bottom right corner of your screen!  I use this if I need a quick answer or if I need to check my work.

ADDing up some more resources

There is so much to cover when it comes to math and formulas in GoogleSheets (let alone Microsoft Excel) but I need to wrap it up for the night, so I am going to leave you with some links to explore for even more addition tips and tricks.

  • Subtotal function– now available in GoogleSheets!  (Yes, I know that this link was published in 2011 but I’m still learning to love GS… so work with me here).  Subtotals are great for when you want to “sort and filter” your data and take the subtotal as you go (whereas the Sum() function would always add everything).   I especially like this article because the examples they use are pretty relatable (summer barbecue sounds really good as the East Coast preps for yet another snow storm)
  • Subtotals con’t – explained with more detail in this awesome tutorial! Best part – Subtotals automatically get EXCLUDED when you are adding up a whole bunch of data – so you don’t have to worry about double counting.  This is extra useful when applied to budgeting!
  • SUM IF ( ) function – oh man, this one can be a doozie so I will save a more detailed explanation for a future post on TDAA.  If you want to dive in, scroll down to Example 7 at this link!

Ok, it’s time to say goodbye so that I can prep for a conference entirely dedicated to the art and science of Mail Merges.  Signing off with a pun:

How do cows do math?

By adding one number to an udder one!

4 thoughts on “Not more than the sum of its parts

Leave a Reply