# Count Cells With Specific Text in Excel

While working on a client’s sales plan last week, I had to count the orders for a couple of specific customers. Here’s a screenshot of some sample data, with the customer names in column B.

### Count cells that are an exact match

In Excel, you can use the COUNTIF function to count cells that meet a specific criterion. For example, you can use this formula to count the orders that were placed by the customer, BigMart.

**=COUNTIF($B$2:$B$14,”BigMart”)**

- The first argument, $B$2:$B$14, is the range that contains the cells to be counted.
- The second argument, “BigMart”, is the value that we want to count.

There are 3 cells that contain the exact text, “BigMart”, so the COUNTIF formula returns 3.

### Use a cell reference in COUNTIF

Instead of typing the text in the COUNTIF formula, you can refer to a cell that contains the text you want to count. For example, if cell H1 contains the customer name, BigMart, use this formula to count their orders.

**=COUNTIF($B$2:$B$14, H1)**

The COUNTIF formula returns 3, the same result that you got by typing the text in the formula.

### Count non-exact matches

In the sales plan data that I worked on, there were a few variations on some customer spellings, and I wanted to count all of them. In the screenshot at the start of this article, you can see that BigMart is also listed as Big-Mart and Big Mart.

##### Use the * wildcard

To count all the variations, you can use the wildcard character, *****, to represent any characters, or no characters, in the text string. For example, if cell H1 contains the customer name with a wildcard, **Big*Mart**, use this formula to count all the variations.

**=COUNTIF($B$2:$B$14, H1)**

The COUNTIF formula returns 7, because it counts **BigMart**, **Big-Mart** and **Big Mart**.

##### Use the ? wildcard

If you use the **?** wildcard character instead of the ***** wildcard character, it represents one character.

Now the COUNTIF formula returns 4, because it counts **Big-Mart** and **Big Mart**, but not **BigMart**. It doesn’t count BigMart, because there isn’t a character between the g and the M.

### More COUNTIF Examples

For a few more examples of counting cells with specific text, you can visit the Excel Count Functions page on the Contextures website.

_______________

So simple yet took me forever to look up. THANKS!

i have question

i want to Count Cells that Match Text within specific cells.

for example i have a cell (a1) which i want it to count other cells (f1:f13) with condition that if in these cells (f1:f13) there is a text maching other cell(a2) count it, otherwise don’t.

how can i do this?

I would like to know how to take this to the next level. I.e. if I have a column of customer names with different invoice amounts in a corresponding column how would I use this to look for all customers with a matching name but then add the amounts in the invoice amounts column instead of displaying just how many there are?

Thanks

=SUM(($F$3:$F$1500=$A3)*($I$3:$I$1500))

where

column F is your customer names, column A is the customer you want the total for, and column I is the invoice amount.

This is an array formula, so you need to push “shift-ctrl-enter”.

WONDERFUL HELP. THANKS A MILLION!

Tahnks a lot.. this post helps me to get out of big problem.

I want to know for the number of cells whose name begins with specific characters.

I want to count the Account group mention in a particular column &

also have to count the Status mentioned in other column like(Pending/ completed)

Hi can any one tell me a simple way of counting the number of items are the same in one column so that I can put a number against them,

falls

books

tools

falls

and so on if I sort them in to falls they should add up to 2

Cheers B

Just want to say thanks! This helped me a lot.

thanks it is essay to get

bless you

Thanks, really needed this!

That’s nice!

It really helped and save my time, thanks a lot…

Thank you for this help.

I need to modify this formula =COUNTIFS(F$3:F$21,”*Here*”) so it counts “Here” and “Wade”…. Please Help!!!

=COUNTIFS(F$3:F$21,”*Here*”)+COUNTIFS(F$3:F$21,”*Wade*”)+…

And You may wish to either Stop or Continue at that.

THANKS AND GREAT YOU ARE REALLY GENIUS

HI

I would like to know how to create and offset formula that will automatically update my charts that would only count every second column ex A1,C1,E1.The Quantities in the columns are what is needed to update on the charts .

