My SUMIF is giving the wrong total — numbers stored as text fix
SUMIF finding the right rows but the total comes out too low? The sum_range holds numbers stored as text, so SUMIF matches correctly and then adds zero for every text cell. Here's the fix
Why the match works but the math doesn't
SUMIF's criteria comparison and its summation step are two separate operations under the hood. The criteria comparison tolerates loose matches between text and numbers — "Chicago" matches "Chicago" whether the surrounding cells are text or not — so you can watch it correctly identify every row that should count. The summation of the sum_range column, though, behaves exactly like a plain SUM: any cell holding text instead of a real number contributes zero, with no warning that anything was skipped.
How to confirm this is what's happening
Compare =COUNTIF(range,criteria) against how many rows you'd expect to match — if that count is correct, the matching side is fine and the problem is isolated to the sum_range column. Then check whether the matched rows in sum_range are left-aligned (a sign of text) rather than right-aligned (a sign of real numbers), or select just those cells and see if the status bar's quick sum agrees with SUMIF's answer.
Fixing the sum_range column
Select the sum_range column, run Data > Text to Columns and click Finish to force a re-parse into real numbers, or use the copy-1-then-Paste-Special-Multiply trick to convert the whole column in place without touching the criteria column at all. A quick way to sanity-check before and after: wrap the range in SUMPRODUCT(--sum_range), which coerces text-numbers to real numbers for the purposes of that one calculation, and see if the total jumps to match what SUMIF should have returned.