Comments on: Excel formula to count cells with certain text (exact and partial match)

When analyzing large chunks of information in Excel, you may often want to know how many cells contain specific text. This tutorial explains how to do it in a simple way. Continue reading →

Comments page 2. Total comments: 105

  1. Hi,
    How to count cells with certain numeric value?
    For example this is a column A
    12
    13
    21
    And I want to find how many "1" counts?
    =Countif(a:a,"*1*")
    This returns 0 instead of 3
    Can you help me
    Thanks

  2. Dear Alexander,

    I have set up a Google Form linked to a sheet to register visitors to the school where I work.

    I'm using conditional formatting with a custom formula "=COUNTIF(B:B,B:B)>1" to apply a rule for instances of names that occur more than once in the column. In this case, it will grey out visitors' names who have completed their visit, leaving only current visitors in regular formatting.

    But in the case where a visitor returns for a another visit, their name will appear three (or more times), and therefore will also be greyed out.

    How do I modify the formula to only apply to counts that are even numbers, rather than just greater than one?

    Thank you!

      1. Thank you very much, I'll have a chance to plug it in and experiment later ...

  3. I can't seem to find an answer to a very simple problem: I want to COUNTIF cells whose value does not equal a string, but that string has a space in it.
    I have tried the following permutations and they don't work

    =COUNTIFS(A1:A4,"NOT FOUND")
    =COUNTIFS(A1:A4,"NOT FOUND")
    =COUNTIFS(A1:A4,""NOT FOUND"")
    =COUNTIFS(A1:A4,"'NOT FOUND'")

    Any help much appreciated!

  4. Looking for a formula that counts specific text in a row. For example in a row of 31 characters how mant times does "V" appear?

  5. Good afternoon.

    I'm trying to figure out a way to count how many times a certain name appears in a spreadsheet, with a "yes" in the cell next to it.
    Is that possible?

    Thanks

  6. Hello, I am looking to count if text contains key words

    I need the formula to check for cells that contain "abdo" anywhere in the box and "cramp"

    I want this to be able to pick up; crampy abdominal pain, abdominal cramps, abdominal cramping etc

    =COUNTIF(G2:G151,"*"&"cramp"&"*"&"*"&"abdo"&"*")

    This formula gets only some of the data and I'm not sure how to make it include all of it!

    Thanks

      1. Thanks for your reply

        I'm afraid that returned no results and there are around 12 comments with both pieces of information.

          1. Excellent! That does indeed work!

            =SUMPRODUCT(--ISNUMBER(SEARCH("Small",Sheet1!J2:J145)),--ISNUMBER(SEARCH("bowel",Sheet1!J2:J145)),--ISNUMBER(SEARCH("dist*",Sheet1!J2:J145)*OR("Dila*",Sheet1!J2:J145)))

            Here I'm trying to search for the boxes with EITHER small bowel distention OR small bowel dilation.

            It does not work, any ideas?

            Also, thanks for all your help! It has been, for want of a better word, helpful.

  7. Hi, this article is linked to the COUNTIF article, in which you can count the number of times items in a list are seen in another list. However, as the article states, COUNTIF is case insensitive, and directs you here for case sensitive.
    This article does not provide an alternative method that is case sensitive. Is it possible?

  8. I am trying to to count only cells that are visible in a sorted list. For example, my current formula is =COUNTIF($D$358:$D$95320,A95327). The text in A95327 is EXECUTIVE, however it is counting all EXECUTIVE in the D Column, even the the cells that are hidden due to sorting. Anyone have a solution? Thank you so much.

  9. How can i get something similar to this work? Jack will be on different Classes "A,B,C,D,E,F" and i would like to have a count of how many A classes he has, how many B, C, D, E, F.
    formula for each letter will be used for different cells (A on A1, B on B1, C on C1....)

    =countifs(X:X=O5,I6,W:W)

    Please if anyone has the answer this would be much appreciated. I can do this using a Pivot Table, but i prefer to have the table so it refreshes automatically once a Cell is updated without me refreshing all the time.

    Thanks :)

      1. Thanks Alexander, really helpful - =COUNTIFS($A$2:$A$11, "Apples", $C$2:$C$11,"Cancelled")

  10. Hi there,

    I want to learn how to create a formula to tally a location from an enquiry. The many examples i've seen the data you want to tally is known, ie how many times is the word apple used. My data is unknown till entered & will change on a weekly basis.

    So i have the set cells i want the data tallied from being H7, H13, H19 & H25.

    If i use example data that would be inputted into these cells. H7= Liverpool, H13: Fairfield, H19: Oran Park, H25: Liverpool

    I want excel to spit out & identify that i had x2 Liverpool, x1 Fairfield & x1 Oran Park.

    How is that possible?

  11. I can trying to count total instances that match both a specific text value in a column and a second column if ISNUMBERIC. Example data might look like this.

    Month | Processed | ID
    July | not processed | NULL
    July | processed | 123
    Aug | not processed | 234
    Aug | processed | NULL
    Sept | not processed | 765

    Here is what I am trying to do in a function and if it worked would return a count of 1 as my result:

    The output is in Tab 0 and the data is in Tab 1,

    =COUNTIFS('Tab 1'!A:A,"July",'Tab 1'!C:C,ISNUMBER) <-- this doesn't work. I always get zero. ID is number so I know that is not my issue.

    So basically looking for July in column A and a numeric value in ID. If that is met then count up the instances.

  12. Hello!

    I'm trying to total my clinical hours by category. If I have the hours in column B and the category in column D, am I able to use the COUNTIF function to total these hours? So I don't want to count the actual number of cells in column D containing certain text like "aphasia," "language," "articulation," "fluency," but rather locate the columns that have those titles and count the numerical value in column B for the same row?

    I don't want to know how many cells contain the word "aphasia" - I want to total the hours I have (column B) in the rows containing "aphasia."

  13. Hello,
    I am trying to format a document that calculates the different types of days off employees can take at my business. Ideally we would like to format the cells on the calendar with either a V for vacation or S for sick day. Along with the letter, I would like to have the hours used for example if an individual took 8 hours off for a sick day it would read S8. Is there anyway I can write a countif statement where I can separate the days off based on its category and calculate the hours used as well? I tried using a statement you shared with a different user but our situations seem to be too different for it to work for me as well. My end goal is to calculate the hours used each month for the different types of days off so employees can see the hours they have remaining to use. Thank you for any help or insights you may be able to provide me

    1. Hello!
      You didn't say anything about what data you have for calculations.
      What columns and what rows does your table have?
      However, if you have "S8" written in the cell, then you cannot perform any calculations, since this is text, not a number.

  14. Hello,

    I'm using the COUNTIF formula however, it does not autocorrect itself to a number once I click enter. The formula still remains when I click away. I have two spreadsheets - it's working fine on one but not the other. I've double checked the formula and it's correct

    1. Hi,
      I think there is a symbol in front of the = sign. Perhaps it is a space or '
      Therefore Excel writes your formula as text.

  15. If I have the following:
    A B

    1 G 1
    2 W 2
    3 R 2
    4 G 1
    5 R 1

    Where G, W, R indicates a colour (Green, Red or White) and the numbers in column B indicate what team you are on - what would the formula be to count for example all the R on Team 1?

    I can get a formula to count the how many overall are on W, R or G but now I need a formula to work out how many W are on Team 1, How many W are on team 2, How many R are on team 1, how many R are on team 2 etc etc....

    Thanks in advance for any help.

    1. Sorry A is meant to be above G, W, R etc and B above 1, 2, 2 etc for columns - first column 1-5 is the row number.

      Thanks

  16. Hi,
    I've been trying to solve a problem for a while, but am still struggling.
    The task: I have a list of product names (column A), some of which are very similar (e.g. Protos 1, Protos 100, or Tarin 3, Tarin 3 Pro). Cells can contain more than 1 product.
    When I use the countif formula with *, I get double counts (every mention of Protos 100 is also counted for Protos 1 etc.).
    So I've built the following formula:

    =COUNTIFS(K:K,"*Protos 1*",K:K,""*Protos 100*")

    However, now I am missing results that mention both: Protos 1 AND Protos 100. So I added another part:

    =COUNTIFS(K:K,"*Protos 1*",K:K,""*Protos 100*")+COUNTIFS(K:K,"*Protos 1*",K:K,"*Protos 100*")

    The idea for the second Countifs was that I want to validate both criteria: mentions of Protos 1 AND Protos 100. But the problem now is that this part K:K,"*Protos 1*" in the second countif again gives me all of the Protos 100 as well.

    Would you have any tip on how to solve this?
    Thank you!

    1. Edit: product names are in column K, not A

      1. Sorry, saw another copy paste mistake the formulas are:
        =COUNTIFS(K:K,"*Protos 1*",K:K,"*Protos 100*")
        =COUNTIFS(K:K,"*Protos 1*",K:K,"*Protos 100*")+COUNTIFS(K:K,"*Protos 1*",K:K,"*Protos 100*")

        1. Hello!
          I don't know what separators are used in your values. I assume it's a comma. Then you can use the formula:

          =COUNTIF(K:K,"*Protos 1")+COUNTIF(K:K,"*Protos 1,*") + COUNTIF(K:K,"*Protos 100") + COUNTIF(K:K,"*Protos 100,*")

          I hope it’ll be helpful.

  17. Im trying to create a Dashboard on Sheet 1

    To count a Specific Word (Column A) based on a Specific Day (Column B) on Sheet 2

  18. Good afternoon,
    I have last names in cells in column c. Task is to count all cells in range that contains letter "v" on second place. Would You be so kind and give me solution.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM(--(MID(A1:A10,2,1)="v"))

      I hope this will help

  19. Hi. I have a worksheet with numerous text and numbers. In column A I have different text:A1 AAAA A2: BBBB: A3: AAAA as well. In column be I have numbers B1:1,B2:2,B3:1. How do I lookup for AAAA in column and then count the values which is picked up in column B?

    1. Hello!
      The formula below will do the trick for you:

      =SUMPRODUCT(--("AAA"=A1:A15),B1:B15)

      I hope my advice will help you solve your task.

      1. Thanks so much.
        I have another problem....
        I have also a sheet with multiple texts and 2 sheets.
        On sheet 1 I have text AAA in column A
        On sheet 2 I have various lines in
        Column A Column B Column C
        AAA CAT 1
        BBB DOG 1
        BBB CAT 1
        AAA CAT 2
        How do I look up only AAA Cat on sheet 2 and count the CAT in various lines so that CAT shows the amount of 3 in sheet 1 for AAA.
        Apologies I'm only entry into Excel :-)

        1. Hello!
          To count the number of lines containing both "AAA" and "CAT", use the formula

          =SUM(--("AAACAT"=A1:A15&B1:B15))

  20. Hi
    I'm looking for a formula that if column F says 'VA' then the numeric number in the same row but column E needs to be added into a total on a table.
    If the cell in column F is changed to a different variable eg 'OS' then the value is subtracted from the 'VA' total and added to the 'OS' total.

    1. Bianca, your best bet is to use a pivot table to sumarize data that way. But if you really want it on the same sheet, use SUMIF. E.g. in your case you might have the formula for the total of the VA values have something like =SUMIF(F1:F100,"VA",E1:E100) (assuming you have 100 rows of data)

  21. Thank you for your tutorial it was very insightful and helpful to me. I am wondering though if there is a countif formula that will count letters and numbers and then total the numbers? For Example: if I have cells that contain V10, V8, F8, F4, and V2 is there a countif formula that would total the numbers with the V (10+8+2=20)and the F (8+4) and then total the cells so they read V20 or F10?
    I do not know if that is even possible but I am hoping that it is.
    Thank you very much for your time

    1. Hello Lori-Ann!
      If I understand your task correctly, the following formula should work for you:

      =CONCATENATE("V",SUMPRODUCT(--(LEFT(A1:A16,1)="V"), --(IFERROR(RIGHT(A1:A16,LEN(A1:A16)-1),0))))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  22. Is there a countif formula to count cells if they do NOT contain specific words. For example, I have a countif formula for everything that contains "BAW" in it. Now I would like a countif formula for everything except "BAW" in it but I need to be able to address multiple words like {"BAW","BAE"} etc. Any help would be appreciated. Thank you.

    1. Hello Matthew!
      If I understand your task correctly, the following formula should work for you:
      =SUMPRODUCT(--($A$1:$A$17<>$E$1),--($A$1:$A$17<>$E$2))
      where in E1 written "BAE", in E2 - "BAW"
      I hope it’ll be helpful.

      1. Hi - What if in the same scenario, but you want to exclude cells that contain text that begin with "BAW" looking at an entire column in another sheet in the same workbook?

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)