Page 423 - Excel 2013 All-in-One For Dummies

To find out the number of occupied cells in the entire second worksheet of your workbook (assuming that it’s still called Sheet2), you enter this COUNTA formula:
=COUNTA(Sheet2!1:1048576)
Note that you can also enter the argument for this COUNTA function by designating the entire range of column letters (rather than the range of row numbers) as in:
=COUNTA(Sheet2!A:XFD)
However, Excel automatically converts the argument that specifies the range of columns to rows, using absolute references ($1:$1048576) as soon as you enter the COUNTA function in its cell.
When entering COUNTA functions that return the number of occupied cells in an entire row, column, or worksheet, you must be sure that you do not enter the formula in a cell within that row, column, or worksheet. If you do, Excel displays a Circular Reference Alert dialog box when you try to enter the formula in the worksheet. This happens because you are asking Excel to use the cell with the formula that does the counting in the count itself (defi- nitely the type of circular logic that the program doesn’t allow).
Conditional counting
Excel includes a COUNTIF function that you can use to count cells in a range only when they meet a certain condition. The COUNTIF function takes two arguments and uses the following syntax:
COUNTIF(range,criteria)
The range argument specifies the range of cells from which the conditional count is to be calculated. The criteria argument specifies the condition to use. You can express this argument as a number, expression, or text that indicates which cells to count. When specifying a number for the criteria argument, you don’t have to enclose the number in quotes. For example, in a cell range named table_data, to count the number of entries that contain the number 5, you enter the following COUNTIF formula:
=COUNTIF(table_data,5)
However, when specifying an expression or text as the criteria argument, you must enclose the expression or text in closed quotes as in “=5”, “>20”, or “New York”. So, if you want to use COUNTIF to find out how many cells in
Statistical Functions 405
Book III Chapter 5
Math and Statistical Formulas