Category: Excel Training

Posted on

Using Counta in Excel 2010

The COUNTA function counts the number of cells that are not empty in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.).

Syntax

COUNTA(value1, [value2], ...)

The COUNTA function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

  • value1    Required. The first argument representing the values that you want to count.
  • value2, …    Optional. Additional arguments representing the values that you want to count, up to a maximum of 255 arguments.

Remarks

  • The COUNTA function counts cells containing any type of information, including error values and empty text (“”). For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty cells.
  • If you do not need to count logical values, text, or error values (in other words, if you want to count only cells that contain numbers), use the COUNT function.
  • If you want to count only cells that meet certain criteria, use the COUNTIF function or the COUNTIFS function.

Example

This help topic links to live data in an embedded workbook. Change data, or modify or create formulas in the worksheet and they will immediately be calculated by Excel Web App – a version of Excel that runs on the web.

This example uses the COUNTA function to count the number of nonblank cells in a range.

Examples of the COUNTA function

Posted on

Using the Countif Function in Excel 2010

Countif Counts the number of cells within a range that meet the given criteria.

Syntax

COUNTIF(range,criteria)

Range    is the range of cells from which you want to count cells.

Criteria    is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.

Remarks

  • You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
  • Microsoft Excel provides additional functions that can be used to analyze your data based on a condition.
    • To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function.
    • To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.
    • To count cells that are empty or not empty, use the COUNTA and COUNTBLANK functions.

Example 1: Common COUNTIF formulas

The example may be easier to understand if you copy it to a blank worksheet.

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
4
5
A B
Data Data
apples 32
oranges 54
peaches 75
apples 86
Formula Description (result)
=COUNTIF(A2:A5,”apples”) Number of cells with apples in the first column above (2)
=COUNTIF(A2:A5,A4) Number of cells with peaches in the first column above (1)
=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2) Number of cells with oranges or apples in the first column above (3)
=COUNTIF(B2:B5,”>55″) Number of cells with a value greater than 55 in the second column above (2)
=COUNTIF(B2:B5,”<>”&B4) Number of cells with a value not equal to 75 in the second column above (2)
=COUNTIF(B2:B5,”>=32″)-COUNTIF(B2:B5,”>85″) Number of cells with a value greater than or equal to 32 and less than or equal to 85 in the second column above (3)

Example 2: COUNTIF formulas using wildcard characters and handling blank values

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.

  1. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
4
5
6
7
A B
Data Data
apples Yes
   
oranges NO
peaches No
   
apples YeS
Formula Description (result)
=COUNTIF(A2:A7,”*es”) Number of cells ending with the letters “es” in the first column above (4)
=COUNTIF(A2:A7,”?????es”) Number of cells ending with the letters “les” and having exactly 7 letters in the first column above (2)
=COUNTIF(A2:A7,”*”) Number of cells containing text in the first column above (4)
=COUNTIF(A2:A7,”<>”&”*”) Number of cells not containing text in the first column above (2)
=COUNTIF(B2:B7,”No”) / ROWS(B2:B7) The average number of No votes including blank cells in the second column above formatted as a percentage with no decimal places (33%)
=COUNTIF(B2:B7,”Yes”) / (ROWS(B2:B7) -COUNTIF(B2:B7, “<>”&”*”)) The average number of Yes votes excluding blank cells in the second column above formatted as a percentage with no decimal places (50%)

 Note    To view the number as a percentage, select the cell and click Cells on the Format menu. Click the Number tab, and then click Percentage in the Category box.