Home > Formulas > Count > List Items Count Specific Items in Excel ListSee how to count specific items in an Excel list with formula, for text, numbers, dates. Short stepbystep video, free Excel workbook, written steps, screenshots 
Video: Count Specific Items in ListThis 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 FunctionIn 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 ArgumentsWith the COUNTIF function syntax, there are 2 required arguments:

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

Match Specific Text ExactlyIn 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:
Completed COUNTIF FormulaFor a named table, the completed formula shows the table name (tblExact) and column name (Item):
and for a worksheet list, the formula shows the cell reference:
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 Anywhere in CellInstead 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.
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".
Completed Formula With WildcardsFor a named table, the completed formula shows the table and column names:
and for a worksheet list, the formula shows the cell reference:
Use Cell ReferencesTo 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.
You can use a cell reference with wildcard characters too. Use the & (ampersand) operator to join the wildcard characters to the cell reference.

Video Transcript  Count Specific ItemsHere 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. First ArgumentThe 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 ArgumentThe 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 FormulaHere, again, I'll use COUNTIF.
It's going to look for anything that contains the letters, PEN in a string.
Formula ResultAnd when I press Enter we get 6. So one, two, three, four, five, six. It found anything that contained that string of characters, PEN and the case didn't matter. 
Get the Sample FileTo 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. 
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Count Criteria in Other Column
Count Cells With Specific Text
Last updated: June 3, 2024 10:11 AM