Currently i am using a formula like this :

=OFFSET($A$1,0,0,1,COUNTA($A$1:$Z$1)

Can anyone help

Thank you so much :-)

How to COUNT IF a cell is shaded with a specific color?

Hi,

Thanks for help. But it solves only half of my problem. I have two/three text in cell like in cells(abc, def, ghi). Now I want to count the occurrence of “abc” in the cell range. How to do that? As the above method is returning “0”.

Thank you,

I was looking all over the internet for a solution to help me find a text string in a range of data referencing data from another cell. Using the wildcards is the only solution that I could find to make it work. And it seems so simple. Thanks so much, this is awesome!

Hi – i have a report of records that are identified by ID number, some are repeat. I need to know how many times each ID number appears. Is there a formula for this? Thanks so much, this site has been helpful.

I would like to count cells on another worksheet that contain a certain letter. The cells are not in a range, I need to count every other cell. The formula =countif(SummarySheet!(data cells [separated by commas]), “C”) gives me an error. Is there a way to do this?

Thank u …..really helpful and easy to understand that other posts and blogs

I’d like to know how to use a formula that counts the number of different unique values in a single column. To make it easy for me to describe what I want to do, I’ll use the NFL list of champions. For example, I want the formula to return the number of teams that have won the NFL championship. I have already used the COUNTIF function on a team-by-team basis, but for the purpose of just counting unique vales (i.e. the number of teams that have won the championship) I have been unable to find any solution for a counting formula which uses data from a single column. Everything I have encountered involves a second column of data. The COUNTA function isn’t meant for what I want to do so I was wondering if anyone knows a way of pulling off this task.

Hello

I have worked on the similar excel file that has been provided above for multiple criteria ie, for example in the above table, if I want to count the number of cells that containing customers “Bigmart” and “Minimart”. Its working fine. I used the formula

