Figure Out Frequency

About this Tutorial

Fid out how to get the frequency of ProductIds, or Names, or Emails. or how many times they appear in a sheet/column/row.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div> 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 AB B at the end, C D D E, but it also has ab, 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 sheet 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 are, there's actually less.  ones that are repeating, but, 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 want to figure it out. So we'll just do equals unique . We're gonna use the range B, colon B, and we'll hit enter. Now what we get here, we actually get the ID as well. We can get rid of that if we just do b2. 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. 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 parenthesis. Now we get auto fill . .

Now if autofill , if you happen to catch this sometime, most of the time I don't catch this, I hit enter twice and I don't get this autofill again. You can hit command, enter here to, to autofill . But let's say we don't, let's, let's say we miss it somehow and we're like, oh, shoot, like actually that seemed very helpful. We wanted to autofill , 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. Alright.  again. What you might be doing now is you might be like selecting these columns and going to sort sheet zk, not seat 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 as 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 mean 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, I get this like 33% of the time wrong. So we're gonna rate false and we're going to 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. 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 threes 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 want to 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 want to 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 autofill right? But we don't need autofill . We can just hit enter. Again, we have nothing. If we don't need autofill , we can just double click on this.

And we got the, all right, now we're gonna create another tab and we are 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 cuz we want two. Both of those columns, we have, the short column is gonna be two because not the first column. We want the 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, quarter.  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, in 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 put in the count in 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 equal 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.  this at the bottom few times. Let's say let's say 900 times, just saying 900 times. Go back to our sorted. There we go. We have a hundred.  of this. 95. 89. 89. There we go.

We, our sorted and our counts 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 double. . 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 want to 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.</div>