Count how often a value occurs

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

Suppose you want to find out how many times particular text or a number value occurs in a range of cells. For example:

  • If a range contains the number values 5, 6, 7, and 6, the number 6 occurs two times.
  • If a column contains "Buchanan", "Dodsworth", "Dodsworth", and "Dodsworth", "Dodsworth" occurs three times.

There are several ways to count how often a value occurs.

What do you want to do?


Count how often a single value occurs by using a function

Use the COUNTIF function to perform this task.

Example

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

ShowHow to copy an example

  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 Formulas tab, in the Formula Auditing group, click the Show Formulas button.
 
1
2
3
4
5
6
7
A B
Salesperson Invoice

Buchanan

15,000

Buchanan

9,000

Suyama

8,000

Suyama

20,000

Buchanan

5,000

Dodsworth

22,500
Formula Description (Result)
=COUNTIF(A2:A7,"Buchanan") Number of entries for Buchanan (3)
=COUNTIF(A2:A7,A4) Number of entries for Suyama (2)
=COUNTIF(B2:B7,"< 20000") Number of invoice values less than 20,000 (4)
=COUNTIF(B2:B7,">="&B5) Number of invoice values greater than or equal to 20,000 (2)

Function details

COUNTIF    This function counts the number of cells within a range that meet a single criterion that you specify.

Top of Page Top of Page

Count based on multiple criteria by using the COUNTIFS function

Introduced in Excel 2007, the COUNTIFS function is similar to the COUNTIF function with one important exception: COUNTIFS lets you apply criteria to cells across multiple ranges and counts the number of times all criteria are met. You can use up to 127 range/criteria pairs with the COUNTIFS function. The syntax for the function looks like this:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

To learn more about using this function to count with multiple ranges and criteria, see COUNTIFS function.

Top of Page Top of Page

Count based on criteria by using the COUNT and IF functions together

Let's say you need to determine how many salespeople sold a particular item in a certain region, or you want to know how many sales over a certain value were made by a particular salesperson. You can use the IF and COUNT functions together; that is, you first use the IF function to test a condition and then, only if the result of the IF function is True, you use the COUNT function to count cells.

Example

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

ShowHow to copy an example

  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 Formulas tab, in the Formula Auditing group, click the Show Formulas button.
 
1
2
3
4
5
6
7
8
9
10
11
A B C D
Region Salesperson Type Sales
South

Buchanan

Beverages 3571
West

Davolio

Dairy 3338
East

Suyama

Beverages 5122
North

Suyama

Dairy 6239
South

Dodsworth

Produce 8677
South

Davolio

Meat 450
South

Davolio

Meat 7673
East

Suyama

Produce 664
North

Davolio

Produce 1500
South

Dodsworth

Meat 6596
Formula Description (result)
=COUNT(IF((A2:A11="South")*(C2:C11="Meat"),D2:D11)) Number of sales of meat in the South region (3)
=COUNT(IF((B2:B11="Suyama")*(D2:D11>=1000),D2:D11)) Number of sales greater than 1000 by Suyama (2)


 Notes 

Function details

COUNT    This function counts the number of cells that contain numbers, and counts numbers within the list of arguments.

IF    This function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE.

Top of Page Top of Page

Count how often multiple text or number values occur by using functions

Use the IF and SUM functions to do this task:

  • Assign a value of 1 to each true condition by using the IF function.
  • Add the total, by using the SUM function.

Example

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

ShowHow to copy an example

  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 Formulas tab, in the Formula Auditing group, click the Show Formulas button.
 
1
2
3
4
5
6
7
A B
Salesperson Invoice
Buchanan 15,000
Buchanan 9,000
Suyama 8,000
Suyama 20,000
Buchanan 5,000
Dodsworth 22,500
Formula Description (Result)
=SUM(IF((A2:A7="Buchanan")+(A2:A7="Dodsworth"),1,0)) Number of invoices for Buchanan or Dodsworth (4)
=SUM(IF((B2:B7<9000)+(B2:B7>19000),1,0)) Number of invoices with values less than 9000 or greater than 19000 (4)
=SUM(IF(A2:A7="Buchanan",IF(B2:B7<9000,1,0))) Number of invoices for Buchanan with a value less than 9,000. (1)

 Note   The formulas in this example must be entered as array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). Select each cell that contains a formula, press F2, and then press CTRL+SHIFT+ENTER.

Top of Page Top of Page

Count how often multiple values occur by using a PivotTable report

You can use a PivotTable report to display totals and to count the occurrences of unique values. A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to expand and collapse levels of data to focus your results, and drill down to details from the summary data for areas that are of interest to you. In addition, you can move rows to columns or columns to rows ("pivoting") to see different summaries of the source data.

Example of source data and resulting PivotTable report    

Callout 1 Source data, in this case, from a worksheet
Callout 2 The source values for Qtr3 Golf summary in the PivotTable report
Callout 3 The entire PivotTable report
Callout 4 The summary of the source values in C2 and C8 from the source data

Use the following procedure to create a PivotTable report.

  1. Select the column that contains the data. Make sure that the column has a column heading.
  2. On the Insert tab, in the Tables group, click PivotTable.
  3. The Create PivotTable dialog box is displayed.
  4. Click Select a table or range.
  5. Place the PivotTable report in a new worksheet starting at cell A1 by clicking New Worksheet.
  6. Click OK.
    An empty PivotTable report is added to the location that you specified with the PivotTable field list displayed.
  7. In the field section at the top of the PivotTable field list, click and hold the field name, and then drag the field to the Row Labels box in the layout section at the bottom of the PivotTable field list.
  8. In the field section at the top of the PivotTable field list, click and hold the same field name, and then drag the field again to the Values box in the layout section at the bottom of the PivotTable Field List.

 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, select a cell in that column, and then on the Options tab in the Active Field group, click Field Settings, click the Summarize by tab, click Count, and then click OK.

Top of Page Top of Page

 
 
Applies to:
Excel 2010, Excel 2007, Excel 2003