=SUM(COUNTIFS($B$2:$B$14,{“BigMart”,”MiniMart”))

I GOT THE CORRECT ANSWER.

Can anyone please help me to count the number of cells that contain Customers “Bigmart” and “Minimart” AS WELL AS Items “Crackers” and “Cookies”. I used the following formula but it is not working.

=SUM(COUNTIFS($B$2:$B$14,{“BigMart”,”MiniMart”),$c$2:$C$14,{“Crackers”,”Cookies”})

Is there way count cells when the date change? Say for instance I have a list people and their birthdays. Column A includes the names and Column B includes the birthdays. Is it possible to count how many people have the same birthdays? I believe I can do it birthday individually but is there is there a function that will automatically count them. I am do something similar to that however I have over 75,000 records to check. Is there that well automatically give me total after each birthday is changed. Like, if the previous birthday is not equal to the next date, display total of the previous birthday and reset total. Please advise. Thanks in advance!

I want to know that how many cookies, crackers for bigmart? Beacuse the above formullas are for only bigmart count. I want to count cookies, crackers for bigmart.

dear Sir/madam:

I would like to find ranges: start row and end of the row with the first column (like A with the same text contents).

say this way,

from A1 to A5 , the cell include the same characters “xxx”

but the range from 1 to 5 could be changed with the same “xxx”,

so I would like to know the beginning of A# and the end of A##

how could I do that.

thanks

roseanne

Hi,

I have spreadsheet which contains values like

csrini/Zircon Free csrini PAC PAC PAC PAC PAC PAC PAC PILLAR PILLAR

I want to exclude the certain text such as (PAC PDU and Pillar ). i want to use a dynamic countif formula so that tomorrow there are any other text i want to add in this exclusion I can do it without much work.

Can we add a range in the criteria?

currently i am using the formula below.

=COUNTIFS(B6:M6,”*PAC*”,B6:M6,”*PDU*”,B6:M6,”*PILLAR*”,B6:M6,”*Free*”)

Thanks

suppose i want to multiply the occurrence of the item ”cookies” with quantity ??

i Have a data in which in one column is the product names, the second column is the type that is occurrence of ‘men’ and ‘women”,and in other column is quantities .

i want to know the total quantities in MEN With total occurence .

COLUMN1 COLUMN2 COLUMN3

Product name Type Qty

1.tommy men 16

2. boss men 12

3.cartier men 4

4. Armani women 3

5.

I have seen all the great help and was hoping to get some myself. LOL. I have a spreadsheet that shows customers, example “Paul Pets”. Some of these fields are strikethrough and some not. Is there a way to add all the fields without the strikethrough and then subtract the strikethroughs? This is only one customer of many. Thanks.

The built-in functions, such as COUNTIF, can’t detect strikethrough, and count those cells. You could create a User Defined Function, and there are a few examples in this thread on the Microsoft Excel forum:

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-to-count-strikethrough-cells/273feb61-ae99-458e-8bb0-aae68838815d

Another suggestion…

If you’re striking through the entire contents of the cell, I’d add another column and use an indicator (1, true, complete). Then you can use conditional formatting to strike through that other cell.

And you can use =countif(), =sum() or other functions to count that column.

The good news is that this would make subtotals and autofilters show nicer counts.

Yes, that’s a much better idea, thanks!

I want to count same text cells excluding strike through cells have same text e.g

cell#1 khan

Cell#2 khan

cell#3 ATM

cell#4 khan(strike through)

cell#5 khan

what command i should use that it would count only cell which have khan excluding strike through

here text ” khan’ as “ATM’etc could be changed and currently i am using formula countif() but it counts all cells not excludes strike through

@jazi, please see Paul’s question about strikethrough, just above yours, and the replies to it.

http://blog.contextures.com/archives/2010/03/08/count-cells-with-specific-text-in-excel/#comment-168079

Very helpful, Thanks for this.

Hi,

I have two columns:

Column A = week number

Column B = finished tasks (I can do one task multiple times per day)

I need to count how many times I have created a database in week 34. I have a separate cell that contains the week number ($D$1).

Example:

How many times I have created a database this week ($D$1= “Week 34”)?

week 33 – created DB

week 34 – Created DB

week 34 – Research

week 34 – Emailing

week 34 – Created DB

=> cells shows: 2 (I have created two databases this week)

Thank you in advance for your help!

@Tereza, you can use COUNTIFS or SUMPRODUCT to calculate that.

There are examples on my Count Functions page:

http://www.contextures.com/xlFunctions04.html#count04

I need to count the dates in the month. It is contract dates. I need to count the number entered for the month. So the dates are varied. I need to count the total by the month so that count can be placed in yet another spreadsheet. I have played with COUNTIF but that was a bust. The originating spreadsheet will have dates for the calendar year. This needs to produce the total by the month. Any ideas???

hello,

iahve database that contains words , so how can i define spacific value for particular word pls help on it

thnks

Hello,

Very helpful forum.

Please can you help, I’m okay with basic formulas but having difficulty putting this one together.

Column G is a VRN and column U is the total mileage that vehicle does for the day. My data base is set up over a monthly period and would like to know how to add up the total monthly mileage that vehicle does for the complete month.

=COUNTIF($G$4:$G$208,”1880″)…Unfortunately stuck after this!!!

Thanks

You probably already have a solution for your question, but here’s my 2 cents.

You can use the sumif instead of the countif. Set aside a cell (eg cell Y1) into which you can copy the target VRN. In another cell (eg cell Y2) use the formula =sumif(G4:G208, Y1, U4:U208)

Can you help… I just want to count how many cells before it meets the exact data I want… example the “APPLE” was place in A7 and I want a formula that it will find the apple in the 7th row…

Lee, you can use the MATCH function to find the position of an item in a column:

http://www.contextures.com/xlFunctions03.html

I have a range of excel file by Months for 2016 and I am trying to consolidate it in a pivot table. I have done this and I get a count by Month. But I wish to further specify the count between paid and not paid, no values, words only in the pivot table. I have tried opening Fields, Items & Sets but so far nothing I put works. The pivot is giving me a total count by month, which is fine, but I want to further define it between Count Paid and Count Not paid, then total Count. Can anyone assist?