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
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.
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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for