So we want to count every row, but by color, basically we want a little bit of a table like this that says, here's the color, here's how many there are of that color. These are hex codes that are those colors. I'm gonna show you how to do that in this video. Let's start. So one of the simplest ways to do that is to go up to Extensions app script . And we're gonna create a function called Get Backgrounds, get B Gees backgrounds, not the B Gees, but the backgrounds. And we're just gonna get all of the backgrounds, spreadsheet app, get active spreadsheet . That get range . Now here we have to be very specific for this particular first case, and I'll show you how to edit this later. Basically we want the B column from B one down, actually B two you can say just the second row. So we'll get the range starting on the second row in the second column, and

let's see how many we want to go down. Maybe we wanna go past this a little bit, but we'll say 50. And then we'll just do dot get backgrounds and the end parentheses, and then we'll return all the backgrounds. We're gonna save this, which we're gonna save this as a project name come by color, but this particular function we can actually use inside of our sheet and it's not gonna do everything for us right away. But it'll get us part of the way there. And maybe this is the simplest option for you if you just want to get something quick. So right next to this row, we'll do get bgs. Ah, we need to actually use active sheet here, not active spreadsheet , not. And one extra thing is if we wanna start typing it and it'll actually come up as a auto complete. We'll do slash and two stars at custom function .

We'll save that. Now we can do equals get bgs. There it is. And parentheses. Now, of course this is only going to get us this particular set of values . I want to be able to maybe enter some data here, the range here and get it. So let me show you how to do that. So we'll say in our function row column, and then column count. These numbers, they have to be numbers. But in this particular case, we're gonna enter the numbers up here when we enter the function . So we'll change these numbers to the variables. It's actually row start. Let's be more specific, row start and column start, which is helping ourselves out later when we look at this again. So row start and column start, and then column count. Let's make this the same syntax there.

So now we're going to get the exact same result, but we're going to do it a little differently, which is we're going to enter GI bgs. We're gonna have the row start, which is two comma column start, which is two. B two and how many columns, actually, that's how many rows. We should change that and we'll get the exact same result. Then we can do unique here, uh, of C column C we can say there's all the unique ones, and then we can do a count if range , and the criteria is here and there we go. We have the exact same result, right? But we want to do this automatically. We wanna count this and let us do that by app script . Let's go change one thing and change this to row count, no column count there.

And we're gonna add another function up here that actually counts all of this stuff. So we'll call this function count bgs. Put a curly brackets here. We'll say our variable of BGS equals get bgs, and that's just calling this particular function here. So we need to actually get all of this data as well. So we'll say count bgs, and then we'll put that data right there into that function . Get BGS exactly as we did it before to return all of the backgrounds that we want in that particular range . And let's get a result. But we'll start. The result is equal to color, comma, and count. And this is an array, and it's just saying, here's the header of the array. Here's where we're gonna start it, and then we're gonna end

up with we're each color listed and each of the counts listed. And we have a variable count, and it's gonna be an empty array for now, or not an empty array. Empty curly brackets. And we're gonna be pushing stuff to that. So we'll take all of the backgrounds, we'll do four each, and we're gonna create a little function here with a row, which is basically every single item in here is a row, and we're gonna do something to that function . That's something that we're gonna do is variable color is equal to. The row data , which is actually square brackets here, basically the, the name, and then we're gonna say counts and in brackets the color. So if it's like red, blue, green, it would be counts, red counts blue. But in this case it'll be hec hex colors, hex codes.

But we're gonna say, Hey, if. You can find this great, get that, get that amount, or with two pipes or zero basically, if you can't find it, it's gonna be zero. And we're going to plus one, which is just add one to that found number if we found it. So this is looping through every single one, saying, Hey, for the color that it is, count it. If it's not in there. Add one to zero there you. But now we need to actually put all of those results into this array of counts. So four variable color in counts. So this counts is now full. We'll do a function result, which is up here, dot, push.

And we'll push the color and the counts with the color. And at the end of this we'll return result. So let's go back to our sheet and see if this is going to enter. We have, have changed the name, so we'll change that. So we'll go equals count. Ah, we forgot one thing. Which is let's create a custom function up here slash two asex at custom function . We'll keep that count BGS as a custom function as well. That's auto complete. That's gonna just show up here. So equal count BGS. There we go. We got a row start, which is two comma, two column start and the row count 50. We'll hit enter.

We have an error . Get BGS is not the fine. Line. Four. I think I just typed it in wrong. Yeah, I just had a capital S there. That should fix it. There we go. We have each of the colors and each of the counts. Isn't that awesome? If you want the app script in this video, check out the description below. Hope you enjoyed that. You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.