Today's video is brought to you by TMetric.
TMetric is the simple time tracking app to help you manage every minute of your day.
Are you a freelancer and need to keep track of billable hours?
Well, TMetric makes invoicing those hours to your clients so much easier.
And best of all, TMetric integrates directly with some of your favorite tools including Google Docs, Gmail, Asana, Trello, and so much more.
To try TMetric for yourself, click the link in the description below.
Have you ever been working within Google Sheets?
Overview
And having multiple sheets of data and wishing that you could sum them all up together in a separate sheet?
Well, in today's video, I'm gonna show you how to do just that.
Hello everyone, Scott Friesen here at Simpletivity helping you to get more done and enjoy less stress.
And you know, sometimes there's good reason why we have multiple sheets for different pieces of information.
Maybe this represents different managers or members of your team.
Maybe this represents different sales areas or sales regions, for example.
So you may want to keep them separate because people are entering information just for their area, but you'd like to total them all up in a separate sheet.
Now it's true, I could take the information for example, from B and C, I could cut or copy it and bring it over here to A, but I wanna show you a much better way of doing this, especially if you are gonna be adding more information in the future.
So here I've got a tab, a sheet called Total.
Tutorial
And the first thing that we need to do is we wanna make sure that we have our headings that are exactly the same as the other sheets that we are gonna combine.
So I'm just gonna copy and paste that over here.
Now the next step that we are gonna add here is we're gonna add a formula.
So in the very first cell, just below date, I'm gonna hit that equal sign.
And what we're gonna do is we're gonna add a brace or sometimes better referred to as a squiggly bracket, not just a regular bracket, a squiggly bracket.
And we're gonna come over here to Team A, and we're just gonna select all of the data that we want.
Now we're gonna hit our semi-colon button on our keyboard, we're gonna go to Team B, and do the exact same thing, we're gonna select sorry, all the information that we want from this sheet, we're gonna hit semi-colon once more, go to Team C and once again, we're gonna select all of that information as well.
We're gonna close things off by hitting that squiggly bracket once again, at the end of our formula there, hit Enter and now here on our total sheet, we have everything from A, B and C.
If we look down here, says we've got 30 rows, now let's subtract one, right? Because we've got our header up here.
So we really have 29 rows.
So if we go back here to Team A, remember to subtract one, we've got 12 plus six, that's 18 plus 11.
Yeah, that adds up to 29.
So we've got all of that information here on this particular tab.
So that's great, it looks like we're finished right?
Well, maybe there's one problem.
Maybe this is all you need for right now.
But what if you are planning to add more information to you know, one of these sheets over here or other members of your team?
Let's say I'm here on Team B, and I'm gonna add a new entry here.
Okay?
I'm gonna add, let's add myself, just so it stands out from the rest of the list here.
Let's give ourselves a state, let's say I'm selling binders and let's say I am an absolutely horrible sales executive.
All right?
So I've added my new data here, perfect Team B.
If I go over to my total area, if I scan down and I'm not seeing any zeros here, I don't see my name here, that's a bit of a problem, right?
Because I want to be able to add information over there and have it show here but it's not happening.
I've made that new entry, and it's not showing up here.
So let's see how we can tweak our formula up here so we can do just that.
So a couple of things at first, we're gonna come in here and we're gonna look at the cells that we're referencing.
One of the problems is that we are saying to finish at E13.
We wanna finish at that row but of course, as we're adding things, we're gonna be going well beyond that.
So the first thing that we wanna do is come in here and actually remove the row number.
We wanna leave the column of course, column E, but we wanna come in here and remove the row number.
So E7 becomes E and over here E12 just becomes E as well.
But watch what happens when I hit Enter and change this formula.
So, what happened here?
So this is team A, right?
That looks like Team A has come over and you may be saying it didn't even bring over Team B and Team C.
Well, actually, you'd be wrong.
But here's the problem, I have to scroll all the way down to I think about the thousands, somewhere around there and yeah, look at that.
There's Team B.
And if we scroll down 1000 more, we would get to around Team C.
It is grabbing all of those empty or no cells.
Well, that's a problem, right?
That's certainly not what we want.
So let's go back in here and alter our formula once again.
We're gonna leave those Es that the way they are, but what we wanna do is add a query.
So at the very beginning of our formula, we are gonna type in the word query and an open bracket, all right?
So an open bracket here is gonna allow us to have this query.
We're gonna keep everything that we left in there, everything that we left from that original formula minus the row number and we're gonna come to the end here and hit comma.
Now we're gonna hit our quotation marks and we are going to enter in the following, we're gonna say select, this is how we're gonna make sure that we're only getting information that contains values.
We're gonna say select asterisk, that's sort of our wildcard, right? The star.
Select where column one, and that's capital C-O-L one, that's looking at column one or column A, in all of those different sheets that we're referencing, select star where column one is, oops, Did I put in another space? Is not null.
Okay?
So whenever it's gonna go back and take a look it's only gonna bring in information where there are things where there is information in that date column.
And lastly, we wanna hit our quotation marks to close that off, and one more we're gonna do our bracket to close off the full equation, let's hit Enter and hope that this works.
Okay, looks like we've got a lot more information here.
Do we have all the same information we had before?
We have all the same information and then some, hey, there's me, there's my poor sales job of selling absolutely nothing.
So there's that new entry we made, now it went and grabbed that information from Team B.
Let's do one more quick check test just to make sure that this works.
I'm gonna put in a new entry here on Team C, this time, let's say, let's call this one Jenny Sample also from Washington, let's say, and she's selling art and she is an amazing salesperson.
Sold $45,000 worth of art the other day.
So if I go back to total now, hey, look what's showing up at the very bottom.
So now with that new formula that we put in on this total sheet, we can continue to add new lines, new information to these sheets and they will total up perfectly here on this final sheet.
Well, I hope that you found today's video helpful and I would love to hear from you next.
What other tips would you like to learn about Google Sheets here on the Simpletivity Channel?
Be sure to let me know in the comments down below.
Thank you so much for watching today's video and remember, being productive does not need to be difficult.
In fact, it's very simple.
