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

Function details

Count how often multiple number values occur by using functions

Function details

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

Function details

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.

How 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

  3. Press CTRL+C.

  4. In the worksheet, select cell A1, and press CTRL+V.

  5. 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

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.

How 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

  3. Press CTRL+C.

  4. In the worksheet, select cell A1, and press CTRL+V.

  5. 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 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)

Note   

  • The formulas in the example must be entered as array formulas. After copying the example to a blank worksheet, select the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

  • For these formulas to work, the second argument to the IF function must be a number.

Function details

COUNT

IF

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.

How 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

  3. Press CTRL+C.

  4. In the worksheet, select cell A1, and press CTRL+V.

  5. 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. Select each cell that contains a formula, press F2, and then press CTRL+SHIFT+ENTER.

Function details

IF

SUM

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 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

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language