提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何计算单元格是否包含Excel中的文本或文本的一部分?

假设您有以下数据,并想要计算包含文本的单元格数“Apple“,单元格数包含文本”橙市“和细胞包含”“另外,如何完成它?在本教程中,我们解释了 COUNTIF 详细功能可帮助您快速计算单元格数量(如果它们包含Excel中的文本或特定文本的一部分)。 此外,我们还推出了一个非常棒的功能,只需点击即可轻松实现。


在Excel中,如果单元格包含文本或文本的一部分,可以轻松计数:

在此 选择特定单元格 实用程序 Kutools for Excel 如果它们包含某些文本或部分文本,可以帮助您快速计算范围内的单元格数。 在弹出对话框中获得结果后,将自动选择所有匹配的单元格。 立即下载Kutools for Excel的全功能60天免费试用版!


使用COUNTIF函数计算单元格是否包含文本或部分文本

COUNTIF函数可以帮助计算包含Excel中一系列单元格中部分文本的单元格。 请做如下。

1。 选择一个空白单元格(例如E5),将下面的公式复制到其中,然后按 输入 键。 然后向下拖动“填充手柄”以获得所有结果。

=COUNTIF(B5:B10,"*"&D5&"*")

句法

=COUNTIF (range, criteria)

参数

  • Range (必填):您想要计算的单元格范围。
  • 标准 (必需):确定将对哪些单元格进行计数的数字,表达式,单元格引用或文本字符串。

笔记:

  • 在公式中,B5:B10是您要计算的单元格范围。 D5是包含您要查找的内容的单元格引用。 您可以根据需要更改参考单元格和公式中的条件。
  • 如果您想直接在公式中键入要计算的文本,请应用以下公式:
    =COUNTIF(B5:B10,"*Apple*")
  • 此公式不区分大小写。

计数单元格包含带COUNTIF函数的文本

如下面的屏幕截图所示,如果要计算仅包含文本的特定范围内的单元格数,本节中的方法可以为您提供帮助。

1。 选择一个空白单元格输出结果,将下面的公式复制到其中,然后按 输入 键。

=COUNTIF(B5:B10,"*")


使用Kutools for Excel计算单元格是否包含文本或部分文本

小技巧:除了上面的公式,这里介绍一个很棒的功能,以轻松解决这个问题。 随着 选择特定单元格 实用程序 Kutools for Excel,如果单元格包含文本或部分文本,则可以快速计算。 您甚至可以根据需要使用此功能进行OR或And条件识别。 请做如下。

使用前 Kutools for Excel,你需要花几分钟 免费下载并安装它 首先。

1。 选择要计算包含特定文本的单元格数量的范围。

2。 点击 Kutools > 选择 > 选择特定单元格.

3。 在里面 选择特定单元格 对话框中,您需要:

  • 选择 细胞 在选项 选择类型 部分;
  • 具体类型 部分,选择 包含 在下拉列表中输入 Apple 在文本框中;
  • 点击 OK 按钮。
  • 然后会弹出一个提示框,告诉您符合条件的单元格数量。 点击 OK 按钮和所有合格的单元格同时被选中。

提示。 如果您想获得此实用程序的免费试用(60日), 请点击下载,然后按照上述步骤去应用操作。


相关文章

在Excel中使用具有多个条件的countif
在Excel中,COUNTIF函数可以帮助我们计算列表中某个值的数量。 但有时,我们需要使用多个标准进行计数,这将更加复杂。 本教程将向您展示实现它的方法。
点击了解更多...

计算单元格是否以Excel中的特定文本开头或结尾
假设您有一系列数据,并且您想要计算工作表中以“kte”开头或以“kte”结尾的单元格数。 本文介绍了一些技巧,而不是手动计数。
点击了解更多...

在多个工作表中标识特定值
你如何计算多个工作表中的特定值? 例如从多个工作表中获取特定值“Excel”的出现次数。 本教程介绍了完成它的方法。
点击了解更多...


演示:使用Kutools for Excel计算单元格是否包含文本或部分文本



  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 和保持数据; 分裂细胞含量; 结合重复行和总和/平均值...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 喜欢并快速插入公式,范围,图表和图片; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 数据透视表分组 周数,星期几等...... 显示未锁定,锁定的单元格 用不同的颜色; 突出显示具有公式/名称的单元格...
