How to Count the Occurrences of a Number or Text in a Range in Excel

Next Video:
How to Count the Number of Unique Values in a List in Excel....5

Counting the occurrences of a number or a text item in a range in Excel is something that you can do by looking at each column individually. Count the occurrences of a number or a text item in a range in Excel with help from a mechanical engineer with 32 years of experience in a large aerospace company in this free video clip.

Part of the Video Series: Using Excel
Promoted By Zergnet


Video Transcript

Hi, I'm Ted. Today, I'm going to show you how to count the number of occurrences of a number or a text item in a range in Excel. I have a spreadsheet set up here. It happens to be the list of the members of the Baseball Hall of Fame, just to have something fun to, to demonstrate this on. And what I want to do is I want to show the number of players from each state. So column F, as you can see, has the state with the two letter code. Some of them aren't listed because they're, they weren't born in the US. But what we want to use to do this is the count if function. So I've got a column started, and I've just got a few of the, a few of the states listed here just to demonstrate it. And we're going to go into this cell, and I'm gonna type in equal, which is the way you always start a formula, and I'm gonna enter in count if, count if, and you'll notice that Excel recognizes it, that that's a formula, it's helping me along here. So we've got count if, and the first thing, the first argument, in other words the first entry in the function is the range, which is the range that we want to count. So I'm gonna go over here and I'm gonna select all of the entries in column F. So I just start at the top and drag down until I get to the bottom, just bare with me, we're almost there, and now we're done. Whoops we went a little too far. There we go. All the way to the end. Now I'm still in edit mode in my formula, if you look up here, and we go all the way to the top, and now we type a comma, and the next entry is the criteria. Criteria is going to be the state, which is the entry in column I. Now unfortunately this is rather annoying, but Excel does this, it's got my formula bar over the place where I want to be, so I actually man, you have to manually enter in cell I2, so I'm just gonna type in I2, and then I'm gonna type a closed parenthesis, oops, I got it the entry in the wrong place. I have to put my cursor over here, and I'm gonna type in I2, close parenthesis, and hit a return, and there we go. So Alabama, AL, there are 12 entries from Alabama. Now, what we want to do of course is copy that formula down to all the other ones, but before we do that we have to make sure that when we copy the formula, as you may know, Excel when you copy a formula, changes all of the relative references of the cell. So we want to prevent it from doing that. We want the range here up in my formula bar. We want the dollar signs before all of the row numbers in just the range here. So we put, it's gonna be F dollar 2, and F dollar 242, that's the range of the, the entries in the state column, and then we hit return, and now when we take this formula and we make our cursor to the right-hand corner of the, of the cell, and drag it down, it's gonna copy that formula to all the other states, and sure enough this is the number of entries in column A for each, for each of the different states. So I hope this has been helpful. I'm Ted, and today I showed you how to count the number of occurrences of an entry in a range in Excel. Thank you for watching.


Related Searches

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!