(music)
- [Instructor] Okay, so if you're using Google Sheets
and you have a table of data,
so that's what we have right here,
and it has different background fill colors in it,
and you're trying to either count or sum the amounts
of each background color, okay?
So if you come in here, let's say you want to grab count
of the total number of cells that have a background color
of green, let's hold down the control key
and then click to select all of the cells
with the background color of green.
And we'll go to the bottom right-hand corner,
so there's kind of a shortcut here.
It always shows you the sum of the cells
that you have selected, and if you left-click on it,
it will show you some other functions as well.
So what we're trying to replicate
with this function is the count.
So you see it's saying there's eight of them.
And if I look at my formula here, it's returning in eight.
So that's a little short proof
that these functions are working and how they're working.
So these are custom functions.
If you look, this one's called sum-colored cells,
and this one's called count-colored cells.
The same function in these cells.
But an important thing to note here is
that the function sum-colored cells
doesn't exist in Google Shoots.
That's not a standard function.
So that was written specifically for this spreadsheet.
I contacted a friend that I know called Brian Wochele,
and he created two custom functions for us.
And this spreadsheet that you copy will also have these,
so we'll take a look at them.
If you go up to Tools and left-click on Script Editor,
what Brian did was define how count-colored cells works
and how sum-colored cells worked,
and those are automatically associated
with this spreadsheet.
So you don't have to write those.
They're going to come with the spreadsheet.
But if you want to look through them and understand them,
you can look there.
And the link to this spreadsheet will be
in the description of the video.
I have a link to Brian's YouTube channel
in the description of this video and in the spreadsheet,
if you want to check him out.
He knows how to write out script.
I don't.
Works well.
So if you come back and you want to use this spreadsheet
with your own data, the first thing that you want to do
is replace this table with yours.
And you might have to move some things around
in this spreadsheet to accommodate for it,
'cause it's not gonna be the same size.
And once you do that,
we're going to talk about what you need to adjust.
The first thing is the data range.
So this cell kind of made it obvious that you need
to update this input if you update this range of cells.
So type the new range into F7,
and then let's look at these functions.
Both of them work the same,
so we'll just look at the function in B20.
I'll double left-click in it.
Sum-colored cells, the first thing that it wants to do
is take the range.
So it's looking at F7.
So we've updated that already.
And then it wants the color.
And the color is just what you've colored B20.
So you'll notice that each of these functions is colored,
which corresponds to the color
that it's summing or counting.
We need to make these colors match these colors.
We'll talk about the format painter in one second.
So you'll make sure you're grabbing the exact color
that you want.
And the last parameter is that it's looking at F8.
We'll talk about why it's looking at F8 is
that there's a similar video to this
that I made a few years ago.
It got pretty popular, and I got a lot of viewer comments,
and people were asking if you could make it count
for multiple colors,
so that's why we've expanded this area down here.
And it doesn't update.
So we made this checkbox here.
If you uncheck it and then check it again,
your spreadsheet will update.
So that kind of gives you a little hack
to get this to update.
And that's why it's always helpful
if you have questions on these, post them in the comments
on YouTube, and if I get enough comments
asking certain things, I'll just make new videos
based on your feedback.
So I love it.
Keep it coming.
The third step that I explained here
is what we just went over.
It's to update these colors to correspond to what you want,
but to make sure that you get the right color,
you want to left-click on Paint Format,
and then that will bring down the exact color
to the cell in C20.
Now you do have to be careful using the format painter,
and this is actually a good example
of why you usually don't use cell borders,
because you can't paste only the background color.
It's also going to bring in the borders.
So let's just quickly go back in to C20 and fix that.
Cell borders look nice, but a lot of times
they just add extra time having to maintain them.
And then the last step, which we also went over,
is just to toggle the checkbox.
So if you change anything in here,
just take the checkbox, uncheck it, and recheck it.
So let's test one.
We have green, instead of one, I'm going to make it five.
So keep your eye on the totals.
This is 66.
Let's move the checkbox on and off, and it updates to 70.
Let's just do one thing here.
I'm gonna change color totals to color sum
to differentiate it from count.
And since we always use the proper format for Google Sheets,
we'll make these function names uppercase.
Nothing like correcting it on the fly, right?
So in our next video, we're going to talk
about how to make an interactive checklist in Google Sheets.
When you check something off,
it puts a strike-through through your to-dos.
Thanks for watching.