Use the COUNTIF function to do this task.
ExampleThe example may be easier to understand if you copy to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- 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.
|
|
Function details
Count the occurrence of more than one condition
Use the IF and SUM functions to do this task.
ExampleThe example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- 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.
|
|
Note The formulas in this example must be entered as array formulas. Select each cell that contains a formula, press F2, and then press CTRL+SHIFT+ENTER.
Function details
Count the occurrences of unique entries
- Select the column containing the data.
- On the Data menu, click PivotTable and PivotChart Report.
- Click Finish.
- Drag the column label from PivotTable Field List to Drop Row Fields Here.
- Drag the same column label from PivotTable Field List to Drop Data Items Here.
Note If your data contains numbers, the PivotTable report totals the entries instead of counting them. To change from the Sum summary function to the Count summary function, on the PivotTable toolbar, click Field Settings , and then in the Summarize by box, click Count.
Count the number of unique values
For example, if a column contains 1,2,2,2 the result is 2 unique values in the column.
- On the Data menu, point to Filter, and then click Advanced Filter.
- In the Advanced Filter dialog box, click Copy to another location.
- In the List range box, delete any information in the box or click the box, and then click the column for which you want to count the unique entries.
- If you have a column label, in the Criteria range box, delete any information in the box or click the box, and then click the column label for your column of data.
- In the Copy to box, delete any information in the box or click the box, and then click a blank column where you want to copy the unique values.
- Select the Unique records only check box, and click OK.
- Select the blank cell below the last cell in the range.
- Click the arrow next to AutoSum on the Standard toolbar, and then click Count.
- Press Enter.