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.

CustomerCount01

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")

CustomerCount02

  • 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)

CustomerCount03

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)

CustomerCount04

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.

CustomerCount05

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.

 

_______________

26 comments to Count Cells With Specific Text in Excel

  • Angelica Y.

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

  • minizoro

    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?

  • Jo Field

    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

    • Robb M.

      =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".

  • Al

    WONDERFUL HELP. THANKS A MILLION!

  • Skey

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

  • Rajendra Patil

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

  • bharath

    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)

  • Barry Broeders

    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

  • Esteban

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

  • vicky

    thanks it is essay to get
    bless you

  • Ross

    Thanks, really needed this!

  • Hani

    That's nice!

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

  • Shiraz

    Thank you for this help.

  • Craig

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

  • Dileep

    THANKS AND GREAT YOU ARE REALLY GENIUS

  • Andries

    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

  • V Missirian

    Thank you so much :-)

  • Jason

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

  • Ankush Agarwal

    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".

  • Jordan Craw

    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!

  • Cindy G

    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.

  • Pam S

    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?

  • saba

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

  • BillUSA

    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.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>