Various Count Functions in Excel

Written by tony allevato
  • Share
  • Tweet
  • Share
  • Pin
  • Email
Various Count Functions in Excel
Choose from a number of functions to count data occurrences. (Calculating payments image by Christopher Meder from Fotolia.com)

Microsoft Excel supports a wide variety of functions that let you count the number of occurrences of different kinds of data in your spreadsheets. These abilities range from the basic (count the number of cells that are not blank) to the more complex (count the number of cells that simultaneously match multiple conditions). Knowing which function best fits a particular situation will help you get the most out of your Excel experience.

Other People Are Reading

Numerical cells

The COUNT function computes the number of cells in a range that contain numbers or dates. The formula excludes blank cells and cells that contain non-numerical values, such as text, from the count.

This function takes one or more ranges as arguments. For example, COUNT(A1:A10, B1:B10) will return the number of numerical cells in columns A and B of the first ten rows.

Any Non-Blank Cells

The COUNTA function computes the number of cells in a range that contain any data, not just those that are numerical. Cells that are flagged as containing errors will also be included, but blank cells will not.

Like the COUNT function, COUNTA takes one or more ranges as arguments. For example, COUNTA(A1:A10, B1:B10) will return the number of non-blank cells in columns A and B of the first ten rows.

Blank Cells

The COUNTBLANK function returns the number of cells in a range that are either blank or contain a formula that returns empty text (represented by empty quotes, "") when evaluated. The cell must truly be blank; COUNTBLANK will not include cells containing zeroes.

Like COUNT and COUNTA, COUNTBLANK takes one or more ranges as arguments.

Cells that Satisfy a Condition

If you want to count only those cells that satisfy a certain condition, use the COUNTIF function. This function takes a single range as its first argument and a condition as its second, and returns the number of cells in that range that match the condition.

You can write the condition passed to COUNTIF in a few different ways. The first way is to write a value that you want to match. For example, COUNTIF(A1:A10, "Chicago") counts the number of cells in rows 1 through 10 and column A that contain only the text "Chicago." To count the number of cells in a range that equal another value in the spreadsheet, reference another cell in the table. COUNTIF(A1:A10, B20) counts the number of cells in column A of the first ten rows that are equal to the value in column B, row 20. COUNTIF can also handle comparison expressions. Write a comparison expression in quotes: COUNTIF(A1:A10, ">=10") counts the number of cells in column A of the first ten rows that are greater than or equal to 10.

Cells that Satisfy Multiple Conditions

Since the release of Excel 2007 for Windows and Excel 2008 for Mac OS X, Excel includes the COUNTIFS function, which is a version of COUNTIF that takes multiple ranges and conditions and returns the number of times that cells in the ranges satisfy all of the conditions.

All of the ranges passed to COUNTIFS must have the same number of cells, and you can pass the same range multiple times if you wish. The conditions use the same format as COUNTIF: a value, a cell reference, or a comparison.

This function checks each cell in each range with the condition that follows it, and adds one to the count if all of the conditions hold. For example, COUNTIFS(A1:A10, "Chicago", B1:B10, ">=10") checks that A1 is "Chicago" and B1 is at least 10, then checks that A2 is "Chicago" and B2 is at least 10, and so on.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.