How To Count Number Of Times A Word Appears In Excel

Count how often a value occurs

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 More...Less

Suppose you lot want to find out how many times particular text or a number value occurs in a range of cells. For case:

If a range, such as A2:D20, contains the number values 5, six, 7, and 6, then the number 6 occurs two times.
If a cavalcade contains "Buchanan", "Dodsworth", "Dodsworth", and "Dodsworth", so "Dodsworth" occurs three times.

There are several means to count how oft a value occurs.

Utilize the COUNTIF role to count how many times a particular value appears in a range of cells.

COUNTIF examples

For more information, encounter COUNTIF function.

The COUNTIFS function is similar to the COUNTIF function with one of import exception: COUNTIFS lets yous apply criteria to cells across multiple ranges and counts the number of times all criteria are met. You can use upwards to 127 range/criteria pairs with COUNTIFS.

The syntax for COUNTIFS is:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

Come across the post-obit example:

COUNTIFS example

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

Permit'due south say you need to determine how many salespeople sold a item 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 lot starting time use the IF part to exam a condition and then, but if the upshot of the IF office is Truthful, y'all employ the COUNT part to count cells.

Notes:

The formulas in this example must be entered as array formulas.
- If you have a electric current version of Microsoft 365, then you can simply enter the formula in the meridian-left-cell of the output range, so press ENTER to confirm the formula every bit a dynamic array formula.
- If you have opened this workbook in Excel for Windows or Excel 2016 for Mac and newer versions, and desire to change the formula or create a similar formula, press F2, and then press Ctrl+Shift+Enter to make the formula render the results y'all look. In before versions of Excel for Mac, use +Shift+Enter.
For the example formulas to work, the second argument for the IF function must exist a number.

Examples of nested COUNT and IF functions

To acquire more nearly these functions, come across COUNT role and IF function.

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

Notes:The formulas in this example must be entered as array formulas.

If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, and then press ENTER to confirm the formula as a dynamic assortment formula.
If you have opened this workbook in Excel for Windows or Excel 2016 for Mac and newer versions, and want to change the formula or create a similar formula, press F2, and and then press Ctrl+Shift+Enter to brand the formula return the results you wait. In earlier versions of Excel for Mac, utilise +Shift+Enter.

Example 1

Example 1: SUM and IF nested in a formula

The to a higher place office says if C2:C7 contains the values Buchanan and Dodsworth, then the SUM role should display the sum of records where the condition is met. The formula finds iii records for Buchanan and i for Dodsworth in the given range, and displays iv.

Case ii

Example 2: SUM and IF nested in a formula

The higher up office says if D2:D7 contains values bottom than $9000 or greater than $nineteen,000, and so SUM should display the sum of all those records where the condition is met. The formula finds two records D3 and D5 with values lesser than $9000, and and then D4 and D6 with values greater than $19,000, and displays 4.

Example iii

Example 3: SUM and IF nested in a formula

The higher up function says if D2:D7 has invoices for Buchanan for less than $9000, then SUM should display the sum of records where the condition is met. The formula finds that C6 meets the condition, and displays 1.

You tin use a PivotTable to brandish totals and count the occurrences of unique values. A PivotTable is an interactive way to quickly summarize large amounts of information. You can use a PivotTable to expand and plummet 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 tin can motion rows to columns or columns to rows ("pivoting") to run across a count of how many times a value occurs in a PivotTable. Let'southward await at a sample scenario of a Sales spreadsheet, where you can count how many sales values are in that location for Golf and Lawn tennis for specific quarters.

Enter the following information in an Excel spreadsheet.
Select A2:C8
Click Insert > PivotTable.
In the Create PivotTable dialog box, click Select a tabular array or range, then click New Worksheet, so click OK.

An empty PivotTable is created in a new canvas.
In the PivotTable Fields pane, do the following:
1. Drag Sport to the Rows expanse.
2. Drag Quarter to the Columns expanse.
3. Drag Sales to the Values surface area.
4. Repeat footstep c.
  
  The field name displays every bit SumofSales2 in both the PivotTable and the Values area.
  
  At this point, the PivotTable Fields pane looks like this:
5. In the Values area, click the dropdown next to SumofSales2 and select Value Field Settings.
6. In the Value Field Settings dialog box, exercise the following:
  1. In the Summarize value field by section, select Count.
  2. In the Custom Name field, modify the proper name to Count.
  3. Click OK.
The PivotTable displays the count of records for Golf and Tennis in Quarter 3 and Quarter 4, along with the sales figures.