Count how often a value occurs

A number of worksheet functions can help you count how many times a particular text or number value occurs in a range of cells. For example, you can use the COUNTIF function to count how often a single value occurs, as shown in the following example.

Example

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)

3

=COUNTIF(A2:A7,A4)

Number of entries for Suyama (2)

2

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

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

4

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

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

2

Use COUNTIFS to count values based on multiple criteria

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

Employee ID

Region Number

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

Description

Result

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

Number of employees in Region 2 and in the Finance department

2

The first criteria range includes all region numbers, and the second criteria range includes all department names. 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.

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!

×