You have a list in Excel. You want to know how many times each entry appears in the list. This week, Bill and Mike show a solution using a pivot table or Advanced Filter with COUNTIF.
Transcript of the video:
Bill: Hey everyone, welcome back, it's time for another Dueling Excel podcast. I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from ExcelIsFun, this is our episode 128 - Count Occurrences of Each Text!
Today's question sent in by Muhammed, Muhammed has this list, and he wants to know how many cars of Toyota, how many cabs of Nissan, and so on. Well hey, this is going to be easy with a Pivot table, so we select one cell in the data, Insert, PivotTable. OK, we get a brand new worksheet where we're going build our report, check-mark data dump, it goes down the left-hand side, this is one unique occurrence of every value in the original data. And then take data dump and move it to Values, because that is text, we're done.
I think that's what Mohammed wanted, although hey, let's do a little bit more. I'm going to do Alt D E, for Data, Text to Columns, delimited by a space. And very important here, I'm going to say that this is going to go not to G2 but to H2, that will create two new fields for me. Let's create a Pivot table from this, Insert, PivotTable, OK. We'll put Type down the left hand side with a check-mark, Column Labels across. Ah, you know what, let's put Brand down the Column Labels, Type going across, and then take any text field to the Values area to get account. We're practically done here, Options, For empty cell shows 0, I don't like the blank cells. And finally, Design, Report Layout, Tabular Form, so we have real headings, Brand and Type instead of Row Labels and Column Labels. There you go, a crosstab report showing for each brand, how many bus, cab, car, and van. Mike, let's see what you have!
Mike: Thanks MrExcel! Hey, you know, Pivot table really is the way to go, and here's the reason why: If you really do have a data dump, this means just dumped on your computer one time, the data is not going to change.
Man, messing around with formulas is not worth it, because a Pivot table can do this so quickly.
Now, if you didn't want to use the Pivot table, we could use Advanced Filter, Extract Unique Record. So I'm going to have a single cell selected, like so many of our data analysis features require just a single cell, go up to Advanced in the Filter, or Alt A Q. I'm going to select Copy to another location, it got the lists correctly. One important thing about this list range is, if you're extracting a unique list, you better have a field name at the top that's different than anything down in here. If you don't have a field name, you'll get exactly one duplicate, because it'll think the first item is the field name. Criteria range, we don't have any criteria, but you could have if you're doing a unique list extract. I'm going to say Unique Records Only, that's going to be our only criteria, and copy to, maybe E1, click OK. And just like that I have a unique list.
You can see that that took a few more clicks than that Pivot table, the Pivot tables are so amazing. Count, Ctrl+Enter, Ctrl+B for Bowl, I'm going to use the =COUNTIF, and then the range, arrow arrow arrow (left), Ctrl+Shift+Down arrow, F4, comma, and then left arrow to get my criteria, which is a relative cell reference.
Notice I'm not going to put a closed parenthesis, I'm just going to Ctrl+Enter, and double click and send it down. So there we have counted the unique items from this list, Pivot table's really the way to go. Now, if you want to download this workbook and see something insane, there are some formulas here to extract the unique list from this data set, this is just crazy. Only reason you would ever do something this insane is, if for some reason you had a dashboard, or the data was changing a lot.
Because if it's a one-time deal, do a Pivot table, or if you want to waste some time, Advanced Filter and COUNTIF. Alright, throw it back to MrExcel!
Bill: Alright there you have it. Hey Mike, you didn't even put the right parenthesis in. You know what, the closing parenthesis, just Enter and Excel doesn't give you any hassle about "Hey, we think you've meant to add a parenthesis", it just puts that in.
That's a cool trick, I've never seen do that one before, and here all this time, I've been typing the right parenthesis, so excellent.
Oh hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun!
Today's question sent in by Muhammed, Muhammed has this list, and he wants to know how many cars of Toyota, how many cabs of Nissan, and so on. Well hey, this is going to be easy with a Pivot table, so we select one cell in the data, Insert, PivotTable. OK, we get a brand new worksheet where we're going build our report, check-mark data dump, it goes down the left-hand side, this is one unique occurrence of every value in the original data. And then take data dump and move it to Values, because that is text, we're done.
I think that's what Mohammed wanted, although hey, let's do a little bit more. I'm going to do Alt D E, for Data, Text to Columns, delimited by a space. And very important here, I'm going to say that this is going to go not to G2 but to H2, that will create two new fields for me. Let's create a Pivot table from this, Insert, PivotTable, OK. We'll put Type down the left hand side with a check-mark, Column Labels across. Ah, you know what, let's put Brand down the Column Labels, Type going across, and then take any text field to the Values area to get account. We're practically done here, Options, For empty cell shows 0, I don't like the blank cells. And finally, Design, Report Layout, Tabular Form, so we have real headings, Brand and Type instead of Row Labels and Column Labels. There you go, a crosstab report showing for each brand, how many bus, cab, car, and van. Mike, let's see what you have!
Mike: Thanks MrExcel! Hey, you know, Pivot table really is the way to go, and here's the reason why: If you really do have a data dump, this means just dumped on your computer one time, the data is not going to change.
Man, messing around with formulas is not worth it, because a Pivot table can do this so quickly.
Now, if you didn't want to use the Pivot table, we could use Advanced Filter, Extract Unique Record. So I'm going to have a single cell selected, like so many of our data analysis features require just a single cell, go up to Advanced in the Filter, or Alt A Q. I'm going to select Copy to another location, it got the lists correctly. One important thing about this list range is, if you're extracting a unique list, you better have a field name at the top that's different than anything down in here. If you don't have a field name, you'll get exactly one duplicate, because it'll think the first item is the field name. Criteria range, we don't have any criteria, but you could have if you're doing a unique list extract. I'm going to say Unique Records Only, that's going to be our only criteria, and copy to, maybe E1, click OK. And just like that I have a unique list.
You can see that that took a few more clicks than that Pivot table, the Pivot tables are so amazing. Count, Ctrl+Enter, Ctrl+B for Bowl, I'm going to use the =COUNTIF, and then the range, arrow arrow arrow (left), Ctrl+Shift+Down arrow, F4, comma, and then left arrow to get my criteria, which is a relative cell reference.
Notice I'm not going to put a closed parenthesis, I'm just going to Ctrl+Enter, and double click and send it down. So there we have counted the unique items from this list, Pivot table's really the way to go. Now, if you want to download this workbook and see something insane, there are some formulas here to extract the unique list from this data set, this is just crazy. Only reason you would ever do something this insane is, if for some reason you had a dashboard, or the data was changing a lot.
Because if it's a one-time deal, do a Pivot table, or if you want to waste some time, Advanced Filter and COUNTIF. Alright, throw it back to MrExcel!
Bill: Alright there you have it. Hey Mike, you didn't even put the right parenthesis in. You know what, the closing parenthesis, just Enter and Excel doesn't give you any hassle about "Hey, we think you've meant to add a parenthesis", it just puts that in.
That's a cool trick, I've never seen do that one before, and here all this time, I've been typing the right parenthesis, so excellent.
Oh hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun!
