Show All

Hide All

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.

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.

Notes

• The formulas in the 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.). 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.

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

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.

   

2. On the Data menu, click PivotTable and PivotChart Report.
3. Click Finish.
4. Drag the column label from PivotTable Field List to Drop Row Fields Here.
5. 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 , and then in the Summarize by box, click Count.

Top of Page