Are there times when you don’t want to calculate the values in your spreadsheet and instead want the total count of the items? Using the five COUNT functions in Microsoft Excel, you can count multiple items, include the empty cells, or see how many items meet your criteria. This guide examines 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 that contain values, which is useful for seeing the number of sales, reviews, or numerical scores in your sheet. Note that text is not counted as an entry.
The syntax of the formula is COUNT(value1, value2,…)
, where only the first argument is required. You can enter the cell range and add up to 255 additional items or cell ranges using the remaining arguments.
For this first example, we use the COUNT function using the following formula to sum the number of cells that contain values in our range B2 through B11:
To show the difference if you have text in the cell and not a number, you can see that our next result is 9 instead of 10, since cell B8 contains the word “six”.
If you want to count the total numbers in a range of cells, but not place the formula in your sheet, use the status bar at the bottom of the Excel window.
Right-click on the status bar and select “Numeric Count” to place a check mark next to it.
When you select your range of cells, just look at the status bar, and you will see “Numeric Count”. Click on this item to place the result on your clipboard to paste where needed.
advice: Before processing your data with functions and formulas, be sure to clean up your Excel spreadsheet data.
How to use the COUNTA function
Maybe you have a spreadsheet where you expect empty cells. This could be for data you expect 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. Remember that the function counts cells that contain errors, formulas, and formula results, because these cells are not empty.
In this example, we use the following formula to count the number of cells that contain values in our range B2 through C11:
Our result is 16, because that is the number of cells in our range that contain values.
Note: If you need to remove empty cells or want to display 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 data you are missing by counting the empty cells.
The syntax is COUNTBLANK(range)
where you have only one argument for the range of cells.
Using the following formula, we count the number of empty cells in the range B2 to C11:
Our result is 4 empty cells.
advice: Want to rearrange your data so that columns become rows and vice versa? Transfer your data in Excel to make this happen.
How to use the COUNTIF function
For a more advanced function, use COUNTIF to sum the number of cells that meet specific criteria.
The syntax is COUNTIF(value, criteria)
, when both arguments are required. Use the “value” argument for the range of cells and the “criteria” argument for the data you want to locate. You will need to enclose the condition you use for the second argument in quotes if it is text or a comparison operator.
In this first example, we count the number of cells in the range B2 through B11 that contain the number 10 with this formula:
The result is 3 cells containing the value of 10.
For example using text, we count the number of cells in the range A2 to A11 that start with “San”. Use the formula:
“San*” is added in quotes using an asterisk
wildcard so that all letters after “san” are counted. We got a result of 2.
=COUNTIF(C2:C11,"<=5000")
The COUNTIF function uses Less Than or Equal To in Excel
We put our comparison operator “<=5000" inside quotes and got 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 further or include values in additional cells, use the COUNTIFS function. Cells containing multiple conditions can be counted in only one place when using the COUNTIF function. COUNTIFS(range1, criteria1, range2, criteria2,…)
The syntax is
, where the first two arguments are required for the cell range and the condition. Use the additional arguments for the second set of cells and/or conditions.
=COUNTIFS(B2:B11,10,C2:C11,1000)
COUNTIFS function using values in Excel Our result is 2 records that have 10 in them and
1,000 in these cell ranges.
=COUNTIFS(A2:A11,"S*",B2:B11,">9")
COUNTIFS function using text and Greater Than in Excel
Our result is one item that starts with “S” in our first cell range and has a value greater than 9 in our second cell range.
=COUNTIF(C2:C11,"<10000",C2:C11,">2000")
COUNTIFS function using Greater and Less Than in Excel
Our result is 2 records which is 5,000.advice
: If you also use Google apps, check out some helpful functions for Google Sheets as well.
Common questions
How can I automatically count cells in Excel?
If you need to add numbers to cells in a column or row, use Excel’s AutoFill 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 lower left 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 button. Excel should recognize your numbering pattern and meet the requirements.
How can I sum values in Excel?
You can use the SUM function to add values in cells together. This is useful for summarizing your monthly accounts, 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 “Edit” section of the ribbon. Sum is the default function of this button, which allows you to quickly add cell values.
What is the difference between the formula bar and the status bar?
The Formula Bar in Excel is where you can enter formulas like the ones you see in this article and edit existing formulas. This is displayed at the top of your spreadsheet.
The status bar in Excel shows information about your sheet, errors you might encounter, and quick calculations, like those mentioned above. It is displayed at the bottom of the window in Excel, as well as in other Microsoft applications, such as Word and PowerPoint. Image credit:Pixabay
With her BA in Information Technology, Sandy has worked for many years in the IT industry as a Project Manager, Department Manager and PMO Manager. She wanted to help others learn how technology can enrich business and personal life and shared her suggestions and guidelines in thousands of articles.
Sign up for our newsletter!
Our latest tutorials are delivered straight to your inbox