In Excel, COUNTIFS returns #VALUE! most often because its criteria ranges are different sizes; make every range cover the same rows and columns. Also check whether a criteria argument contains an error or a referenced external workbook is closed, since either can be part of the problem.
What COUNTIFS does
COUNTIFS counts records that meet two or more conditions. It checks the first criteria range against its first condition, the second range against its second condition, and so on; a row is counted only when all the paired conditions are true. The function is available in Microsoft Excel for Microsoft 365, including Excel for Windows.
For example, with regions in A2:A10 and sales amounts in B2:B10, this formula counts rows where the region is North and the sale is at least 100: =COUNTIFS(A2:A10,"North",B2:B10,">=100"). The formula returns 2 if exactly two rows meet both conditions. Each range has nine cells, so Excel can compare the two conditions row by row.
The #VALUE! error is different from a result of 0. Zero means the formula ran but found no rows that satisfied every criterion. An error means Excel could not evaluate the formula as written or resolve something it refers to.
The most common cause: ranges with different dimensions
Every criteria range in one COUNTIFS formula must have the same number of rows and columns as the other criteria ranges. If one range is A2:A10 and another is B2:B9, their sizes do not match. Excel cannot consistently pair cells across the ranges, so the formula can return #VALUE! instead of a count.
This issue often happens after editing a formula by hand: one endpoint is changed while the others are not. It can also occur when a formula combines a whole-column reference with a short range, or when ranges start on different rows. Check both the height and width, not just whether the ranges refer to similar data.
- For row-based data, make all criteria ranges start and end on the same rows, such as A2:A500 and B2:B500.
- For two-dimensional ranges, make the row and column counts match. A2:C10 and D2:F10 are the same size; A2:C10 and D2:F9 are not.
- When using whole columns, keep the reference style consistent, such as A:A and B:B, rather than mixing A:A with B2:B500.
Matching dimensions does not mean the ranges must be in adjacent columns. They may be separate parts of a worksheet, as long as their shapes and corresponding record positions line up.
How to diagnose the formula
Start with the formula bar and inspect each range reference in order. Confirm that every range has the same first and last row and the same number of columns. If the ranges use named ranges or table references, check what each name currently refers to rather than relying on its label.
- Click the cell displaying #VALUE! and read the complete formula in the formula bar.
- Count or select each referenced range and compare its dimensions. Look for an off-by-one endpoint, a missing row, or a whole-column reference paired with a partial column.
- Temporarily simplify the formula to one range-and-criterion pair, then add the remaining pairs back one at a time. This helps identify which addition introduces the error.
- Inspect cells used directly as criteria, such as a cell holding a threshold or category. If that cell itself contains an error, fix its underlying formula or replace the error with a valid criterion.
- If the formula refers to another workbook, open that workbook and recalculate. Then check whether the error disappears.
In Excel for Microsoft 365 on Windows, Formulas > Evaluate Formula can help trace a calculation step by step. The Evaluate Formula command is also available in Excel for Mac, although ribbon placement can differ by version. For a quick check without stepping through the formula, select a range reference in the formula bar and press F9 to see what it evaluates to; press Esc afterward so the displayed formula is not replaced by the evaluated value.
Correcting a mismatched-range example
Suppose dates are in A2:A100, regions in B2:B100, and the goal is to count North-region records dated on or after January 1, 2026. The date and region ranges both cover rows 2 through 100, so their dimensions match. With dates in A2:A100 and regions in B2:B100, use: =COUNTIFS(A2:A100,">="&DATE(2026,1,1),B2:B100,"North"). If 14 rows meet both conditions, the result is 14.
If the region range accidentally ends at B99, the ranges no longer describe the same set of records. Correct the endpoint to B100; do not merely add a row to one side of the formula without checking the other ranges. Matching range sizes prevent the error, while matching starting and ending rows also ensures that each date is tested against the region on the same record.
For a worksheet that grows over time, consider using an Excel table. Table columns expand as rows are added, which can make the formula easier to maintain. With a table named Orders and columns named Region and Amount, the formula =COUNTIFS(Orders[Region],"North",Orders[Amount],">=100") counts matching table rows. The two table columns represent the same table records, so they remain aligned as the table expands.
Other reasons COUNTIFS may show #VALUE!
A criteria argument can be the source of an error. For instance, if a formula uses a cell as its criterion and that cell contains #VALUE!, the error may flow into COUNTIFS. Inspect the criterion cell itself, along with any formula that constructs the criterion. A text criterion such as a word or a comparison expression is not the same thing as an error value.
References to a closed external workbook are another possibility. Some COUNTIF-family formulas using external workbook references can return #VALUE! when the source workbook is closed, depending on the reference and Excel context. Open the source workbook first, then recalculate. If the formula must work while the source stays closed, consider importing the relevant data into the current workbook or using another supported data workflow instead of assuming that a closed-workbook reference will evaluate reliably.
Some formula problems produce different errors. A deleted or invalid reference commonly displays #REF!, while an invalid function name may display #NAME?. A valid COUNTIFS formula that finds no qualifying records returns 0. Identifying the exact displayed error helps avoid changing the data or criteria when the actual issue is a broken reference or a range-size mismatch.
When the result is zero rather than an error
If COUNTIFS returns 0, its ranges may be valid even though the criteria do not match the stored values. Check for extra spaces, different spellings, numbers stored as text, and criteria that are too restrictive. Excel date values are stored as serial numbers, so a date criterion needs to be compared with real date values rather than text that only looks like a date.
Comparison operators such as greater than or equal to are included in a text criterion, and a cell reference can be joined to the operator with &. With prices in C2:C20 and a threshold in F1, use this formula to count prices at least as high as the threshold: =COUNTIFS(C2:C20,">="&F1). It returns the number of qualifying prices if F1 contains a usable numeric threshold. Here the formula uses one range, so there is no second range whose dimensions could disagree.
COUNTIFS criteria can also use wildcards. An asterisk represents any sequence of characters, and a question mark represents one character; use a tilde to match a wildcard character literally. These rules affect which text qualifies, but they do not fix a #VALUE! error caused by incompatible range sizes.
COUNTIFS compared with COUNTIF
COUNTIF handles one condition, while COUNTIFS handles multiple conditions. If the task genuinely has only one condition, COUNTIF may be simpler, but changing functions is not a general cure for #VALUE!. A single-range COUNTIFS formula can work perfectly well; the error usually comes from its arguments or references, not simply from using COUNTIFS.
For example, with status values in D2:D50, COUNTIF can count completed items with =COUNTIF(D2:D50,"Complete"). If a task needs both a status and an owner condition, COUNTIFS is appropriate. With statuses in D2:D50 and owners in E2:E50, use =COUNTIFS(D2:D50,"Complete",E2:E50,"Mina") to count completed items assigned to Mina. Both ranges cover the same rows, keeping each status paired with its owner.
Ways to prevent the error
Build formulas by selecting ranges that begin and end on the same record rows, then copy the formula carefully. When filling formulas down or across, use dollar signs to lock references only where needed; a relative reference that shifts to a different-sized range can introduce a mismatch. Review the resulting formula after copying instead of assuming that Excel adjusted every endpoint as intended.
- Keep source data in consistent columns, with one record per row and a header row separate from the data.
- Use Excel tables for expanding datasets and clear structured references where practical.
- When adding a condition, add a range and its criterion together, and verify the new range matches the existing ones.
- Use descriptive named ranges only after confirming that the names refer to compatible areas.
- Check the exact error displayed before rewriting a formula; #VALUE!, #REF!, and a numeric zero point to different problems.
Frequently Asked Questions
Why does COUNTIFS return #VALUE! when my ranges look the same? Similar-looking references can still have different endpoints or shapes. Compare the number of rows and columns in every criteria range, including named ranges and table references. Also check for an error in a criterion cell or a closed external workbook reference.
Can COUNTIFS use ranges in different columns? Yes. The ranges do not need to be adjacent, but they must have matching dimensions and line up with the same records. For example, A2:A20 and D2:D20 can be paired, while A2:A20 and D3:D21 are misaligned even though both contain 19 cells.
Why does COUNTIFS return 0 instead of #VALUE!? Zero means Excel evaluated the formula and found no rows satisfying all conditions. Check spelling, spaces, data types, date values, and comparison criteria. #VALUE! indicates an evaluation or argument problem, not simply a lack of matching rows.
Will COUNTIFS work with whole-column references? Yes, Excel can use whole-column criteria ranges, and using whole columns consistently avoids unequal heights. For example, a formula may pair A:A with B:B. For very large or complex workbooks, bounded ranges or table columns can be more efficient and easier to inspect.
Does changing COUNTIFS to COUNTIF fix #VALUE!? Only if the task has one condition and the COUNTIFS argument structure is the cause. COUNTIF cannot replace multiple simultaneous conditions, and it will not repair unrelated problems such as a faulty reference or an error in a criterion value.
Related
- Excel Conditional Formatting With Dates: A Practical Guide
- Excel Conditional Formatting for Date Ranges Explained
- Excel Conditional Formatting for Upcoming Dates
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel 3D Map Not Working? Causes, Checks, and FixesIf Excel 3D Maps is not working, first confirm that you are using the Windows desktop version of…
- Excel COUNTIFS Not Working? Fix Common Formula ErrorsWhen Excel COUNTIFS is not working, first check that every criteria range has the same shape, then confirm…
- Excel Circular Reference: Find the Cell and Fix ItIn Excel for Microsoft 365 on Windows, choose Formulas > Error Checking > Circular References, then click the…
- Excel Column Headers Changed to Numbers? How to Fix ItIf Excel’s column headings show numbers instead of letters, the R1C1 reference style is turned on. In Microsoft…
- Excel TRANSPOSE Not Working? Fix the Common CausesIn Excel for Microsoft 365, enter =TRANSPOSE(A2:C4) in one empty cell and make sure the cells where the…
- Excel XLOOKUP #N/A Error? Causes and FixesAn XLOOKUP formula returns #N/A when it cannot find its lookup value in the lookup array, or when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.