How to Iterate Counting Blank Cells in Google Sheets

Intermediate

About this Tutorial

Count different types of blank cells. Cells with nothing in them.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

ARRAYFORMULA() IF() ISBLANK() MAX()

Video Transcript

<div>0:00 Hey, Ben has this pretty funky a question over on Facebook. We're over here on a facebook.com/groups/i love Google sheets. If you are watching this video on YouTube, then come join us. 0:15 We love Google sheets. You probably love Google sheets to then ask this crazy question. So he's trying to convert a CSV file into an SRT file, and he has these, uh, essentially groups of texts in which he needs to enter a new number and iterate through that number . 0:34 Um, every time that there's a blank cell.

And so you see here, he's iterating already, and he has to do this for 4,000 lines. 0:45 Let's see if there's another, there's another paycheck. That's a 31 32 33. And this is going to be a bother if you have to do this manually. 0:51 So I actually came up with a pretty, I think, unique solution. Hopefully it helps.

It's basically the idea is I broke it down in like, well, we know if it's a blank cell by the is blank formula . 1:04 The only thing we don't know for all of these is what is the last number that was used, because if we knew that, then we could just iterate one. 1:14 We could just add one to that number . So here's how I solve this problem. I first have a bunch of numbers. 1:22 Here are cells with non blinks, and then I, I put some blanks in.

Um, and then I added 1, 2, 3, because manually we can add the first one, at least maybe the second one third one. 1:36 But like after that, we don't, we just don't want to do it.

We want to make it programmatic or automatic in some ways, this is sort of the semi-automatic way. 1:44 Here's the solution I did is, and I will, I will break this down after I walked through this or I will walk through it after I share , if is blank of this cell B 16 max. 1:57 So in the tr if that is true and it is blank, then max array formula a five to eight 15 and add one, all right, what did I do here? 2:06 I'll explain them. Then I'll break it down. I took an array formula of the 10 cells above it.

And I thought that would be like, I guess that would be correct. 2:15 Maybe you might have to increase that or decrease that depending. Then I took all of those numbers and found the max maximum max. 2:23 I found the maximum number and then I added one. And now as we go down, we can see this, uh, formula will change, right? 2:31 We 17 B 16 becomes B 17. It's a copy and paste it.

Uh, and then max, uh, eight, five to eight, 15 will become a four to eight 15. 2:44 The first thing I did is I did this and I pasted it and I got a false, but I did get the correct number , but I got all these falses and I had to do one extra thing, which is after the, is, uh, in the, if I had had an extra comma so that there is actually some value for false, which in this case will be nothing. 3:05 And so I did that change and now we have blanks for everything other than a blank row.

So if there's an actual blank row, it'll get the maximum number of the neck of the top 10 cells just above it. 3:20 And then add one. So let me break this down a little bit. If you're watching this video, if you're wa if your bed and you're watching this video, there's your solution, a solution, a possible solution. 3:30 If you're watching this video and this makes no sense whatsoever, I'm going to try building this, uh, piece by piece. 3:36 Okay. So first thing we know is this formula is blank.

Let's start there and we do B we can, we want in there, this is going to give us a true false statement. 3:50 In this case, it is false. Let's just copy paste this down row by row and see what happens when we actually get a blank. 3:58 We get true. Okay? So this is just a basic, true false it's going to give us a true or a false anywhere we go, we, then we can add an F and we do the same thing is blank. 4:15 Uh, B one, let's do the same one. Now we have two more things we have to deal with. We can put two commas in here and pursue them. 4:23 That's the whole thing.

If it's true, meaning if it is blank, it's going to be this first value in this. 4:30 We're going to say, Hey, it's George's and put some text here. If it's false or gonna say, Hey, it's false. 4:39 This doesn't seem much different from what we just did, right from this is blank. It's still giving us one or the other. 4:50 The only difference is that we can edit this. We, we can say what to do when it's true, what to do when it's false. 4:56 And in my case, and this case for the solution, I want nothing to happen. So we'd literally just delete this text. 5:03 Hey falls.

So we'll move it over here. We're going to iterate at each word each step of good when you call them. 5:12 So we're going to just delete this a false part And let's see what happens then. So we actually get nothing, which is actually correct. 5:22 We D we want nothing. And now wherever we have a blank, all the way down, it's going to give us, Hey, it's true. 5:29 This is perfect. This is the next step. All right. Now, when it's true, now, here's our problem. We don't want texts. 5:36 We want a number . Well, here we have one.

So, well, let's just give us, give ourselves a one to start off with. 5:42 We don't need them to do that automatically. We can find the first blank and we next 4,000 lines. We definitely don't want to do it on a non-automatic automatically, but in this case, okay, we know that in the cells , above it is the number , and let's just do something here. 6:04 Let's do equals array, formula , A one, colon, a six. Why I'm choosing that is I'm in the seventh row. And so this is getting the six, uh, six cells above it. 6:25 And you see where it put it, put it in one here.

And so that's not really useful, right? But we got the one. 6:33 Well, how do we get from this array formula ? How do we get just one number ? And we want the biggest number . 6:43 And in this case, we only have one number , but we only want it in this one cell. Well, we can do a few things we can do, like, um, there's no other, what we want is maps. 6:54 That's the answer here. We want max value of all of these max takes an, either a one thing or an array of things. 7:03 And we haven't arrived because we've used array formula . And now we have one.

Now this doesn't really help us, right? 7:11 Because we have other things to deal with, right? We just, one is not the correct answer here. What we need is two. 7:23 So we can just go to the end of this add plus one. And there's two. So now we have taken whatever is up here, found the maximum number and added one. 7:37 Well, how do we put the true false and the, uh, is blank and this together, what we can do, let's move each column. 7:46 Let's go to this column and copy and paste that to G seven. Okay.

And now we're going to grab the formula . 7:56 We're going to double click grab, but just the max, not the equal sign. We want everything, but the equal sign. 8:02 We're going to copy that with command C. Now, instead of, Hey, true. We don't want texts there. We want this formula that we just made. 8:12 Max array formula one through six plus one. Let's see what happens. We have a two. Now, if we, the only thing we have to do is copy paste this to the next row. 8:22 And if nothing happens, we are happy. Happy. We are happy. Now here's the big test.

Number three, we want this to be number three. 8:32 If we are correct, we have done it correctly. We have a number three. Awesome. I think it might break down if this array doesn't have that four. 8:44 Okay. I thought that might not work, but now we have the correct answer all the way down and we continue to have the correct answer. 8:54 It's just going to take up. Here's not the correct answer. There we go. We've got a seven there, seven, eight, and that's the answer. 9:02 So we have created this formula . We can copy paste the entire column down.

It's going to change this <inaudible> is going to change up what the number 's going to change up. 9:11 One, this a one, a six is going to all change up one and we will get the correct fit thing. 9:18 I thought this was pretty unique , a solution to this problem. And I also thought it was a pretty interesting, unique problem. 9:25 So hopefully if you'd like this, uh, solution, join us over on the Facebook group . facebook.com/groups/i love Google sheets, but.</div>