Count Specific Items in Excel List

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

This video shows how to use the COUNTIF function to count 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.

COUNTIF Warnings

Here are a couple of warnings, before you use the COUNTIF function.

• The COUNTIF function can't count numbers within numbers. Instead, use one of the other formulas that will count numbers within numbers.
• If the 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. See another formula 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 the Pen orders only.

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]
5. Type a comma, to separate the arguments
6. 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 -- COUNTIF is not case sensitive
7. Type a closing bracket, and press Enter

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

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

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

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

Match Text Anywere in Cell

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

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]
5. Type a comma, to separate the arguments
6. 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 or lower case -- COUNTIF is not case sensitive
7. Type a closing bracket, and press Enter

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

• =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 text that you want to count in a worksheet cell. Then, in the formula, refer to the cell where you typed the text.

For example, here is the formula to match a specific item, with a reference to cell E7, where 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.

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.

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.

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.

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.

Here, again, I'll use COUNTIF. 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.

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.

Download Sample File

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

More Function Tutorials

COUNT / COUNTIF

Count Criteria in Other Column

Count Cells With Specific Text

AVERAGE

SUM / SUMIF

Subtotal Feature