MS Excel COUNT function for strings containing some value

Douglas Hillmer 0 Reputation points
2026-11-21T21:55:17.5733333+00:00

I want to count all the cells in a range that may contain the letters NA.

For example: XXX: NA or YYYY: NA or just NA, all would count

Here is what I would expect, but it doesn't work:
COUNT(my_range, ~"NA")

Microsoft 365 and Office | Excel | For business | Windows
0 comments No comments

2 answers

Sort by: Most helpful
  1. Demi Nguyen 3,050 Reputation points Independent Advisor
    2026-11-21T22:25:01.67+00:00

    Hello Douglas Hillmer,

    You may refer to the AI-generated answer, as the suggested formula is correct for the requirement described:

    =COUNTIF(my_range,"NA")

    The COUNT function counts cells containing numeric values, while COUNTIF can count cells that meet a specified text condition. In this formula, the asterisks (*) are wildcard characters, so Excel counts any cell containing the consecutive letters NA anywhere within its text.

    For example, the following values would be counted:

    • XXX: NA
    • YYYY: NA
    • NA
    • Status: NA pending
    • BANANA
    • Canada

    Please note that COUNTIF is not case-sensitive. Therefore, NA, Na, nA, and na are treated as the same text. This is why values such as BANANA and Canada are also counted.

    However, a value such as N/A would not be counted because the slash separates the letters N and A, meaning that the cell does not contain the consecutive text NA.

    Therefore, in the example shown, the formula returns 6.

    User's image

    Best regards,


    If the answer is helpful, please click "Yes". If you have extra questions about this answer, please click "Comment". 

    Note: Please follow the steps in documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

    0 comments No comments

  2. AI answer

    2026-11-21T21:55:27.75+00:00

    Use COUNTIF, not COUNT.

    COUNT counts numeric cells. To count cells that contain text such as NA, use a wildcard pattern with COUNTIF:

    =COUNTIF(my_range,"*NA*")
    

    This counts cells where NA appears anywhere in the cell, including examples like:

    • XXX: NA
    • YYYY: NA
    • NA

    Notes:

    • COUNTIF uses only a single criterion.
    • Text matching is not case-sensitive, so NA and na are treated the same.
    • Wildcards are supported: * matches any sequence of characters.
    • If results seem off, check for leading/trailing spaces or other hidden characters in the data.

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.