Count how often a value occurs

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, such as A2:D20, 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

Count based on multiple criteria by using the COUNTIFS function

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

Count how often multiple text or number values occur by using the SUM and IF functions together

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

Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. Adjust the column widths as needed to see all the data. The apostrophe's on the left side of the formulas in the Formula section allow the formulas to display for reference, while the formulas on the right under the Result column will display the formula results.

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,"Buchanan")

'=COUNTIF(A2:A7,A4)

Number of entries for Suyama (2)

=COUNTIF(A2:A7,A4)

'=COUNTIF(B2:B7,"< 20000")

Number of invoice values less than 20,000 (4)

=COUNTIF(B2:B7,"< 20000")

'=COUNTIF(B2:B7,">="&B5)

Number of invoice values greater than or equal to 20,000 (2)

=COUNTIF(B2:B7,">="&B5)

To learn more about using this function, see COUNTIF function.

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],…)

Example

Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. Adjust the column widths as needed to see all the data. The apostrophe's on the left side of the formulas in the Formula section allow the formulas to display for reference, while the formulas on the right under the Result column will display the formula results.

Employee ID

Region No.

Department

20552

2

Sales

21268

2

Finance

23949

1

Admin

24522

4

Admin

28010

3

IT

29546

4

Sales

31634

3

IT

32131

1

IT

35106

4

Finance

40499

1

HR

42051

1

Sales

43068

2

HR

45382

2

Finance

47971

1

IT

Formula

Result

How many employees are in Region 2 and in the Finance department?

'=COUNTIFS(B2:B15,"2",C2:C15,"Finance")

=COUNTIFS(B2:B15,"2",C2:C15,"Finance")

The first criteria range is the region numbers, and the second criteria range is the department name. The criteria applied to the first criteria range is "2" and the criteria applied to the second range is "Finance." COUNTIFS checks to see if both criteria are met.

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

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.

Examples

Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. Adjust the column widths as needed to see all the data. The apostrophe's on the left side of the formulas in the Formula section allow the formulas to display for reference, while the formulas on the right under the Result column will display the formula results.

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((A2:A11="South")*(C2:C11="Meat"),D2:D11))

'=COUNT(IF((B2:B11="Suyama")*(D2:D11>=1000),D2:D11))

Number of sales greater than $1,000 by Suyama. (2)

=COUNT(IF((B2:B11="Suyama")*(D2:D11>=1000),D2:D11))

Notes: 

  • The formulas in this example must be entered as array formulas. If you have opened this workbook in Excel for Windows or Excel 2016 for Mac and want to change the formula or create a similar formula, press F2, and then press Ctrl+Shift+Enter to make the formula return the results you expect. In earlier versions of Excel for Mac use COMMAND +Shift+Enter.

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

The COUNT function counts the number of cells that contain numbers and counts numbers within its list of arguments. The IF function returns one value if a condition you specify evaluates to True and another value if that condition evaluates to False.

To learn more about these functions, see COUNT function and IF function.

Top of Page

Count how often multiple text or number values occur by using the SUM and IF functions together

In the examples that follow, we use the IF and SUM functions together. The IF function first tests the values in some cells and then, if the result of the test is True, SUM totals those values that pass the test.

Examples

Salesperson

Invoice

Buchanan

15000

Buchanan

9000

Suyama

8000

Suyama

20000

Buchanan

5000

Dodsworth

22500

Formula

Description

Result

'=SUM(IF((A2:A7="Buchanan")+(A2:A7="Dodsworth"),1,0))

Number of invoices for Buchanan or Dodsworth.

=SUM(IF((A2:A7="Buchanan")+(A2:A7="Dodsworth"),1,0))

'=SUM(IF((B2:B7<9000)+(B2:B7>19000),1,0))

Number of invoices with values less than $9,000 or greater than $19,000.

=SUM(IF((B2:B7<9000)+(B2:B7>19000),1,0))

'=SUM(IF(A2:A7="Buchanan",IF(B2:B7<9000,1,0)))

Number of invoices for Buchanan with a value less than $9,000.

=SUM(IF(A2:A7="Buchanan",IF(B2:B7<9000,1,0)))

Note: The formulas in this example must be entered as array formulas. If you have opened this workbook in Excel for Windows or Excel 2016 for Mac and want to change the formula or create a similar formula, press F2, and then press Ctrl+Shift+Enter to make the formula return the results you expect. In earlier versions of Excel for Mac use COMMAND +Shift+Enter.

Top of Page

Count how often multiple values occur by using a PivotTable report

You can use a PivotTable report to display totals and 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 to 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 souce data and resulting pivottable report

1   Source data, in this case, from a worksheet

2   The source values for Qtr3 Golf summary in the PivotTable report

3   The entire PivotTable report

4   The summary of the source values in C2 and C8 from the source data

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.
    The Create PivotTable dialog box is displayed.

  3. Click Select a table or range.

  4. Place the PivotTable report in a new worksheet starting at cell A1 by clicking New Worksheet.

  5. Click OK.
    An empty PivotTable report is added to the location that you specified with the PivotTable field list displayed.

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

  7. 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 Analyze tab in the Active Field group, click Field Settings, click the Summarize by tab, click Count, and then click OK.

Top of Page

See Also

Overview of formulas in Excel

How to avoid broken formulas

Find and correct errors in formulas

Excel keyboard shortcuts and function keys

Statistical functions (reference)

Math and trigonometry functions (reference)

Excel functions (alphabetical)

Excel functions (by category)

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×