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

Get support