Microsoft Excel provides the COUNT function and several others to help you enumerate various types of data. Whether you want to count numbers, text, or blank cells, the world’s most popular spreadsheet application has you covered!
In this article, we’re going to look at the following functions:
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
Let’s get started!
The COUNT Function
The COUNT function in Excel enumerates (counts) the number of cells in a range that contain numbers. Blank cells and text are ignored.
Syntax
=COUNT(value1, [value2], …)
value1 is a required argument identifying the first value, cell address or range to be counted.
value2 [optional] identifies an additional value, cell address or range to be counted.
The COUNT function can contain up to 255 arguments, separated by commas.
Example
In the following example, when I click in cell B12 and type =count(B4:B10) then press Enter, the result is 4. Cells containing numbers are counted, blank cells are not.
The COUNTA Function
If you need to count the number of cells in a range that contain numbers and text, use the COUNTA function.
The COUNTA function enumerates cells in a range that are not empty. Numbers, text, logical values, and error messages are all included.
Syntax
=COUNTA(value1, [value2], …)
value1 is a required argument identifying the first value, cell address or range to be counted.
value2 [optional] identifies an additional value, cell address or range to be counted.
The COUNTA function can contain up to 255 arguments, separated by commas.
Example
The spreadsheet in the screenshot below contains more data.
Typing =COUNT(B4:B10) in cell B12 would still return a count of 4 because there are only 4 numbers in that range.
To include the error message in cell B9 and the text in B10, use COUNTA. Typing =COUNTA(B4:B10) in cell B12 would return 6 because COUNTA includes all cells that are not empty.
Caution
If one of the cells in the example above had contained a space, the cell would not be considered empty. Think about that… If I clicked in cell B6, tapped the space bar and hit Enter, the COUNTA function would return a 7. If you’re not getting the results you expect with COUNTA, make sure all of the cells that look empty actually are empty!
The COUNTBLANK Function
If you need to count the empty cells in a range, you’ll want to use COUNTBLANK.
Syntax
=COUNTBLANK(range)
range is a required argument identifying a range with empty cells that need to be counted.
COUNTBLANK only accepts a single argument.
Example
In the example below, typing =COUNTBLANK(B4:B10) would return a count of 1 because there is one blank cell in the range.
Note
Formulas that return an empty text string (“”) are counted as blank, cells with a zero value are not.
The COUNTIF Function
Excel’s COUNTIF function only counts cells in a range that meet the criteria you specify. Criteria – or the conditions you specify – can include logical operators (<, >, <>, =) or wildcards (*, ?). Text used as criteria is not case-sensitive.
Syntax
=COUNTIF(range, criteria)
range identifies the cells to be counted.
criteria specifies the condition cells need to meet in order to be counted.
Examples
In the spreadsheet shown below, typing =COUNTIF(B4:B11, “Pencils”) would return a count of 3 because the word “pencils” appears 3 times in the range. When you use text, or logical operators with numbers, as criteria they must be enclosed in quotes.
The COUNTIF function is very flexible. Let’s look at a few more examples.
- =COUNTIF(B4:B11, A13) // Counts cells that match the text or number entered in cell A13
- =COUNTIF(B4:B11, “pen*”) // Counts cells that begin with the letters pen
- =COUNTIF(C4:C11, “>25”) // Counts cells where Order Qty exceeds 25
How Do I Count Cells With Text in Excel?
If you only want to count the cells in a range that contain text (and exclude empty cells, numbers, logical values, and errors), use the COUNTIF function with a wildcard.
The asterisk (“*”) character is a wildcard that represents any number of characters. Numbers and other values are automatically excluded.
In the example below, only two cells in the highlighted range contain text.
The COUNTIFS Function
Excel’s COUNTIFS function counts cells in one or more ranges that meet the criteria you specify. Criteria – the conditions you specify – can include logical operators (<, >, <>, =) or wildcards (*, ?).
Syntax
=COUNTIF(criteria_range1, criteria1, [criteria_range2], [criteria2], …)
criteria_range1 identifies the cells to be counted.
criteria1 specifies the condition cells need to meet in order to be counted.
criteria_range2 [optional] identifies a second range of cells to be counted.
criteria2 [optional] specifies the condition cells in criteria_range2 need to meet in order to be counted.
Like the COUNTIF function, text used when specifying criteria needs to be enclosed in quotes, numbers and cell references do not – unless numeric values use a logical operator for comparison. Text used as criteria is not case-sensitive.
Examples
In the spreadsheet shown below, typing =COUNTIFS(B4:B11, “Pens”, C4:C11, “>=50”) would return a count of 2. Pens were ordered 3 times in column B but order quantity was only greater than or equal to 50 twice.
What’s Next?
Learning about functions is a terrific way to become more productive in Excel.
If you’ve enjoyed reading about the COUNT function, you should check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. Both of these functions are incredibly useful for anyone who needs to combine data from multiple sources.
See you next time!
