To count cells that are not the literal text N/A, use =COUNTIF(A2:A20,"<>N/A"). If blank cells should not count either, use =COUNTIFS(A2:A20,"<>N/A",A2:A20,"<>"); if you mean Excel’s actual #N/A error, use a different formula because an error value is not the same as the text N/A.
The basic COUNTIF formula
COUNTIF checks a range against one condition and returns the number of cells that meet it. Its syntax is =COUNTIF(range,criteria). The criteria <>N/A means “not equal to N/A”: the angle-bracket and equals combination is Excel’s not-equal operator. In the first formula, A2:A20 is the range to inspect, and N/A is the exact text value to exclude.
For example, suppose A2:A7 contains Ready, N/A, Review, Ready, N/A, and Hold. With those six cells as the range, =COUNTIF(A2:A7,"<>N/A") returns 4: it excludes the two cells containing N/A and counts the other four. The formula counts matching cells, not distinct values, so repeated entries are each included.
Use ordinary straight quotation marks around the criterion. Excel needs them because the criterion combines an operator with text. If you enter the formula without quotes, or use curly “smart” quotation marks copied from formatted text, Excel may report a formula error rather than interpret the condition correctly.
Decide whether blanks should count
A subtle point is that “not N/A” can include an empty cell: a blank is not the text N/A. Therefore, a COUNTIF using only the not-equal condition may include blank cells in its result. If you want a count of populated cells other than N/A, add a second condition that requires the cell to be nonblank.
Assuming the values are in A2:A20, use =COUNTIFS(A2:A20,"<>N/A",A2:A20,"<>"). COUNTIFS applies both conditions to the same range: the cell must not equal N/A and must not be blank. For the six-cell example above, if A5 is blank instead of N/A, the formula returns 4: Ready, Review, Ready, and Hold qualify, while the blank does not.
Choose the version that matches the question you are answering:
- Use COUNTIF if every cell that is not the literal N/A text should count, including blanks.
- Use COUNTIFS if you want populated cells other than the literal N/A text.
- Use an error-checking formula if N/A means Excel’s actual
#N/Aerror.
Text N/A and the #N/A error are different
A cell can display N/A in two fundamentally different ways. It may contain ordinary text typed as N/A, or it may contain Excel’s #N/A error value, often produced by a lookup formula when no match is found. They can look similar on the worksheet, but a text comparison is not the same as testing for an error.
If A2:A20 contains a mixture of values and formulas, and you want to count every cell that is not the #N/A error, use =SUMPRODUCT(--NOT(ISNA(A2:A20))). ISNA identifies cells whose value is specifically the #N/A error; NOT reverses that test, and SUMPRODUCT adds the resulting TRUE/FALSE results after the double unary converts them to 1s and 0s. This formula counts blank cells as not-#N/A too.
If blanks should also be excluded, use =SUMPRODUCT(--NOT(ISNA(A2:A20)),--(IFERROR(A2:A20,"<>")<>"")). The IFERROR portion substitutes a nonblank marker for an error before the nonblank test, while the first condition excludes #N/A specifically. In a range containing Alpha, #N/A, a blank, the text N/A, and Beta, the first error-checking formula returns 4 because it counts the blank; the second returns 3 because it counts only Alpha, the text N/A, and Beta. These formulas test errors, not whether text equals the letters N/A.
How COUNTIF treats case, spaces, and wildcards
COUNTIF is not case-sensitive for text criteria. The condition <>N/A also excludes n/a or N/a when those are stored as text. If capitalization distinguishes meaningful categories in your data, COUNTIF alone cannot make that distinction; you would need a case-sensitive approach rather than changing the capitalization in the criterion.
Extra spaces are a frequent reason a result seems wrong. A cell containing N/A followed by a space is not an exact match for N/A, so the not-equal criterion treats it as a different text value and counts it. The same issue can occur with leading spaces or nonbreaking spaces copied from websites or reports. You can inspect a suspicious cell in the formula bar, or clean a separate helper column with TRIM for ordinary extra spaces. TRIM does not remove every kind of invisible character, so copied data may need additional cleanup.
COUNTIF also recognizes wildcard characters in text criteria: an asterisk can match any number of characters, and a question mark can match one character. The literal criterion N/A contains neither wildcard, so it is treated as ordinary text. If the text you need to match itself includes a wildcard character, prefix that character with a tilde in the criterion to search for it literally.
Choose the right counting method
The simplest formula is not always the right one. Start by defining what one counted item means: any cell other than the literal text, any nonblank cell other than that text, or any cell that is not an error. Those are separate tests and can produce different totals when the range includes blanks, formulas, or error values.
- COUNTIF: Best for one condition, such as excluding the literal text N/A.
- COUNTIFS: Best when you need multiple criteria, such as excluding N/A and excluding blanks.
- SUMPRODUCT with ISNA: Useful when you need to classify actual #N/A errors as errors rather than text.
- A helper column: Helpful when the logic is complex or you want to see which individual rows qualify before summarizing them.
For a helper-column check, put a formula beside each source row that returns TRUE or FALSE for the rule you care about, then count the TRUE results. This adds a visible audit trail and can make a workbook easier for colleagues to maintain. It is often preferable to compressing several checks into one long formula when the data has inconsistent inputs.
Use a suitable range and verify the result
Use the smallest practical range that includes the data. A2:A20 is easier to inspect than a whole-column reference when the list has a known size. Whole-column ranges are convenient for expanding lists, but formulas that repeatedly process entire columns can add unnecessary calculation work in a large workbook, especially when many other formulas depend on them.
If your data is formatted as an Excel Table, structured references can make a formula expand as table rows are added. For example, assuming a table named Records has a column named Status, the formula =COUNTIF(Records[Status],"<>N/A") checks that column. Replace the table and column names with the names in your workbook. The formula still follows the same text-versus-error distinction, and a separate blank condition is still needed if blanks should not count.
Check a formula against a small sample where you know the expected answer. Count the N/A entries manually, decide whether blanks qualify, and compare that decision with the formula result. You can also filter the source column to inspect the values being counted. A result that is off by one often points to an overlooked blank, a trailing space, or a cell containing an error rather than text.
Common problems and quick fixes
If Excel displays a formula error, check that the criterion is surrounded by straight quotation marks and that the range is valid. Excel for Microsoft 365 on Windows normally uses commas between function arguments with standard regional settings. Some regional settings use semicolons instead; if Excel rejects a correctly written comma-separated formula, use the list separator configured for that installation. The formula logic is the same.
If the result is larger than expected, test whether blank cells are being counted and whether some entries have spaces or slightly different text. If it is smaller than expected, check for variations such as N/A – pending or N/A?; those are not exact matches to N/A and should be counted by the basic not-equal formula. Also check whether a cell contains a formula returning an empty string. Such a cell looks blank but is not necessarily an actually empty cell, so blank criteria and formula-generated empty text may behave differently in a counting setup.
If your goal is to count only cells that are not the error and not empty, use the error-aware formula rather than relying on a text criterion. If your source data uses the literal word N/A as a placeholder, the COUNTIFS version may be enough. Knowing which representation the source uses is usually the key to resolving a confusing total.
Frequently Asked Questions
How do I count cells that are not N/A in Excel?
For the literal text N/A, use =COUNTIF(A2:A20,"<>N/A"), changing the range to fit your data. Add a nonblank criterion with COUNTIFS if empty cells should not be included.
Does COUNTIF count blank cells as not N/A?
They can be included because a blank is not equal to the text N/A. To count only nonblank cells, use =COUNTIFS(A2:A20,"<>N/A",A2:A20,"<>").
How do I exclude the #N/A error instead of text N/A?
Assuming the cells are in A2:A20, use =SUMPRODUCT(--NOT(ISNA(A2:A20))). It counts cells that are not the #N/A error, including blanks; use the error-and-nonblank formula above if blanks must be excluded too.
Why is my count wrong when the cell visibly says N/A?
The cell may contain spaces, extra text, a formula result, or an actual #N/A error rather than the exact text N/A. Inspect the cell and decide whether your rule targets text, errors, or both.
Is COUNTIF case-sensitive?
No. COUNTIF text criteria do not distinguish uppercase from lowercase. N/A, n/a, and N/a are treated as the same text for this comparison.
Related
- Excel 3-Flag Icon Set: Setup and Thresholds
- Excel Conditional Formatting for Duplicates: How It Works
- Compare Excel Workbooks: Methods, Steps, and Differences
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Paste in Excel Without Formulas and Keep ValuesTo paste in Excel without copying formulas, copy the cells, select the destination, then choose Home > Paste…
- Excel SUM Column Formula: Examples and Common FixesTo add numbers in a column in Excel, use the SUM function. If the values are in A2…
- Microsoft Excel LAMBDA Function: How It WorksMicrosoft Excel’s LAMBDA function lets you create reusable custom functions using an Excel formula, without writing VBA. For…
- Extract Middle Text in Excel: Formulas and ExamplesTo extract text from the middle of a cell in Excel, use MID: with the source text in…
- Excel Filter Does Not Contain Text: How to Exclude MatchesTo show rows whose cells do not include particular text, open the column’s filter menu and choose Text…
- AVERAGEIF in Excel: What It Returns and How to Use ItAVERAGEIF is not a number in Excel; it is a worksheet function that calculates the average of cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.