Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Sometimes, rather than finding the sum or average of values in a range of cells, you'll just want to know how many cells in that range contain a number, any value at all, or are blank. Excel has several functions that count the number of cells in a range that contain a particular type of data. All these function are versions of the count function. The count function counts number of cells in a range that contain numbers. So let's say that you use a worksheet to track the bottle breakage rates at your warehouse. If you want to count the number of days where you had any sort of breakage, you would want to count the number of cells in this range, B2 through B10, that contain a numerical value.
You might put an n/a to indicate that you didn't take a measurement that day, and you can also have a blank cell just in case you forget to enter a value. So, the count function that I'll show you will take care of all of these scenarios. So now let's creates a function that just counts the number of cells that contain a numerical value. For that you type =count, and then and a left parenthesis. Now all you need to do is type in the range of cells you want to examine: =count(B2:B10), and you get the number six.
So you have one, two, three, four, five, six numerical values, and that corresponds with our answer. Now let's say that you want to count the number of cells that contain any value at all, in other words all the cells that are not blank. To do that, we use the count A or count all function so =counta and a left parenthesis, and again the same cell range, B2:B10) close it up, hit Return, and the answer is 8, and that corresponds with our measurements here.
We have one, two, three, four, five, six, seven, eight cells that contain any value, so those are the six numerical values, and the two n/a. If you want to count the number of cells that are blank, in other words the ones that have values in them, then as you probably suspect, you can use the countblank function. Left parenthesis, B2:B10, hit Return. The formula returns the value one, which corresponds with the one blank cell in our list.
These three variations on the count function help you discover the number of the cells in a given range that contain particular types of values. In larger worksheets using a count function tells you how much data you have in your collection, which can often be surprisingly valuable information.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 75193 Viewers
80 Video lessons · 130042 Viewers
52 Video lessons · 64187 Viewers
59 Video lessons · 49989 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.