Countif not working on Excel for Mac

Ian Lancaster 20 Reputation points
2026-08-10T15:11:33.1666667+00:00

I'm trying to use COUNTIF to count the number of cells with a specific text in a large range of cells (c1600 rows), but whatever syntax I use and whatever I enter, the result is:
"This value doesn’t match the data validation restrictions defined for this cell."

What I'm typing is =COUNTIF(H2:H1590,"specifictext"). What am I doing wrong or not doing?

Thank you for help!

Microsoft 365 and Office | Excel | For home | MacOS
0 comments
Answer accepted by question author
IGYQ 22,055 Reputation points Independent Advisor
2026-08-10T17:25:08.04+00:00

Hi!

Welcome to the Microsoft Q&A forum.

Your formula itself is correct. =COUNTIF(H2:H1590,"specifictext").

The error you have “This value doesn’t match the data validation restrictions defined for this cell” indicates the problem is likely the cell where the formula is being entered, not your formula itself.

First, select the cell where you are entering the COUNTIF formula. Then go to Data → Data Validation and select the Settings tab. Check the Allow option to see what type of restriction is applied to the cell. If the cell has a restriction such as List, Whole number, Decimal, or Custom, that restriction may be preventing the formula from being entered. If you do not need the data validation, select Clear All and click OK. Finally, enter the COUNTIF formula into the cell again and check whether it is accepted.

Let me know if this fixes it. If not, let me know so I can assist you further.

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest

  1. AI answer

    2026-08-10T15:11:50.6066667+00:00

    =COUNTIF(H2:H1590,"specifictext") is valid COUNTIF syntax. The message “This value doesn’t match the data validation restrictions defined for this cell” indicates the problem is likely the cell where the formula is being entered, not the COUNTIF formula itself.

    Try this:

    1. Select the result cell where the formula is being entered.
    2. Check whether that cell has Data Validation applied, and remove or change it so formulas/results are allowed.
    3. Make sure the formula starts with =.
    4. Make sure the text criterion is in quotation marks, which is already correct in the formula shown.
    5. If the cell was formatted as Text, change it to General, then press F2 and Enter to force Excel to recalculate it as a formula.

    A few COUNTIF checks:

    • Correct syntax: COUNTIF(range, criteria)
    • For text, criteria must be in quotes: =COUNTIF(H2:H1590,"specifictext")
    • COUNTIF is for a single criterion. If more than one condition is needed, use COUNTIFS.

    If the formula still returns an unexpected result after fixing the destination cell, check the source data for hidden issues such as:

    • leading or trailing spaces
    • inconsistent quotation marks
    • nonprinting characters

    In those cases, TRIM or CLEAN can help normalize the text.

    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.