Categories
Microsoft Excel

Use Excel’s COUNT Function Like a Boss!

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!

Excel Courses: We offer Microsoft Excel training delivered online by a live instructor. Participants can connect from anywhere in Canada.

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.

COUNT function example
COUNT Function Example

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.

COUNTA function example
COUNTA Example

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.

COUNTBLANK function example
COUNTBLANK Example

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.

COUNTIF function example
COUNTIF Function Example

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.

COUNTIF function with wildcard

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.

COUNTIFS function example
COUNTIFS Function Example

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.