Fix the #VALUE! Error in Google Sheets SUMIF/COUNTIF

TL;DR: The 10-Second Fix

The #VALUE! error usually means Google Sheets thinks your numbers are actually Text. You can't do math on words, so the formula breaks. Try these quick fixes:

• The Format Fix: Highlight your numbers and go to Format > Number > Number.
• The Math Hack: Wrap your range in VALUE() using SUMPRODUCT: =SUMPRODUCT((A2:A10="Criteria")*VALUE(B2:B10))
• The "Multiply by 1" Trick: In a new column, use =C2*1. This forces Sheets to recognize the value as a number.

The Exact Error Message

Hover over the red corner of your cell. You will likely see this specific warning:

#VALUE! (Function SUMIF parameter 3 expects number values. But '...' is a text and cannot be coerced to a number.)

Why Is This Happening?

Google Sheets usually does a great job of guessing your data types. However, hidden formatting can break this logic. If your sum_range contains values that look like numbers but are stored as strings, SUMIF will give up.

Here are the most common culprits:

• Messy Imports: Data from CSVs or accounting software often includes "ghost" apostrophes (e.g., '500) that keep numbers as text.
• Web Scrapes: Copying data from a website often brings along invisible characters like   (non-breaking spaces).
• Manual Overrides: A teammate might have set the column to "Plain Text" before entering data.
• Comma vs. Period: If your sheet is set to a European locale, 1,234.56 might be read as text instead of 1.234,56.

How to Fix It

Method 1: Use SUMPRODUCT for On-the-Fly Conversion

If you don't want to mess with your source data, use SUMPRODUCT. Unlike SUMIF, it handles array operations more flexibly. It can convert text to numbers while it calculates.

=SUMPRODUCT((A2:A100="Sales")*VALUE(B2:B100))

This formula checks for "Sales" in column A, converts column B into real numbers using VALUE, and then adds them up.

Method 2: Scrubbing Hidden Spaces

Sometimes a number isn't a number because it has a trailing space (e.g., "100 "). You can clean the entire range at once using TRIM and CLEAN.

=ARRAYFORMULA(SUMIF(A2:A100, "Completed", VALUE(TRIM(B2:B100))))

Method 3: The "Paste Special" Bulk Fix

This is the fastest way to fix thousands of rows without adding new formulas. It forces Google Sheets to re-evaluate every cell.

• Type the number 1 into any empty cell and copy it (Ctrl+C).
• Highlight the column of "fake" numbers causing the error.
• Right-click and choose Paste special > Paste values only.
• Right-click again and choose Paste special > Multiply.

Google Sheets multiplies every cell by 1. Since you can't multiply text, Sheets is forced to convert the content into a numeric format.

Method 4: Align Your Ranges

Double-check that your criteria range and your sum range are identical in size. If your criteria is A2:A50 but your sum range is B2:B40, the formula will often fail or return incorrect results. Ensure both ranges start and end on the same row numbers.

How to Verify the Fix

Not sure if it worked? Look at the alignment. By default, Google Sheets aligns numbers to the right and text to the left. If your numbers are hugging the left side of the cell, they are still text.

You can also run a quick test in a blank cell:

=ISNUMBER(B2)

If this returns FALSE, you still have a formatting issue to solve.

Further Reading

• Google Sheets Support: SUMIF Function Guide
• Mastering VALUE Function for data cleaning
• Advanced: Using REGEXREPLACE to strip currency symbols from numbers