Figure Out Frequency of Numbers

Transcript

Hi, welcome to today's video. Today's video is gonna be counting numbers or counting IDs, counting text emails. This came about in actually in a very interesting, unique way. It came through Twitter. You can go follow me on Twitter at @kamphey but here on YouTube, I wanted to help you figure this out because the person I helped out on Twitter.

Said it worked. It was exactly what they needed. They described it in a different way than I would've described it.

What they were trying to figure out was the numerical frequency. That's how they described it.

As you see the title of the video is figure out frequency, figure out the number of times something appears within a list of data.

We can use this in a lot of different ways. I use these formulas, which is countif() unique() and even sort() a lot of times when I'm trying to figure out the business of how many, how many people are buying certain things from me. So I sell products on Gumroad. And I have lots of different products, so I have lots of different transactions, but unique buyers will have an email address.

And so I actually use this same formulas to figure out which of the people that are buying from me are buying frequently. I'll segment them into different categories based on this frequency. It's very useful also in this particular case. The video I made for you figures out product ID or some semblance of like letters and numbers.

So numbers and letters are combined as a product ID. You can use this for numbers only, or text only as I said, email is how I use it to count email addresses.

If you are having difficulty implementing these three formulas, a few things that might happen as you do it is if you're trying to do it in a sheet that already exists, you might have stuff in the cells. So you might get an error that says cannot overwrite cell C54 or cannot overwrite cell A19. The thing you need to do is go to that cell in the error message and delete that number.

So that's one roadblock that might happen for you. One challenge that you might run into.

If you are interested in getting the sheet that I reference in this. Video and you are not yet a Better Sheets member go and become a Better Sheets member at BetterSheets.co

You can get a free membership, which allows you access to now over 60 videos on BetterSheets.co track your progress.

And if you want anything else become a monthly member for $19 a month. And you can also get a lifetime membership, which gets you access to literally everything. You get, every script, every sheet, every video there's over 190 videos. Now on the way to 200 videos.

Thank you for watching let's go figure it out now.

hey. So in this video, we're figuring out the frequency of in this case IDs or some kind of product IDs. You can do this with even emails. I do this personally when I try to figure out who has bought transactions, who has executed transactions with me the most amount of time. So I'll get, say 8,000 emails or 8,000 transactions with people's emails.

And then I will figure out through this process who actually. does the most. So what we might have is a list of, again, product IDs or some kind of numbers or text that is repeated some number of times, if you can see here, this 5 99 number has a B at the end, C D D, but it also has B again, C D D E here's AB again, and as we scroll down, we then get another set of sort of.

Some very similar numbers and then we might get a complete random numbers throughout. Maybe we have transactions.

So you might have something like a thousand rows or a hundred rows. In this case I have 80. This is very helpful when you have thousands of rows.

And what we'll do here is I will show you what you usually do. What you might do is something like sort cheat and sort this column. Sometimes then you might like, look at this and be like, oh, here's a unique one. You might have maybe only a few uniques meaning like five or six. But sometimes like in this case, there's mostly uniques there.

There's actually less. Ones that are repeating. So we're gonna do a use a couple of formulas here. We're gonna use unique and we're gonna use count if, to figure this out. You don't necessarily have to use the sheet you're on. We can do this on a separate sheet. And actually I prefer that, but I will show you that second right now.

This is usually what you do. You try to figure out something really quick. You want to get the data right here. You just wanna figure it out. So we'll just do equals unique. We're going to use the range B colon B, and we'll hit enter. Now what we get here, we actually get the idea as well. We can get rid of that.

If we just do B two. I can do that. And we have much less numbers here. We have 35 numbers, whereas in our normal row we have 80. So the moment you do this unique, you'll see a stark difference in the length of your array or length of the numbers set you have. Now next to this unique column, since this is individual ones that are located on here, we need to know how many there are.

So we're gonna do count if. count if co not colon parenthesis. Now the range is going to be again, B colon B, same range as our unique. We're gonna hit comma. Now, what is our criterion? Criterion means we need to have like, something to count. So we go here, we just select D two. Now, if you're using something like a filter formula or function this is gonna be a little more, bit more complicated.

You're gonna do something like B to B equals D two, but in this case we're gonna get zero, right? If we do that same thing as we do in filter we'll know right away that we're wrong because it's zero and we, we know 100% that this 1 41 number. Exists in this column. I mean, we can see it right there at the top.

So we know we've been, we've entered something wrong, so we'll just hit delete. Now we have D two and we end the parentheses. Now we get auto fill. now if auto fill, if you happen to catch it, sometimes most of the time I don't catch this, I hit enter twice and I don't get this auto fill again. You can hit command, enter here to, to auto fill, but let's say we don't let let's say we miss it somehow.

And we're like, oh shoot. Like, actually that seemed very helpful. We wanted to auto fill. We wanted to know the count. If, well, you probably com command C command V copy paste. You probably command paste all the way. but another much more faster way is go on the bottom corner here. E two double click and it auto fills.

And now we see this 12, 7, 5 count. This is great. We now have our count. Yes. As we can see, most are one. All right. again, what you might be doing now is you might be like selecting these columns and going to sort sheet Zita, not see sheet it'll be like range. Like you might select this whole range. And you might say, oh, I want to, this is, might be what you're doing.

