← Back to Error Directory

#VALUE! Error Inside SUMIFS/COUNTIFS Criteria — Complete Fix

Multi-condition functions have more moving parts, and more places for a size mismatch or operator typo to hide.

Table of Contents

1. Why Multi-Criteria Functions Fail Differently

SUMIFS and COUNTIFS accept pairs of arguments — a range to check, and a criteria to check it against — repeated for each condition you need. This flexibility is exactly why they're more prone to #VALUE! than a simple SUM: every additional criteria pair is another opportunity for a range size mismatch or an operator syntax mistake to sneak in.

2. Interactive Demo: Range Size Checker

SUMIFS requires every range argument to be exactly the same size — check whether your ranges qualify.

Change Criteria range 2 to "B2:B100" to match the others and see the error resolve.

3. Cause 1: Mismatched Range Sizes

This is the single most common cause of #VALUE! in SUMIFS/COUNTIFS. Every range argument — the sum range and every criteria range — must contain the exact same number of rows and columns:

=SUMIFS(C2:C100, A2:A100, "East", B2:B50, ">100")     → #VALUE! (B range is only 49 rows vs 99 for the others)
=SUMIFS(C2:C100, A2:A100, "East", B2:B100, ">100")     → Correct (all ranges are 99 rows)

This mistake commonly happens when a range is extended for one criteria (to accommodate new rows) but the sum range or another criteria range isn't updated to match.

4. Cause 2: Comparison Operator Syntax Mistakes

Comparison operators (>, <, >=, <>) must be included inside the same quoted string as the comparison value — a very common mistake is placing the operator outside the quotes:

=SUMIFS(C2:C100, B2:B100, >"100")     → #VALUE! (operator outside quotes)
=SUMIFS(C2:C100, B2:B100, ">100")      → Correct (operator inside the quoted string)

When the comparison value comes from a cell reference rather than being typed literally, concatenate the operator and the cell reference using &:

=SUMIFS(C2:C100, B2:B100, ">"&D1)

5. Cause 3: Closed External Workbook References

Unlike a simple SUM formula, which can sometimes retrieve cached values from a closed external workbook, SUMIFS and COUNTIFS require the referenced source workbook to be open at calculation time. If any range argument points to a closed external file, the function returns #VALUE! rather than attempting to use potentially outdated cached data.

6. Cause 4: Wildcard Character Mistakes

SUMIFS and COUNTIFS support wildcards (* for any number of characters, ? for a single character) within text criteria, but if your actual data contains a literal asterisk or question mark character that you don't intend as a wildcard, you'll need to escape it with a tilde:

=COUNTIFS(A2:A100, "Model*")           — matches any text starting with "Model"
=COUNTIFS(A2:A100, "Model~*99")        — matches the literal text "Model*99", treating * as a real character

7. Building Dynamic Criteria From Cell References

When building criteria dynamically from other cells rather than typing literal values, remember that comparison operators still need to be concatenated as text using &, and text-based criteria referencing a cell generally don't need quotes around the cell reference itself, only around any literal operator prefix:

=SUMIFS(C2:C100, A2:A100, D1, B2:B100, ">"&E1)

Here D1 might contain the literal text "East" (no quotes needed since it's a direct cell reference, not typed text), while E1 might contain a number like 100, concatenated with the ">" operator.

8. Google Sheets Equivalent (SUMIFS Works Identically)

Google Sheets implements SUMIFS and COUNTIFS with essentially identical syntax and the same range-size requirement, so every fix above applies directly without modification. The one practical difference worth knowing: Sheets' locale settings (covered in our #PARSE! guide) can affect whether commas or semicolons separate the function's arguments, though the internal logic of range matching and operator syntax remains the same either way.

9. Frequently Asked Questions

Why does SUMIFS return #VALUE!?

The most common cause is that one of the criteria ranges is a different size than the sum range, since SUMIFS requires every range argument to have identical dimensions. Other causes include referencing an entire closed external workbook, or a criteria string with incorrect comparison operator syntax.

How do I write a greater-than criteria correctly in SUMIFS?

Comparison operators must be included inside the same quoted text string as the value, such as ">100", not as a separate argument or written as >"100". Writing the operator outside the quotes is a common cause of #VALUE! in SUMIFS and COUNTIFS.

Can SUMIFS handle criteria from a closed external workbook?

No, SUMIFS and COUNTIFS cannot evaluate ranges from a closed external workbook and will return #VALUE! if attempted. The source workbook must be open at the time of calculation for these functions to work correctly.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.