# How to Count Specific Items in Excel List (2023)

See how to count specific items in an Excel list with formula, for text, numbers, dates. Short step-by-step video, free Excel workbook, written steps, screenshots • Video: Count Specific Items in List
• COUNTIF Warnings
• Match Specific Text Exactly
• Match Text Anywere in Cell
• Use Cell References
• Video Transcript - Specific Items
• Get the COUNT Sample File

## Video: Count Specific Items in List

This video tutorial shows how to use the COUNTIF function to count the number of cells that contain a specific string of text, such as "Pen". You can also find text that is part of a cell -- how many orders were placed for any kind of pen, such as "Gel Pen", "Pen" or even a "Pencil"?

There are written steps below the video, and you can read the video transcript at the bottom of this page.

## Excel COUNTIF Function

In Microsoft Excel, you can use the COUNTIF function to count cells that meet one criterion.

Note: To count cells based on multiple criteria, use the Excel COUNTIFS function.

### COUNTIF Arguments

With the COUNTIF function syntax, there are 2 required arguments:

(Video) How to Count the Occurrences of a Number or Text in a Range in Excel : Using Excel

1. range - cells to check for criteria
2. criteria - criteria to match - typed in the formula, or refer to a cell ## COUNTIF Warnings

Here are a couple of warnings about the COUNTIF function limitations, before you use this function in your Excel spreadsheet.

• Numbers In Numbers: The COUNTIF function can't count numbers within numbers.
• For example, it cannot count the number 123, if the cell value is the number 91236, even though that real number contains the string 123
• Instead, use one of the other formulas that will count numbers within numbers.
• Text Numbers: If the range of cells being counted have a mixture of text and numeric values, you might get incorrect counts.
• COUNTIF matches text numbers, like "00123", with real numbers, like 123.
• This could result in the false count of a duplicate value
• See another formula using the SUMPRODUCT function, that will avoid this problem.

## Match Specific Text Exactly

In Excel, sometimes you need to count specific items in list. In this example, there is a list of items that were ordered, and we want to count number of occurrences for the Pen orders only.

Follow these steps to create a COUNTIF formula to count the pen orders:

