Are there times when you don’t want to calculate the values on your spreadsheet and instead want the total count of items? Using the five COUNT functions in Microsoft Excel, you can count a number of items, total the blank cells, or see how many items meet your criteria. This tutorial looks at each function in detail.
How to Use the COUNT Function
The COUNT function in Excel is one of the most basic of the five functions. Use it to count the number of cells containing values, which is handy for seeing the number of sales, reviews, or numeric grades in your sheet. Note that text is not counted as a value.
The syntax for the formula is COUNT(value1, value2,…), where only the first argument is required. You can enter the range of cells and add up to 255 additional items or cell ranges using the remaining arguments.
For this first example, we’re using the COUNT function via the following formula to total the number of cells containing values in our range B2 through B11:
=COUNT(B2:B11)
To show the difference if you have text in a cell rather than a number, you can see our next result is 9 instead of 10, as cell B8 contains the word “six.”
If you want to count the total numbers in a cell range, but not place the formula in your sheet, use the Status Bar at the bottom of the Excel window.
Right-click the Status Bar, and select “Numerical Count” to place a checkmark next to it.
When you select your cell range, simply look at the Status Bar, and you’ll see “Numerical Count.” Click that item to place the result on your clipboard to paste where needed.
Tip: before processing your data with functions and formulas, be sure to clean up your spreadsheet data in Excel.
How to Use the COUNTA Function
Perhaps you have a spreadsheet where you’re expecting blank cells. This could be for data you’re expecting to enter later or import from another location. Count the number of cells that are not empty using the COUNTA function.
The syntax is similar to the COUNT function, COUNTA(value1, value2,…), where only the first argument is required, and you can include up to 255 additional arguments. Keep in mind that the function counts cells that contain errors, formulas, and formula results, as these cells are not blank.
In this example, we’re using the following formula to count the number of cells containing values in our range B2 through C11:
=COUNTA(B2:C11)
Our result is 16, as that’s the number of cells in our range that contain values.
Note: if you need to remove empty cells or want to surface data that meets specific criteria, try using filters in Excel.
How to Use the COUNTBLANK Function
On the opposite side of the COUNTA function is the COUNTBLANK function. You may have a large spreadsheet and want to see how much missing data you have by counting the empty cells.
The syntax is COUNTBLANK(range), where you have just one argument for the cell range.
Using the following formula, we’re counting the number of blank cells in the range B2 through C11:
=COUNTBLANK(B2:C11)
Our result is 4 empty cells.
Tip: want to reorganize your data so that columns become rows and vice versa? Transpose your data in Excel to make that happen.
How to Use the COUNTIF Function
For a more advanced function, use COUNTIF to total the number of cells that meet specific criteria.
The syntax is COUNTIF(value, criteria), where both arguments are required. Use the “value” argument for the cell range and the “criteria” argument for the data you want to locate. You’ll need to place the condition you use for the second argument within quotation marks if it’s text or a comparison operator.
In this first example, we’re counting the number of cells in the range B2 through B11 that contain the number 10 with this formula:
=COUNTIF(B2:B11,10)
The result is 3 cells that contain the value of 10.
For an example using text, we’re counting the number of cells in the range A2 through A11 that start with “San.” Use the formula:
=COUNTIF(A2:A11,"San*")
“San*” was added in quotation marks using an asterisk (*) as a wildcard so that any letters after “San” are counted. We received a result of 2.
For an example that uses a comparison operator, we’re counting the number of cells in the range C2 through C11 that are less than or equal to 5,000 with this formula:
=COUNTIF(C2:C11,"<=5000")
We placed our comparison operator “<=5000” within quotes and received a count of 7.
How to Use the COUNTIFS Function
If you like the idea of entering criteria for the cells you want to count but would like to narrow it down even further or total values in more cells, use the COUNTIFS function. You can count cells that contain multiple conditions instead of just one when using the COUNTIF function.
The syntax is COUNTIFS(range1, criteria1, range2, criteria2,…), where the first two arguments are required for the cell range and condition. Use the additional arguments for the second set of cells and/or conditions.
For this example, we’re counting the number of records containing a 10 in the range B2 through B11 and a value of 1,000 in the range C2 though C11 using this formula:
=COUNTIFS(B2:B11,10,C2:C11,1000)
Our result is 2 records that have both 10 and 1,000 in those cell ranges.
Using a text example, we’re counting the number of records that start with the letter “S” in the range A2 through A11 and have a value greater than 9 in the range B2 through B11 with this formula:
=COUNTIFS(A2:A11,"S*",B2:B11,">9")
Our result is 1 item that starts with an “S” in our first cell range and has a value greater than 9 in our second cell range.
In another example, we’re using the same cell range for multiple criteria. We’re counting the number of records that have a value less than 10,000 and greater than 2,000 in the range C2 through C11 with this formula:
=COUNTIF(C2:C11,"<10000",C2:C11,">2000")
Our result is 2 records that are 5,000.
Tip: if you also use Google’s apps, check out several helpful functions for Google Sheets, too.
Frequently Asked Questions
How do I auto count cells in Excel?
If you need to add numbers to cells in a column or row, use Excel’s auto-fill feature. To start counting at one, enter 1 in the first cell and 2 in the second cell. Then, select both cells and drag the fill handle (square in the bottom-right corner) down a column or across a row to fill the remaining cells.
To use a different numbering scheme, for example, 10, 20, 30, and so on, enter the first two numbers you want to use in the first two cells, then use the fill handle. Excel should recognize your numbering pattern and comply.
How do I sum values in Excel?
You can use the SUM function to add values in cells together. This is handy for totaling your monthly bills, sales, or inventory.
Select the cells with the values you want to add, go to the “Home” tab, and click the “Sum” button in the “Editing” section of the ribbon. Sum is the default function for this button, allowing you to quickly add cell values.
What’s the difference between the Formula Bar and Status Bar?
The Formula Bar in Excel is where you can enter formulas like those you see in this article and edit existing formulas. This displays at the top of your spreadsheet.
The Status Bar in Excel shows information about your sheet, errors you may encounter, and quick calculations, like those mentioned above. This displays at the bottom of the window in Excel, as well as other Microsoft applications, like Word and PowerPoint.
Image credit: Pixabay. All screenshots by Sandy Writtenhouse.
Our latest tutorials delivered straight to your inbox