Hello, welcome back to Better Sheets. Today on this video I'm gonna talk about how to take a list of emails and figure out which domains count the domains. Few reasons we might do this is maybe we have a newsletter like I do, and we wanna find out which companies it's of like a business newsletter which companies subscribe to us more than others.

we may want to find out what percentage of employees actually subscribe. We might know their employee count some other way, like through their LinkedIn or something to find out, you know, who should we talk to about getting more subscribers from their companies. But also we might wanna reward refers.

I particularly right now I have a newsletter referral program, but, Before that I used Google Sheets to figure out which companies were subscribed to me more than others, to find out who was talking about me amongst their office. In fact, it helped me figure out that I should have a referral program to measure a veteran and, and, and find out who are my influencers, who are the influencers inside a companies that are actually not just talking about me, but actually finding success, getting me subscribers.

In this particular case, I have a list of journalists that we're gonna go through. You might have a list of journalists, but you wanna find out which publications their work at. You might wanna find out what is the formula of their names. So you might see one but you wanna see like a group of them together.

If you're doing sales and you're doing it maybe amongst a group of people, or you're doing it over a long period of time and you've collected a lot of email addresses that you're doing cold emails you want, might wanna do some statistics to find out, like, are you reaching out to the right companies?

You might want to find out, are you reaching out to the right people at the right companies? And you wanna pull together all of that emails and find out what, which companies are you reaching out to the most? Yeah, there's a lot of reasons we will want to count the domains and here's how you do it.

We're gonna do it today. We're gonna get to the count today, and then in the next video, part two we're gonna. , add a few more interesting formulas, go a little more advanced and be able to save the data for every month so that you can keep this Google sheet. If you do this along with me, you can keep this Google sheet and then just replace the initial data set and every month you'll be able to see month over month your gains.

You'll be able to see. Did you know, did you go and talk to those? Particular influencers or companies. Did you get more subscribers month over month? You can actually compare the data month over month. But we'll do that in the second part. Today we're gonna import the list. We're gonna split the list around the at symbol, then we're gonna count using account if, and we're gonna run into a problem here.

But I'm gonna show you how to get around that problem and get through that challenge. And then we're gonna sort it and you're gonna see the top. So I'm gonna import my list here. Just go to file import, and it should be a CSV file. You can get this from MailChimp, you can get this through or any crm.

You might be able to export a list of emails. I'm gonna just select this. I have actually three email go through. They're all journals. They're all publication. I'm not sharing with you my private email list. What I'm gonna do is insert new. , we in the second part, when we do this again, we're not gonna insert the sheet, we're gonna be replacing it, but right now we're gonna insert it.

And that's import when we split it, we're gonna use the split function anytime. So we have our imported list. That took a little bit of time, so I wanted to pause the video. We're gonna split count and. Now you absolutely can do all of what I'm gonna do, all in the same sheet. You're more than welcome to do that.

I, however, just in the best practices that I've learned, I like to do these on separate sheets so that I can replace this initial list. So I have this list. Here's names, email addresses are in this column. I don't want to do it on this list cuz I don't wanna have to copy and paste. I just wanna be able to import this and move it around.

So what I will do is I will create another tab here, call it split. I'm just gonna do my work on different tabs count and on the split tab. So to pull, we just want the email list. So we want column C, we're gonna use an array formula. Array, if I can spell it correctly. Array formula. And then all we need is a range.

So we're actually gonna go click over column C and parentheses and we have our list of email addresses to work with. And it's a really simple formula split. It takes two mandatory arguments in the formula. And it just needs a text and an eliminator, meaning what, what do we, what do we separate? How, where do we split it?

So we need to go a two comma, and how we split it is in quotation marks at the at symbol bolt. That's all we wanna do right here. And that's gonna go across two columns and then we just do command C copy. What I like to do right now, actually before I do this, I'm gonna copy and paste that all the way down, but I don't wanna go all the way to the bottom of the sheet.

Just wanna go to the bottom of the data we have. So I'm just gonna delete this flow and actually shift command down arrow, select them all. I'm gonna control, delete. I like doing that just to save time here later. . Now, what's interesting when we delete this is that nothing happens right now, but when, if and when we replace this list, and if it has more, it'll add the extra row automatically, but we'll do something else later to deal with changing data.

But right now we just wanna split this so we do command C. I'm gonna copy this. So edit copy.  and then all I wanna do is paste it down so I do shift command down arrow to go down the entire column, command v, paste, and that will paste all. So now we have this great list of domains, but we can't sort this.

Right? Or we like we Absolutely. Again, again, you can do this on your in, in your own. But actually, funny enough, with this array formula, we can't, if we had done this on the original one, we can't. So instead what I'm gonna.  because I'm gonna go do it on account. And in order to count I need to know the unique ones.

I just need one example. So I'm gonna have domains here and count in column B. I'm gonna, I'm gonna use a unique function and unique gets rid of anything that's not unique and it just gives me the actual domains that Great. And now in the B column. I'm gonna do count if equals count. If I need a range, so the range is gonna be split.

This this sheet, we can just go click on it, but it'll be, see, I forgot which which range it was. So we'll have to go take a look. Yeah, it is column C. And then the second argument is just a three. This one, we just want to count anything that goes. Is this,  command, command C for copy, shift command down for V command, V to paste, and then we have all the counts.

Great. Right. But we also, right. We wanted to be able to, we have this, we've split 'em, we've counted them, but now we want to sort them. Right? So we wanna add another sheet. And again, you can do this all in one sheet, but I prefer to do this on separate. So we have domains count again equals sort. And this is gonna take three arguments.

It's gonna take a range. So we wanna sort, actually, we want to do the count a three. , sorry. Yeah. A three colon B to get both of those range columns, which column do we wanna sort by? We wanna sort by the second column or two, and then we choose is it gonna go up or down? And it, and in this case, because it's a count, we want the top one to be the most.

So we do descending, so we actually say no. So the, the argument is, is it ascending, which is going from one. And we say with a bullying no. By zero, by doing zero, we say, no. We want it descending. And we end parentheses something that popped up that you might wait, what's going on? Oh, that's why.

Because we didn't actually say it was gonna be in the count table. And there we go. Now we have our counts. We now see that Gmail is the most. . And then there's C B S Interactive Business Insider Future Net. You know, we got this great list, but there's one challenge that we ended up with. We need a check.

So let me show you in column B. If we take that, we look down here at the Sum 2 69, let me go to. , see how many are in this? 2 69? Well, it didn't actually happen here, , but what's gonna happen is if there are anything, let me make it happen. So like Gmail, let's change this. If, if somebody did this right now to the count and see it's 3 0 7, sorry.

Let's go to some. It's 3 0 8 here, so there's 308 in this list, but really there's only 269 in the list. So why did that happen? It's because the count if. Formula will count capital Gmail as a different, unique one than under lowercase. So it actually puts them both in this list and counts 'em both. But the count, so the unique function says they are not u they're not the same, they're different.

So count the, and then the count function says, oh no, those are the same. So we have this 39 and 39. So to get rid of that, In our split. So when we did this array formula, what we're gonna do and, and, and we can only do this with email addresses because we know that uppercase and lowercase doesn't matter.

We put a lower formula around this and what does is it makes works. There we go. See it had to insert more row, so. For some reason and we put everything lowercase and now we don't have a problem. Cool. So what we did is we imported our list. We split the list, we counted the list, and then we sorted the list.

And next time, join me in part two. We'll figure out how to do this. Month to month. We'll create a, summarize, a summary table, and we'll do some cool join filter tape formulas to See which which people, what are, what are the, like the formula or the style of their email address like,