by The tutorial shows how to count number of cells with certain text in Excel. You will find formula examples for exact match, partial match and filtered cells.
Last week we looked at how to count cells with text in Excel, meaning all cells with any text. When analyzing large chunks of information, you may also want to know how many cells contain specific text. This tutorial explains how to do it in a simple way.
How to count cells with specific text in Excel
Microsoft Excel has a special function to conditionally count cells, the COUNTIF function. All you have to do is to supply the target text string in the criteria argument.
Here's a generic Excel formula to count number of cells containing specific text:
The following example shows it in action. Supposing, you have a list of item IDs in A2:A10 and you want to count the number of cells with a particular id, say "AA-01". Type this string in the second argument, and you will get this simple formula:
=COUNTIF(A2:A10, "AA-01")
To enable your users to count cells with any given text without the need to modify the formula, input the text in a predefined cell, say D1, and supply the cell reference:
=COUNTIF(A2:A10, D1)
Note. The Excel COUNTIF function is case-insensitive, meaning it does not differentiate letter case. To treat uppercase and lowercase characters differently, use this case-sensitive formula.
How to count cells with certain text (partial match)
The formula discussed in the previous example matches the criteria exactly. If there is at least one different character in a cell, for instance an extra space in the end, that won't be an exact match and such a cell won't be counted.
To find the number of cells that contain certain text as part of their contents, use wildcard characters in your criteria, namely an asterisk (*) that represents any sequence or characters. Depending on your goal, a formula can look like one of the following.
Count cells that contain specific text at the very start:
Count cells that contain certain text in any position:
For example, to find how many cells in the range A2:A10 begin with "AA", use this formula:
=COUNTIF(A2:A10, "AA*")
To get the count of cells containing "AA" in any position, use this one:
=COUNTIF(A2:A10, "*AA*")
To make the formulas more dynamic, replace the hardcoded strings with cell references.
To count cells that begin with certain text:
=COUNTIF(A2:A10, D1&"*")
To count cells with certain text anywhere in them:
=COUNTIF(A2:A10, "*"&D1&"*")
The screenshot below shows the results:
Count cells that contain specific text (case-sensitive)
In situation when you need to differentiate uppercase and lowercase characters, the COUNTIF function won't work. Depending on whether you are looking for an exact or partial match, you will have to build a different formula.
Case-sensitive formula to count cells with specific text (exact match)
To count the number of cells with certain text recognizing the text case, we will use a combination of the SUMPRODUCT and EXACT functions:
How this formula works:
- EXACT compares each cell in the range against the sample text and returns an array of TRUE and FALSE values, TRUE representing exact matches and FALSE all other cells. A double hyphen (called a double unary) coerces TRUE and FALSE into 1's and 0's.
- SUMPRODUCT sums all the elements of the array. That sum is the number of 1's, which is the number of matches.
For example, to get the number of cells in A2:A10 that contain the text in D1 and handle uppercase and lowercase as different characters, use this formula:
=SUMPRODUCT(--EXACT(D1, A2:A10))
Case-sensitive formula to count cells with specific text (partial match)
To build a case-sensitive formula that can find a text string of interest anywhere in a cell, we are using 3 different functions:
How this formula works:
- The case-sensitive FIND function searches for the target text in each cell of the range. If it succeeds, the function returns the position of the first character, otherwise the #VALUE! error. For the sake of clarity, we do not need to know the exact position, any number (as opposed to error) means that the cell contains the target text.
- The ISNUMBER function handles the array of numbers and errors returned by FIND and converts the numbers to TRUE and anything else to FALSE. A double unary (--) coerces the logical values into ones and zeros.
- SUMPRODUCT sums the array of 1's and 0's and returns the count of cells that contain the specified text as part of their contents.
To test the formula on real-life data, let's find how many cells in A2:A10 contain the substring input in D1:
=SUMPRODUCT(--(ISNUMBER(FIND(D1, A2:A10))))
And this returns a count of 3 (cells A2, A3 and A6):
How to count filtered cells with specific text
To count visible items in a filtered list, you will need to use a combination of 4 or more functions depending on whether you want an exact or partial match. To make the examples easier to follow, let's take a quick look at the source data first.
Assuming, you have a table with Order IDs in column B and Quantity in column C like shown in the image below. For the moment, you are interested only in quantities greater than 1 and you filtered your table accordingly. The question is – how do you count filtered cells with a particular id?
Formula to count filtered cells with specific text (exact match)
To count filtered cells whose contents match the sample text string exactly, use one of the following formulas:
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(B2:B10=F1))
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(B2:B10=F1))
Where F1 is the sample text and B2:B10 are the cells to count.
How these formulas work:
At the core of both formulas, you perform 2 checks:
- Identify visible and hidden rows. For this, you use the SUBTOTAL function with the function_num argument set to 103. To supply all the individual cell references to SUBTOTAL, utilize either INDIRECT (in the first formula) or a combination of OFFSET, ROW and MIN (in the second formula). Since we aim to locate visible and hidden rows, it does not really matter which column to reference (A in our example). The result of this operation is an array of 1's and 0's where ones represent visible rows and zeros - hidden rows.
- Find cells containing given text. For this, compare the sample text (F1) against the range of cells (B2:B10). The result of this operation is an array of TRUE and FALSE values, which are coerced to 1's and 0's with the help of the double unary operator.
Finally, the SUMPRODUCT function multiplies the elements of the two arrays in the same positions, and then sums the resulting array. Because multiplying by zero gives zero, only the cells that have 1 in both arrays have 1 in the final array. The sum of 1's is the number of filtered cells that contain the specified text.
Formula to count filtered cells with specific text (partial match)
To count filtered cells containing certain text as part of the cell contents, modify the above formulas in the following way. Instead of comparing the sample text against the range of cells, search for the target text by using ISNUMBER and FIND as explained in one of the previous examples:
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(ISNUMBER(FIND(F1, B2:B10))))
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(ISNUMBER(FIND(F1, B2:B10))))
As the result, the formulas will locate a given text string in any position in a cell:
Note. The SUBTOTAL function with 103 in the function_num argument, identifies all hidden cells, filtered out and hidden manually. As the result, the above formulas count only visible cells regardless of how invisible cells were hidden. To exclude only filtered out cells but include the ones hidden manually, use 3 for function_num.
That's how to count the number of cells with certain text in Excel. I thank you for reading and hope to see you on our blog next week!
150 comments
I would like to count the number of times a certain word pattern appears in a column: "RUN", "STOP", "STOP". Each word is in a separate cell.
Hello Anna!
We have a special tutorial on this. Please see: COUNTIF formulas with wildcard characters (partial match). For example:
=COUNTIF(A1:A20,"*STOP*")
Thank you for your response!
The trouble with my excel sheet is I have a column of about 5000 "RUNs" and "STOPs". I only want to count the cell progression when the following pattern comes up: "RUN STOP STOP". Is there a way to count a pattern of cells when each word is in a separate cell?
Thank you so much! This was incredibly helpful and exactly what I needed.
My spreadsheet includes a column of names listed multiple times, as well as a column that 'hides' certain among that list (i.e., 'x' = do not want to count). How do I count number of unique names in column (without x's)?
Hello Maggie!
You can find the answer to your question in this guide: Count unique values with criteria. The formula might look as follows:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A10,B2:B10<>"x"))), 0)
Hi,
You probably forgotten to mention that COUNTIF limited with 255 chars. How would you search text string longer than that?
Thanks,
hog
Hi! To count the number of certain values longer than 255 characters in Excel, you can use SUMPRODUCT function. Here's an example of how to do it:
=SUMPRODUCT(--(A1:A10=“your_value”)))
This formula will count the number of cells in the range A1:A10 that contain a specific value (replace “your_value” with the value you want).
Sir,
values are 101, 102, 101, 2101, 1101
If i apply countif formula here,
No. of 101s = 4 ( it is taking 2101 and 1101 also)
Any solution for this ??
Hi! Before asking your question, I recommend that you read article above carefully. It describes several solutions to your problem. For example, in the section Case-sensitive formula to count cells with specific text (partial match).
=SUMPRODUCT(--(ISNUMBER(FIND(101, A2:A10))))
How do I count cells within two columns that contain a specific word in one cell when it's next to another cell containing a different word (or blank) in the column adjacent.
Example:
Grass |
Poison| Ground
Bug |Steel
I would like to count all cells that contain "Poison" in one cell and "Ground" in the a cell adjacent. It doesn't matter which word is in which cell, so long as they are adjacent to one another.
Hello Kay!
You can count the number of values for multiple conditions using COUNTIFS function. To use OR logic, sum two COUNTIFS functions. Look for examples and detailed instructions here: Excel COUNTIFS and COUNTIF with multiple AND / OR criteria.
=COUNTIFS(A1:A30,"Poison",B1:B30,"Ground") + COUNTIFS(B1:B30,"Poison",A1:A30,"Ground")
You can also use SUMPRODUCT function to count by multiple conditions.
=SUMPRODUCT((A1:A30="Poison") * (B1:B30="Ground") + (B1:B30="Poison") * (A1:A30="Ground"))
For more information, please visit: Excel SUMPRODUCT function with multiple criteria.
I hope it’ll be helpful.
Hello Sir!
I want to verify if a specific set of numbers is included in a range. The specific number might be in any position in the range cells.
As an example, I tried the formula below, but then it doesn't take into count the cells that are exactly the same as search value.
=IF(COUNTIF(RANGE,"*"&A1&"*"),"Y","N"))
A1=123
RANGE=
123
21 ,12
12
This becomes as "N" even though one of the range cell is exactly the same as search value.
But it works perfectly for the range below which there are mix of cells (some with exact value, some with the value located in a random position in the cell) and the result is "Y".
123
123, 12
21, 123
12, 21
Hi! There is no exact match between the cell value and the search value in your first example. The first cell is a number, and the formula searches for the text value "*"&A1&"*". In the second example, the second and third cell values are a partial match. Therefore, the formula returns "Y".
Your formula is written incorrectly. The correct way to write it is this:
=IF(COUNTIF(RANGE,"*"&A1&"*"),"Y","N")
For more information, please visit: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique
Hello,
I've got a list of food additives abbreviated by the corresponding numbers and I want to count the amount of dishes for each combination.
Salad 12
Pasta 124
Meat 245
SUMPRODUCT(--EXACT(D4;$B$4:$B$500)) [D4="12"] gave me 2 hits since it counted "Salad" and "Pasta".
How can I fix this?
Hello! Unfortunately, I was unable to reproduce your problem on my worksheet. Your formula and your data return a result of 1.
There was an oversight on my part.
Thank you for your response - I wouldn't have found the error that fast without you!
Hi, I cannot find a solution to this problem. I have a large column with fruit and veg types in them. Some cells have just one item, other cells have multiple items separated by columns. I would like to extrapolate how many times each fruit or veg item occurs in the range. I don't want to search by specific fruit and veg type as the list is too large, but instead want a count of each unique item in the column.
F2:F12
Onion
Potato
Parsnip, Turmeric
Jerusalem Artichoke
Onion
Jerusalem Artichoke
Ginger, Jerusalem Artichoke
Chive, Onion
Jerusalem Artichoke, Potato
Cabbage, Potato
The list should output the following:
Onion 3
Parsnip 1
Turmeric 1
Jerusalem Artichoke 4
Potato 3
Ginger 1
Chive 1
Cabbage 1
Thank you for your help!
I forgot, I would also like to be able to do the same again, but for a specific box size.
Column E2:E12 and F2:F12 look like this:
XLVB Onion
XLVB Potato
LVB Parsnip, Turmeric
LVB Jerusalem Artichoke
LVB Onion
MVB Jerusalem Artichoke
MVB Ginger, Jerusalem Artichoke
MVB Chive, Onion
SVB Jerusalem Artichoke, Potato
SVB Cabbage, Potato
So, I would like to know how many Jerusalem artichokes are in the MVB, for instance. Again, without me specifying the item name.
For example,
MVB Jerusalem artichoke 2
MVB Ginger 1
MVB Chive 1
MVB Onion 1
Thanks again.
Hello! To count the occurrences of a string in a range of cells, use these instructions: How to find substring in Excel. Use the SUMPRODUCT function to summarize the number of matches.
=SUMPRODUCT(--ISNUMBER(SEARCH(E1,B1:B10)))
You can add a second condition to the formula for a specific box size:
=SUMPRODUCT((ISNUMBER(SEARCH(E1,B1:B10)) * (A1:A10=D1)))
How do I do this and not get results back that are contained in what I am searching for? i.e. I want to look across values and count the number of times "reading" appears within sentences but i do not want to count the word "read" or "ding" (etc...). The word to look for is stored in a cell.
Hi! You can create an additional column where you use a formula to write 1 if the word is found. For example:
=IF(ISNUMBER(SEARCH(D1,A1)),1,0)
For more information, please read: How to find substring in Excel
To count how many times a word is found, sum the values in the column.
You can also split the text into words using the TEXTSPLIT function. Then count the number of matches with the desired word using the SUM formula.
=SUM(--(TEXTSPLIT(A1,," ")=D1))
I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.
Thank you Alexander! but it didn't work for my use case, was getting #SPILL when trying to use a range.
For example, i have a list of strings to find:
strings to find on col D, with expected results (row by column):
D E
1 reading 2
2 writing 0
3 spelling 2
range to search (G2:G12):
reading
reading
ding
horse
w
writ
ting
spell
spelling
spelling
l
Hi! If you are comparing whole cell values, you can try this formula to count the number of matches:
=COUNTIF(G2:G11,D1)
or
=SUM(--(G2:G11=D1))
All the necessary information is in the article above.
Please, how to count values from a column whose name I enter on another sheet. I want it to find the right column on the sheet according to the name I entered on another sheet and from there count the occurrence of a certain value. Thank you.
Hi! To create a dynamic reference to a cell, range, column, row, or worksheet, use the INDIRECT function. You can find the examples and detailed instructions here: Excel INDIRECT function - basic uses and formula examples. For example:
INDIRECT("'" & A1 & "'!" & B1)
Hello, I am trying to figure out how to count the below as separate items. Countif says that my example "5401" shows up 5 times, however, some instances have preceding zeros and should be counted separately. My data is set to text, yet are all counted together.
In reality 05401 should equal 1, 5401 should equal 2, 005401 should equal 2 - I have about 65k more like this LOL
05401
5401
5401
005401
005401
I struggled with the same problem. And Trifunov's suggested link did not help. What I ended up doing was adding another column with the formula B1=CONCAT("X",A1,"X") so that the data was no longer numeric. Afterwards C1=COUNTIF(B$1:B$123456,B1) worked as it should. (Slowly!)
Hello Stig!
Maybe I didn't understand the content of the previous question exactly. You can not use the additional column and count the numbers written as text with leading zeros using the SUMPRODUCT function. For example:
=SUMPRODUCT(--(A1:A50=A1))
Hi! If I understand your task correctly, you want to count the number of unique values. I recommend reading this guide: How to count unique values in Excel.
I have 2 tab in main worksheet, like I have a 700 players name in colum a in tab A, but the players details are mention in tab b (player age, player hight etc)
I want a data from tab b to tab A ( tab b contain any partial word from player in tab b)
Plz provide formula
Hi! To find a partial match of text strings and extract the corresponding value, try using these instructions: Using wildcards in VLOOKUP formula.
You can determine partial text string matches using this method: How to find substring in Excel (partial match). You can use it in an INDEX MATCH formula or in a FILTER formula. For example:
=INDEX(Sheet2!B1:B10, MATCH(TRUE,ISNUMBER(SEARCH(Sheet1!A1,Sheet2!A1:A10)),0))
=FILTER(Sheet2!B1:B10, ISNUMBER(SEARCH(Sheet1!A1,Sheet2!A1:A10)))
I hope that what I've said will help you to solve it.
I'm trying to count the instances a specific time reference occurs.
I have a column of cells containing multiple times separated by commas like "2:00, 2:15, 2:30, 2:45, 3:00, 3:15, 3:30, 3:45, 4:00, 5:00, 6:00, 7:00, 7:15" and "6:00, 7:00, 7:15, 7:30"
When I try something like =COUNTIF(E:E,"4:00") it does not count all of them.
Hi! Your data is text. You cannot do any calculation with text. To convert your text to time values, split it using the TEXTSPLIT function or other methods described in this manual: How to split text string in Excel by comma, space, character or mask. Then convert the text to time using the TIMEVALUE function. The formula might look like this:
=TIMEVALUE(TEXTSPLIT(A1,,","))
You can then calculate these time values using the COUNTIF function.
Hello,
I need to extract the number of occurrences of a text on a range in another workbook.
With a defined range the formula works, but trying to make it dynamic doesn't return anything. Can you help me?
The basic formula
=SUMPRODUCT(--(ISNUMBER(FIND($A10;'[Copy of Planning test.xlsx]Planning'!DMH48:DML74))))
Returns 6
One of the attempts
=SUMPRODUCT(--(ISNUMBER(FIND($A10,"'["&B1&"]"&C1&"'!"&CELL("contents";$D$1)&":"&CELL("contents",$E$1)))))
With D1 and E1 varying with the N° of the selected week
D1 : =ADDRESS(B7+45,B6) 'return $DMH$48
E1 : =ADDRESS(B7+71,B6+4) 'return $DML$74
Returns 0
Thanks in advance
Hi! To use dynamic references in a formula, use the INDIRECT function as described in this guide: Making a dynamic reference to another workbook. I hope it’ll be helpful.
Thank you very much for your time,
I managed to do what I wanted.
Hi, wondering if you can help with a problem I am facing.
I am trying to work out how to return a result for a partial text match within a larger variable string series, but where 2 of the return criteria share combinations of the same wording.
For example the string in column G contains (in varying order):
Lemon Tart, Apple Pie, Baked Apple Pie
I want to return a true/false value in separate cells to identify if each of the results are present and so have the following formulae:
Cell H2
=IF(COUNTIF(G2,"*Lemon Tart*"), "Yes", "No")
Cell I2
=IF(COUNTIF(AE745,"*Apple Pie*"), "Yes", "No")
Cell J2
=IF(COUNTIF(AE745,"*Baked*"), "Yes", "No")
The results for Lemon Tart and Baked Apple Pie work perfectly, but "Apple Pie" alone also returns all results for "Baked Apple Pie" because of the wildcards before and after. Is there any way to add an exclusion so that it does not record any results containing "Baked" or containing a space before Apple Pie?
Thanks
Correction on the formulas quoted. It shouldn't say AE745 - this is a paste error
Hi! To get the number of "Apple Pie" values, count the difference I2-J2.
With the COUNTIF function, you cannot count values that do not include certain characters.
Thank you, that may give me a workaround at least.
The challenge is that the string will not always contain both Apple Pie and Baked Apple Pie and so will not always give a result.
Would it be fair to assume that this means there is no easy way to return the true or false value as the result, rather a numerical value instead?
Thanks again.
Hi! If there is no value, the COUNTIF function will return 0. It is not clear from your description what result you want. If you need a logical value, use the IF function.
Hi sir Alexander, hope you can help me on this one please. We have 2 columns (column A = Names & column B = Status), how can I count the cells with certain names on column A BUT for the status should not have the word DONE.
Example: We want to count the number of cells under the name John but to exclude the word DONE on column B.
Hi! To count the matches in two columns, use the COUNTIFS function as well as the recommendations from the article above. Here is an example formula:
=COUNTIFS(A1:A10,"John",B1:B10,"<>"&"Done")
Hi sir Alexander,
i want to use the same argument to the rest of the items in the column.
=COUNTIF(D:D, "*item name*")
i have 1500 different item names.
How can i automate this argument to the whole column with different item name.
Thank You
Hi! You can replace the text string in the formula with a cell reference with the item name. For example,
=COUNTIF(D:D,H1)
=SUMPRODUCT(--(D:D=H1))
I have a file in which multiple selections are available in a cell. So suppose 4 selections are made in a cell and like that in other cells also multiple selections are made, I want to have a count of individual selections from all the cells.
Hi! If multiple selections are made in a cell, all the selected values are written in the cell and separated with delimiters. I think you can use this guide to count them: How to count words in Excel - formula examples.
I had this data and the countif function worked:
A2: May 24, 2023 5:05 pm
B2: Lot up
We changed the date (A) to this format: 5/24/2023 17:05
The formula we were using was: =countifs($a$2:$a$2000,"may 24, 2023*", $b$2:$b$2000,"lot up") which worked.
Changing the date to the "5/24/2023*" does not work.
Any suggestions would be greatly appreciated. (I have to use the new date structure)
Thanks,
Bobby
Hi! I can assume that your date is written as text. So you can only change the format of that date if you change the text. Therefore, the COUNTIFS formula does not work. You can convert text to date in another cell using these guidelines and then apply the date format you want.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.