Count how often a value occurs

Let's say you want to find out how many times particular text or a number value occurs. For example:

  • If a range contains the number values 5, 6, 7, and 6, then the number 6 occurs two times.
  • If a column contains "Buchanan", "Dodsworth", "Dodsworth", and "Dodsworth", then "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

Count how often multiple number values occur by using functions

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

Count how often multiple values occur by using a PivotTable report

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 Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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

Top of Page Top of Page

Count how often multiple number values occur by using functions

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.

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 Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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 salespeople who sold 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

IF

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 Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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.

Function details

IF

SUM

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.

  1. Select the column containing the data.

Select column containing list

  1. On the Data menu, click PivotTable and PivotChart Report.
  2. Click Finish.
  3. Drag the column label from PivotTable Field List to Drop Row Fields Here.
  4. 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 (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), click Field Settings Button image, and then in the Summarize by box, click Count.

Top of Page Top of Page

 
 
Applies to:
Excel 2003