Data sort range. and totally okay. If you just need something quick and you want to do a couple clicks, but actually I find that this what I'm about to do much simpler and easier. I just go to a next few columns over. I do equal sort. Now I take the range of D and E and the sort column is going to be the one with the number.

So that's gonna be number two, we have D is the first column. E is the second column. And is it ascending? This is the hardest part of this whole. Honestly, I almost get, I almost get this wrong, like 30% of the time. Is it ascending? Meaning do you want the result to go from lowest to highest ascending where actually in this particular case, we want it to be descending, which we means we want the highest.

Of count at the top and the lowest count at the bottom. So in this case, all right, again, I get this like 33% of the time wrong, so we're gonna rate false and we're gonna pray, like, come on, let's pray for this. What, what do we actually get? We got it right. There we go. We got number 12, the 12 count at the top, correct?

Again, this is like the hardest part. If we write true here, you'll know immediately that it's actually, you won't know immediately. It's wrong. If you don't know that there's multiple things duplicates. If you do not have any duplicates, you're going to get a bunch of ones. If you have. Are you, you're actually counting repeating numbers or repeating text or repeating email addresses.

You will get a number more than one. So that's something to look out for, but my, how I like to do this more is I like to do this on separate sheets. So actually I'm gonna go and show you that because right at this moment hopefully you can see what. Is sort of the problem of having all of this data on the same tab is it's very hard to share.

Like what someone else might not know these formulas, they might might not know that these are three separate columns. That mean three completely separate things. My preference is to do separate tabs for each of these. So I will go and do that now, actually I will. All of this, I will make life a little bit easier.

I'm gonna delete the first column and have everything in the, a column. There is one other reason why you might wanna do this on separate sheets. And I will explain that in a second. So let's make sheet two. Let's go to we're gonna do the same unique sheet one. This is why it makes it easier. Now we know we a hundred percent know it's in, in column a, so we can just write it without without thinking much about it.

Now we need the count. So actually we might wanna label this count ID and count equals count. If we're gonna do our count. If now it gets a little bit more difficult. We're gonna do sheet one. You do have to get the capital capitalization, correct? I do believe sheet one. and again, we know the where it is.

So it's a to a criterion is gonna be a two. So that's not that hard right there. A two. Now, if we hit enter, we're gonna get the auto fill. Right. But we don't need auto fill. We can just hit enter again. We have nothing. If we don't need auto fill, we can just double click on this and we got the auto fill.

All right. Now we're gonna create another tab. And you gonna actually call this sorted. I, I do that a lot. I try to. The tabs by how they are, what they do. What's the action they're taking. So sorted. We're gonna just go equal sort. Now what's the range because we renamed our, our sheet. It makes it much easier.

We're gonna do count a colon B cause we want two, both of those columns, we have the short columns gonna be two because not the first column we want that count the ascending. Remember we have, I think it's gonna be. There we go. Perfect. We got the answer. So now we have assorted tab. We have a count tab that does the work of the counting, and we have the sheet that has the number.

Now, why would we want to put it on separate tabs? Here's one big reason is that if you are doing this count, once you might do this count again, you might do this count weekly, monthly, quarterly. Yearly daily but you might also have more transactions. You might have more ideas to look through. You might have more products.

You might have more this each time you do this, or at various times. And if we have everything on the same tab, we, we make a possibility where the. Formulas at the top are not in the right place. Sometimes say you're using a Google sheet to input information in sorry, a Google form. You're using a Google form to put information into this sheet.

Well, a Google form will insert a row at the top. So then if we insert 10, 20, 30, 50, a hundred things, our, our formula will move all the way down. So we don't really want that. So I like to keep a data sheet. I will say, Hey, I'm put in the count and the sorted formulas, the formulas don't end, they go the entire column a to a right.

We didn't say a two to a 20. We said a to a, a column a so if you add things, if we go here, we'll, we'll just take all of this and we'll put it at the bottom. Now, our account's gonna be different. I don't know if you remembered our account. It was like 12 is the top now 24, if we add. this at the bottom few times, let's say let's say 900 times just saying 900 times, go back to ours.

Sorted. There we go. We have a hundred. Of this 95, 89, 89. There we go. We, our sorted and our accounts are completely automated and are updating automatically as we add data to this sheet. And we don't have to worry about moving formulas around. We don't have to worry about updating it, copying and pasting anything.

We have all of our data. We can add another thousand rows and we'll see everything is doubled. There we go. Everything is doubled. This is a really fun way to figure out frequency of product IDs of, of email addresses, transactions people who took transactions names. Sometimes you're looking at sort of a list and you, you know, you do have other options for D finding duplicates, but this is also a cool way to find a duplicate if you want don't wanna be destructive.

So we do have. data. We do have a, a duplicate detector in Google sheets, but it's destructive. So you might want to just say, Hey, there's two owls or two fills here and figure out what you do in the next stop. Stop. So count if is a really fun and exciting formula to be able to use. I use it a lot.

Thanks for watching. And I hope you make better sheets.

If you are watching other videos here on YouTube and you see a script that I write become a Better Sheets member and you can copy and paste it immediately. Once you're a lifetime member.

If you haven't yet checked it out, I would check out AtomicSheets.com, which is a new product from Better Sheets. It is a component library of Google sheet tabs. So if you wanna add something extra to your sheet, go and check out AtomicSheets.com.

Click subscribe to become a subscriber. I know like 50% of people who watch this video are not gonna be subscribers, so become the other 50%. And I hope to see you in the next video.

Watch the whole video