COUNTIF has no built-in error-handling argument, so wrap it in IFERROR when you want to replace an error result: =IFERROR(COUNTIF(A2:A100,C1),0). This returns zero if COUNTIF produces an error, but a normal no-match result is already zero; use error handling only when you have a reason to catch an actual failure.
What COUNTIF does—and what it does not do
COUNTIF checks one range against one criterion and returns the number of cells that meet that condition. It is a counting function, not an error-handling function. Its arguments are the range to examine and the criterion to apply, such as a word, number, comparison, or wildcard pattern.
With item names in A2:A8, for example, and the criterion “Apple” in C1, COUNTIF returns the number of cells matching that criterion. If no cells match, it returns 0—not an error. That distinction matters: IFERROR does not turn an ordinary zero into a different result, and it is unnecessary for a straightforward count that is working as intended.
COUNTIF can return an error in certain situations. One documented case is a formula referring to a range in a closed external workbook; that can produce #VALUE!. A criterion that depends on another cell can also cause trouble if the criterion itself is an error. In other cases, the formula may be valid but count the wrong cells because of a range or criteria mismatch. IFERROR catches an error result, but it cannot tell you whether the count is logically correct.
Use IFERROR to provide a fallback
IFERROR evaluates a formula and returns a fallback value if the formula results in an error. In the opening example, the fallback is numeric zero. This is useful when a report should remain readable even if an input or external reference is temporarily unavailable.
For example, with product names in A2:A100 and a requested product in C1, use =IFERROR(COUNTIF(A2:A100,C1),0). If the count is 7, the result remains 7. If COUNTIF returns an error, the result is 0. If the product simply does not appear in the range, COUNTIF returns 0 without IFERROR needing to intervene.
Zero is not always the best fallback. If zero could be mistaken for a genuine count, return a message instead. With product names in A2:A100 and the requested product in C1, use =IFERROR(COUNTIF(A2:A100,C1),"Check source data"). The result will be a number when the count succeeds and the text “Check source data” when an error occurs. Because the output can be either text or a number, downstream calculations may need to account for that.
Another option is to leave the result blank. With the same layout, use =IFERROR(COUNTIF(A2:A100,C1),""). This keeps a display clean but can hide a source problem. For operational reports, a visible warning is often safer than a blank cell, especially if someone might interpret blank as “not applicable.”
Choose a fallback that preserves meaning
Decide what an error should mean to the person using the worksheet before choosing the IFERROR fallback. Replacing every error with zero can make a failed calculation look like a valid finding that no records matched. That may be acceptable for a casual summary, but it can be misleading in a reconciliation or quality-control report.
- Use 0 when a zero is a suitable display fallback and users understand that an error could be concealed.
- Use a message such as “Check source data” when the problem should be investigated.
- Use a blank only when hiding the error is intentional and the blank will not be confused with a valid empty result.
- Leave the error visible while building or auditing a workbook so that the cause is easier to identify.
If you are developing a formula, it is usually better to inspect the unwrapped COUNTIF first. Once the formula works and you know which errors need handling, add IFERROR for the final presentation. This separates diagnosis from display and makes it less likely that a real problem will go unnoticed.
Check the criteria and range before hiding an error
Many apparent COUNTIF problems are criteria problems rather than errors. COUNTIF supports comparison operators such as greater than and less than, and those operators can be combined with a cell reference. If the criterion is typed directly into the formula, it must be enclosed in quotation marks. If it combines an operator with a cell reference, join them with the ampersand.
With scores in B2:B20 and the threshold in E1, use =COUNTIF(B2:B20,">"&E1) to count scores greater than the threshold. If E1 contains 80 and five scores are above 80, the result is 5. The criterion “greater than 80” excludes values equal to 80; use a greater-than-or-equal operator if equality should count too.
Text criteria have their own details. COUNTIF is not case-sensitive, so “apple” and “Apple” match the same text. The asterisk wildcard represents any sequence of characters, while the question mark represents one character. To search for a literal asterisk or question mark, precede it with a tilde. These rules can produce unexpected counts, but they do not necessarily produce formula errors.
Also check that the range is the range you intend to count. A reference that starts one row too high may include a heading; one that ends too early may omit records. When criteria are stored in a cell, check for leading or trailing spaces and confirm that the cell contains the expected value. Applying IFERROR will not correct any of these logic issues.
Count error cells separately from COUNTIF failures
An error value inside the data range is different from COUNTIF itself returning an error. If your goal is to find how many cells contain errors, use a function designed to test each cell, rather than assuming IFERROR around COUNTIF will count them. For example, with possible error values in A2:A10, use =SUMPRODUCT(--ISERROR(A2:A10)). If three cells contain any Excel error, the result is 3.
ISERROR returns TRUE for any error value and FALSE for a non-error value. The double unary converts those TRUE/FALSE results into 1s and 0s, and SUMPRODUCT adds them. To count only #N/A errors, use =SUMPRODUCT(--ISNA(A2:A10)) with the same range layout. If two cells contain #N/A, the result is 2. These formulas identify errors in the data; they are separate from handling a failed COUNTIF formula.
When you want to count ordinary values while ignoring problematic source cells, first check how the source errors affect your particular criteria and formula. Do not replace errors in the source with zeros unless zero is a valid substitute: doing so can change counts for numeric criteria such as “greater than 0.” A dedicated error count alongside the main result can make data quality visible without changing the underlying values.
When to use IFNA or another counting function
IFERROR catches all Excel error types, including #VALUE!, #REF!, #DIV/0!, and #N/A. IFNA catches only #N/A. Since COUNTIF is not a lookup function and #N/A is not its usual issue, IFERROR is the more common wrapper for a COUNTIF formula. Use IFNA only if you specifically want to handle #N/A while leaving other errors visible.
For multiple conditions, consider COUNTIFS rather than stacking separate COUNTIF formulas. With departments in A2:A100, status values in B2:B100, and the department and status to match in D1 and E1, use =COUNTIFS(A2:A100,D1,B2:B100,E1). If four rows meet both conditions, the result is 4. COUNTIFS applies all listed conditions, so its criteria ranges need to be the same size and shape. A mismatch is a common source of unexpected results or errors.
COUNTIFS does not replace error handling: if its formula returns an error, you can wrap it in IFERROR too. But switching functions just to suppress an error is not a fix. Choose COUNTIF for one condition and COUNTIFS for several conditions, then investigate any error returned by either formula.
A practical troubleshooting sequence
When a COUNTIF formula displays an error or an unexpected result, work through the formula before applying a blanket fallback:
- Read the displayed error. Check whether it is an Excel error such as #VALUE! or #REF!, rather than a zero that simply means no cells matched.
- Temporarily remove IFERROR. Seeing the underlying result helps distinguish a calculation failure from a fallback that is hiding it.
- Inspect the range and criterion. Confirm the references, check for an error in a criterion cell, and verify that comparison operators and quotation marks are used correctly.
- Check external references. If the range belongs to another workbook, confirm that the file is available and test the formula with the source workbook open.
- Compare the result with a small sample. Manually check a few cells, including boundary values and text with spaces or wildcard characters.
- Add a deliberate fallback. Once the formula is understood, choose a message, blank, or zero that will not mislead the people reading the result.
Excel for Microsoft 365 on Windows uses the same COUNTIF and IFERROR syntax shown here as Excel for Mac and Excel for the web. Menu paths are not needed to enter these formulas; type them in the formula bar or directly in a worksheet cell. If your regional settings use semicolons as formula separators, Excel may display semicolons in place of the commas shown in these examples.
Frequently Asked Questions
Does COUNTIF return an error when there are no matches?
No. A valid COUNTIF formula returns 0 when no cells meet its criterion. IFERROR is for an actual error result, not for a normal no-match result.
Can COUNTIF count cells that contain errors?
COUNTIF is not the clearest tool for counting Excel error values. To count all errors in A2:A10, use =SUMPRODUCT(--ISERROR(A2:A10)). To count only #N/A values in that range, use =SUMPRODUCT(--ISNA(A2:A10)).
Should I use IFERROR or IFNA with COUNTIF?
Use IFERROR when you want to replace any error from the formula. IFNA replaces only #N/A and lets other error types remain visible. For most COUNTIF error handling, IFERROR is the broader choice.
Why does COUNTIF show #VALUE!?
One documented cause is a reference to a range in a closed external workbook. Also inspect the criterion and its referenced cells for errors. Remove any IFERROR wrapper temporarily so you can see the original result and diagnose the cause.
Does IFERROR fix an incorrect count?
No. IFERROR changes what is displayed when a formula errors; it does not repair an incomplete range, incorrect criterion, extra spaces, or unintended wildcard matching. Check the formula logic separately.
Related
- Center Across Selection in Excel: Add It to Quick Access
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
- Capitalize Shortcut in Excel: Fast Ways to Change Case
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Outlook Clean Up Conversation Not Working? Fix ItIf Outlook Clean Up Conversation is missing, unavailable, or leaves messages behind, first check that you are using…
- COUNTA Function Not Working in Excel? Fix ItIf COUNTA seems not to work in Excel, first check that its range includes the cells you intend…
- Excel Unable to Remove a Page Break? Find the Right FixIf Excel will not remove a page break, first check whether it is manual or automatic: select the…
- Excel Can’t Push Non-Empty Cells: How to Fix ItExcel shows this error when inserting cells, rows, or columns would force existing worksheet content beyond the sheet’s…
- Excel Unique Records Only Not Working? Fix the FilterIf Excel’s Unique records only option is not giving the result you expect, check the list range first:…
- XLOOKUP Not Working With Text? Fix the MatchYes—XLOOKUP can look up text, and it uses an exact match by default. When it fails, the usual…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.