1. Select a cell for your formula - cell D4 in this example)
2. Type an equal sign (=) to start the formula
3. Type: COUNTIF(
4. Select the range of cells where the items are listed - cells A2:A10 in this example
• NOTE: If your list is in a named Excel table, the formula will show the table name and column name -- =COUNTIF(tblExact[Item]
• Type a comma, to separate the arguments
• Type the value that you want to match. In this example, the value is text, it's typed inside double quote marks: "Pen"
• Note: You can use upper or lower case for the criteria-- COUNTIF is not case sensitive
• Type a closing bracket, and press Enter
• ### Completed COUNTIF Formula

For a named table, the completed formula shows the table name (tblExact) and column name (Item):

• =COUNTIF(tblExact[Item], "Pen")

and for a worksheet list, the formula shows the cell reference:

• =COUNTIF(A2:A10, "Pen")

In this screenshot, you can see the result of the above formula in cell D4. There are 4 orders with an exact match for the "pen" criterion. ## Match Text Anywere in Cell

Instead of counting exact matches in an entire cell, you might want to count cells where specific text is anywhere in the cell - matching the entire cell value, or any part of the cell value.

• Note: COUNTIF cannot find numbers within real numbers

As in the previous example, we'll count the "Pen" orders. This time, the formula will have asterisk (*) wildcard characters before and after the text string. This wildcard represents any number of unknown characters, or no characters.

As a result, all the Pen, Gel Pen, and Pencil orders will be counted, because they contain the string "pen".

1. Select a cell for your formula - cell D4 in this example)
2. Type an equal sign (=) to start the formula
3. Type: COUNTIF(
4. Select the cells where the items are listed - cells A2:A10 in this example
• NOTE: If your list is in a named Excel table, the formula will show the table name and column name -- =COUNTIF(tblExact[Item]
• Type a comma, to separate the arguments
• Inside double quotes, type the value that you want to match, with asterisk (*) wildcard character before and after the text: "*Pen*"
• Note: You can use upper case or lower case -- COUNTIF is not case sensitive
• Type a closing bracket, and press Enter
• ### Completed Formula With Wildcards

For a named table, the completed formula shows the table and column names:

(Video) Excel: How to Count the Number of Items in a List

• =COUNTIF(tblExact[Item], "*Pen*")

and for a worksheet list, the formula shows the cell reference:

• =COUNTIF(A2:A10, "*Pen*") ## Use Cell References

To make your formulas more flexible, and easier to maintain, you can type the criteria text that you want to count in an Excel worksheet cell.

Then, in the formula, refer to the cell where you typed the criteria text.

For example, here is the formula to match a specific item, with a reference to cell E7, where the criteria text, "pen", was typed.

• =COUNTIF(tblExact[Item], E7) You can use a cell reference with wildcard characters too. Use the & (ampersand) operator to join the wildcard characters to the cell reference.

• =COUNTIF(tblWild[Item], "*" & E7 & "*") ## Video Transcript - Count Specific Items

Here is the transcript of the video above - Count Specific Items in Excel List.

__________

VIDEO TRANSCRIPT

In Excel, we have a list of sales orders and we would like to count how many orders there was a pen in the order. my list is from A1 to A10, and I'd like to count pen in that list.

(Video) Excel Magic Trick #13: SUM or COUNT only certain items! SUMIF COUNTIF functions

to do that, I can use the COUNTIF function. in this cell I start with an equal sign and type COUNTIF, and an open bracket.

#### First Argument

The first argument is which range I would like it to check.

I'm going to select A1 to A10.

I'll type a comma, to end that argument.

#### Second Argument

The next argument is the criteria. What exactly do I want it to count?

I'm looking for a text string, so in double quotes, I type a double quote and then pen and a closing quote and a closing bracket.

When I press Enter, it found 4 pen items in that list.

So 1, 2, 3, 4, it found all 4 of our pen items.

#### Item Name Contains "Pen"

If we have on another worksheet, the same list. Here I've got pen, pencil, gel pen.

Maybe I'd like to find anything that has pen as part of the item name.

So not exactly pen, I don't want to count just the pens, but I'd like to include the gel pen and even a pencil.

#### Start COUNTIF Formula

Here, again, I'll use COUNTIF.

(Video) How to Count the Number of Unique Values in a List in Excel : Using Excel

• I'll start with an equal sign, COUNTIF, and an open bracket, and then the range is A1 to A10, type a comma.
• For this, I want it to include anything that has pen in it.
• I'll type my double quote mark and then an asterisk.
• On my keyboard that was Shift 8.
• That's a wild card character that represents any number of characters or no characters.
• Then the text I'm looking for. I can use upper or lowercase there
• Another asterisk, another double quote and a closing bracket.

It's going to look for anything that contains the letters, P-E-N in a string.

• There can be anything before that or nothing.
• And there can be anything after those three letters or nothing.

#### Formula Result

And when I press Enter we get 6. So one, two, three, four, five, six.

It found anything that contained that string of characters, P-E-N and the case didn't matter.

## Get the Sample File

To see all the COUNTIF examples, download the Count Specific Items sample workbook now. The zipped file is in xlsx file format, and does not contain any macros.

## More Function Tutorials

Compare 2 Lists

COUNT / COUNTIF

Count Criteria in Other Column

Count Cells With Specific Text

Count Specific Items in Cell

AVERAGE

SUM / SUMIF

Subtotal Feature

(Video) How to Count Specific Items in a List with Excel COUNTIF

## FAQs

### How do I count specific items in a list in Excel? ›

Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5.

How do I count only certain text in Excel? ›

If you want to learn how to count text in Excel, you need to use function COUNTIF with the criteria defined using wildcard *, with the formula: =COUNTIF(range;"*") . Range is defined cell range where you want to count the text in Excel and wildcard * is criteria for all text occurrences in the defined range.

How do I count if a cell contains specific text from a list? ›

Count Cells that Contain a Particular String Anywhere within the Cell
1. Type =COUNTIF( in the cell where you want to see the count.
2. Select the range of cells to count.
3. Type a comma to go to the next argument and enter the text for which you want to search. ...
4. Hit enter and you have the result:

How do you count the number of occurrences multiple criteria in Excel? ›

How to Countif Multiple Criteria? The Excel formula to countif multiple criteria is =countifs(). The “s” on the end makes it plural and therefore implies that there are multiple criteria (2 or more).

How do I count only cells with certain values? ›

Use the COUNTA function function to count only cells in a range that contain values. When you count cells, sometimes you want to ignore any blank cells because only cells with values are meaningful to you. For example, you want to count the total number of salespeople who made a sale (column D).

How do you count unique occurrences in a list? ›

You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears.

How do I count specific values in sheets? ›

The COUNT function in Google Sheets allows you to count the number of all cells with numbers within a specific data range. In other words, COUNT deals with numeric values or those that are stored as numbers in Google Sheets. The syntax of Google Sheets COUNT and its arguments is as follows: COUNT(value1, [value2,…])

How do I count occurrences in Excel? ›

Enter the following data in an Excel spreadsheet. Click Insert > PivotTable.
...
Count how often multiple values occur by using a PivotTable
1. In the Summarize value field by section, select Count.
2. In the Custom Name field, modify the name to Count.
3. Click OK.

How do I count specific words in a column in Excel? ›

In Excel, I can tell you some simple formulas to quickly count the occurrences of a word in a column. Select a cell next to the list you want to count the occurrence of a word, and then type this formula =COUNTIF(A2:A12,"Judy") into it, then press Enter, and you can get the number of appearances of this word.

How do you count how many cells contain certain text or value? ›

On the Formulas tab, click Insert, point to Statistical, and then click one of the following functions:
1. COUNTA: To count cells that are not empty.
2. COUNT: To count cells that contain numbers.
3. COUNTBLANK: To count cells that are blank.
4. COUNTIF: To count cells that meets a specified criteria.

### How do I count cells in Excel with partial text? ›

Select a blank cell you will place the counting result at, type the formula =COUNTIF(A1:A16,"*Anne*") (A1:A16 is the range you will count cells, and Anne is the certain partial string) into it, and press the Enter key. And then it counts out the total number of cells containing the partial string.

How do you count cells that do not contain multiple specific text in Excel? ›

In a blank cell enter the formula =COUNTIF(A1:A40,"<>*count*") and then press the Enter key. Then you will get the number of cells which don't contain the specific text of "count" in the specific Range A1:A40.

## Videos

1. Excel - Do a Count of Unique Values | Text and/or Number | Get a Count of Unique Values in Excel
(Chester Tugwell)
2. Count Cells that Contain Specific Text in Excel - EQ98
(TeachExcel)
3. Count specific words in Excel
(ExcelTips)
4. How to count duplicates in a list
(Barb Henderson)
5. Using Count and CountA in Excel - Excel Tutorial
(Technology for Teachers and Students)
6. How to use COUNTIF and COUNTIFS in Microsoft Excel
(Kevin Stratvert)
Top Articles
Latest Posts
Article information

Author: Neely Ledner

Last Updated: 06/30/2023

Views: 6574

Rating: 4.1 / 5 (42 voted)

Author information

Name: Neely Ledner

Birthday: 1998-06-09

Address: 443 Barrows Terrace, New Jodyberg, CO 57462-5329

Phone: +2433516856029

Job: Central Legal Facilitator

Hobby: Backpacking, Jogging, Magic, Driving, Macrame, Embroidery, Foraging

Introduction: My name is Neely Ledner, I am a bright, determined, beautiful, adventurous, adventurous, spotless, calm person who loves writing and wants to share my knowledge and understanding with you.