Count in Excel

Spreadsheets are often used to analyze data. Sometimes, you may have hundreds or even thousands of rows and columns. Fortunately, Excel has several features that help you count cells and values. Let’s learn how to count in Microsoft Excel.

How to Count in Excel

If you’re only counting a handful of cells, it may seem easy enough to do manually. But imagine long and complex spreadsheets. Counting manually would be difficult, take too long, and likely lead to errors. Fortunately, you have better ways to count, because Excel gives you multiple options for counting data.

Consider a set of data containing multiple rows and columns. To analyze this data, you may want to perform a series of counts. These counts can include both totals (how many cells contain data) or counts of specific numbers (for example, the number of cells containing the value 55).

Your simplest technique for counting totals is to use the click-and-drag method. Consider a given range of cells, like B1:C6. This is a twelve-cell range, with six cells in each column.

To count the number of cells, click first into cell B1 to select it. Then, drag your cursor down and to the right, selecting the cells. Now, look at the bottom of your Excel application, just below the worksheet names. Notice that Excel displays a count: 12. 

Keep in mind that this technique specifically counts cells that contain values inside. If you try the same process with blank or empty cells, you won’t see a count.

Also notice that Excel has automatically computed the average and sum of the values. This is a handy feature that saves time – you don’t have to write or build any formulas or functions to perform a basic total count.

For more precision, of course, formulas are useful, and virtually required in extremely large datasets. Imagine a spreadsheet with 5,800 rows and 300 columns. Clicking and dragging would take too long. This is where formulas come in.

Counting formulas in Excel utilize the built-in COUNT function. COUNT is very simple, and gives you more control over your data.

Let’s use the COUNT function in formulas within the same dataset we just demonstrated. First, let’s run the function to count the total number of cells that contain values.

Type into an empty cell on your worksheet. Then, type an = sign. The = sign is standard practice in Excel. It simply tells Excel that you’re typing a formula into the selected cell.

Then, type COUNT, followed by an open ( sign. Your COUNT function is placed inside the formula, which now reads:

=COUNT(

Then, click and drag to select the range of cells that you want to count within. Once again, that range is B1:C6. Complete the formula by typing ). The finished formula with the COUNT function is:

=COUNT(B1:C6)

Go ahead and hit Enter on your keyboard, and Excel will count the number of cells in the given range that contain values: 12, once again.

Now, let’s say that you want to be more specific. Within the same range, you want a count of the number of cells that contain the number 55. To do this, click into an empty cell. Here, you’ll need to use the modified COUNTIF function:

=COUNTIF(B1:C6

From there, you’ll need to make an adjustment to your formula. Type a comma, and then type in the number that you want Excel to search for and count. Here, that’s 55. Then, close the parentheses by typing ).

Your modified, specific count formula is:

=COUNTIF(B1:C6,55)

Hit Enter once again, and Excel counts only the cells in the range containing the number 55. In this example, the result is 3.

As you can see, Excel offers multiple great ways to count cells and values.