kte tab 201905
  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    José Luna López · 3 months ago
    EXCELENTE MUCHAS GRACIAS!!!! ME SIRVIÓ DE MUCHO!!!!
  • To post as a guest, your comment is unpublished.
    Bala · 7 months ago
    Want to countif

    How many apple text in row2 when row 3 is apple farm
  • To post as a guest, your comment is unpublished.
    bubie · 7 months ago
    hi tolong. macam mana ni?
  • To post as a guest, your comment is unpublished.
    Brian · 9 months ago
    Okay, what if I want to do a countif on a specific position in the cell. I'm looking for an 'r' in the third character following a '-' in the cell. So in English count only cells where the third character following the dash ('-') is an 'r'.
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Brian,
      Sorry can't help you with that yet.
  • To post as a guest, your comment is unpublished.
    Renee · 1 years ago
    Hi,
    needing help please, i need this to do count over 33 work sheets with the same name. this is what i tried to start with =COUNTIF( "1 - 33'!,"*Bosch MIC 7000i IP7230 1080p*") - but no go. the 1 - 33 is the number range of spreadsheet names.

    How ever this is where it gets trickier.
    i need it to also include the totals in the individual spread sheet - name of product / part number and supplier....
    so simple hahahaha
  • To post as a guest, your comment is unpublished.
    Tresa · 1 years ago
    Is there a way to use the COUNTIF function but rather than typing in the word to count, the formula can look at another cell where the user could type the name into the cell and the cell next to it (with the COUNTIF function) will show how many time the typed in date is in the worksheet?
    • To post as a guest, your comment is unpublished.
      Honza · 1 years ago
      Yes, I've just used it. Assuming the cell where you type this word is C1, the syntax would be following: =COUNTIF(A2:A6,"*"&C1&"*"). Also works with SUMIF, you just add the third condition (sum range).
  • To post as a guest, your comment is unpublished.
    No · 1 years ago
    Thank you!
  • To post as a guest, your comment is unpublished.
    ravikumar · 1 years ago
    Dear Crystal ,

    Please find this Excel ..


    i cannot solve this ...


    My Problem is i want to show yes or no for column J reached 80 % (subtotal) other wise highlight the cell untill reached 80% of the total .and also give me the formula for the same.


    thanks a lot for your quick reply.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear ravikumar,
      Sorry for reply so late. I still can't find your uploaded file. Is there something wrong while uploading?
      You can send it to my email address: zxm@addin99.com or post your question to our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.
  • To post as a guest, your comment is unpublished.
    ravikumar · 1 years ago
    please find this picture and tell me the formula for count by name but remember vin is same or multiple entries done in the same vin ..so please tell me i want to count how many vin my no is +91 8655315561 or whats app me its urgent
  • To post as a guest, your comment is unpublished.
    rajesh · 1 years ago
    in the column A employee name is written and in column B car number is written and c column accessories amount is written ..etc


    i want count a how many car is billed with accessories in front of their name ...


    i want formula for count function i tried many function but unable to find it ...so please give me solution for this.
  • To post as a guest, your comment is unpublished.
    JoAnn · 1 years ago
    The formula only seems to work if the word I'm counting is the FIRST word in the cell. How do I count the number of cells that CONTAIN that word, whetheror not it's at the beginning of the cell?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear JoAnn,
      Thank you for your comment. The formula =COUNTIF(A2:A6,"*Apple*") can count the number of cells that contain the word Apple no matter which position it loactes in the cell. Please don't forget to enbrace the word with asterisks.
  • To post as a guest, your comment is unpublished.
    K · 1 years ago
    Hi,


    I have want to count the number of cells which has 2 specific words.

    Example, I have cells which may contain up to 6 words separated by a comma.

    Example:

    A1: banana,apple,orange
    A2: apple,orange,pineapple
    A3: pineapple,banana,guava
    A4: apple,banana,guava
    A5: orange,guava,pineapple
    A6: apple,guava,orange


    I want to count how many cells contain the words Apple AND Orange.


    In the above case, count would be 3


    Please Help, thanks


    K
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      As the problem you mentioned, please try this array formula =SUM(--(IF((LOWER(LEFT(TRIM(A1:A6),LEN("apple")+1))="apple,"),TRUE,IF(LOWER(RIGHT(TRIM(A1:A6),LEN("apple")+1))=",apple",TRUE,(IF(ISERR(SEARCH(",apple,",TRIM(A1:A6))),FALSE,TRUE))))*IF((LOWER(LEFT(TRIM(A1:A6),LEN("orange")+1))="orange,"),TRUE,IF(LOWER(RIGHT(TRIM(A1:A6),LEN("orange")+1))=",orange",TRUE,(IF(ISERR(SEARCH(",orange,",TRIM(A1:A6))),FALSE,TRUE)))))) + Ctrl + Shift + Enter
  • To post as a guest, your comment is unpublished.
    Moataz · 2 years ago
    i have excel sheet containing 3 columns (Section / Name / Gender)
    what formula can count how many "male" or "female" in very section
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Moataz,
      Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do?
  • To post as a guest, your comment is unpublished.
    SUSH · 2 years ago
    I HAVE TO COUNT THE TOTAL CELL WHICH CONTAINS A NAME AND NUMBER, SO HOW TO COUNT NAMES AND NUMBERS IN EXCEL EX. CELL A1:A5 CONTAINS APPLE 2143 BALL 5431 CAT TELL ME HOW TO COUNT IT
    • To post as a guest, your comment is unpublished.
      V · 2 years ago
      =COUNTIF(A1:A5,"APPLE")


      Sorry I am not sure I got your question. Would be nice if you could provide screenshot of what you are trying to do..
      • To post as a guest, your comment is unpublished.
        Kyle McLaughlin · 2 years ago
        Your response doesn't quite work, you must include * around the text you're searching. ie =countif(A1:A5, "*apple*")
      • To post as a guest, your comment is unpublished.
        Kyle · 2 years ago
        Your response does not work in this scenario.
  • To post as a guest, your comment is unpublished.
    Graham · 2 years ago
    Hi,

    Great tutorials.

    This is just what I have been trying to do, however with one exception.

    What I need to do is instead of search and count for a cell that contains apple I need it to search to see if the cells contain the contents of a another cell with wildcard.

    Currently using COUNTIF(I$8:O$30,B36) which is on the most part doing the job. Cell B36 will contain 2 or the initials, eg GP or TV, which works fine but what I need to do is count if the cells contains GP when cell b36 contains GP/TV.

    The formula COUNTIF(I$8:O$30,B36) does not then pick up and count that cell because it is not just GP.

    I know I could use =COUNTIF(A2:A6,"*GP*") however the cell for the code has 52 if commands in it and is repeated on 37 rows so its not ideal to use that.

    Can it be done?

    Thanks for looking
    • To post as a guest, your comment is unpublished.
      Luke · 2 years ago
      Graham, did you ever find a way to do this please? As I'm currently trying to find a way to do the exact same thing.

      Thank you
      • To post as a guest, your comment is unpublished.
        Asger T.P. · 1 years ago
        If we take Graham's example, then you can write: COUNTIF(I$8:O$30,"*"&B36&"*").

        Hope this helps :)
      • To post as a guest, your comment is unpublished.
        Kyle McLaughlin · 2 years ago
        When using =countif(s) you can use * around the specific text you're trying to look for in a statement. For example, I was looking to count cells where they had the word "ran" in them, when they would be vastly different entries. There would be such things as "ran off road", "animal hit/ran off road", "overturn/ran off road".

        So I did basically =countif(range, "*ran*") and it works fine. Hope this helps?
      • To post as a guest, your comment is unpublished.
        Kyle M · 2 years ago
        When using =countif(s) you can use * around the text you're trying to look for in a statement. For example, I was looking to count cells where they had the word "ran" in them, when they would be vastly different entries. There would be such things as "ran off road", "animal hit, ran off road", "overturn, ran off road".

        So I did basically =countif(range, "*ran*") and it works fine. Hope this helps?
    • To post as a guest, your comment is unpublished.
      V · 2 years ago
      What i did before was place the this code: (=COUNTIF(A2:A6,"*GP*")) on 1 cell and another formula in another cell, then just click =SUM on the 2 cells with the code.
      • To post as a guest, your comment is unpublished.
        Tony · 2 years ago
        Try =COUNTIF(I$8:O$30,"="&"*"&B36&"*")
        • To post as a guest, your comment is unpublished.
          J · 2 years ago
          This worked for me, except I don't want it count if the cell is blank. Any suggestions?