# SUMIF sometimes not summing when criteria is met

SUMIF is not summing some of the cells despite the logic being met. For example, cell B9 on sheet 3 should result in 25, but it is coming back with 0.

I've tried `sumif(Sheet2!\$B26:\$O26, "*MW*", Sheet2!\$B9:\$O9)` `sumif(Sheet2!\$B26:\$O26, "*"&B\$1&"*", Sheet2!\$B9:\$O9)` and `sumif(Sheet2!\$B26:\$O26, "*"&B\$1&"*", Sheet2!\$B9:\$O9)+sumif(Sheet2!\$B26:\$O26, "B\$1", Sheet2!\$B9:\$O9)` and nothing is giving me the proper results.

Ideally I would like this to work with Sheet 1 formatted the way that it is.

Your SUMIF function is working properly. The problem is that there is no number in `Sheet2!B9` to SUM because `"25"` is not the number `25`

### Your formula in `Sheet2!B9`

``````# where Sheet1!B10="25 MW"

=IF(ISNUMBER(Sheet1!B10),
Sheet1!B10,
REGEXREPLACE(Sheet1!B10,
"[^\d]+", "")
)

="25"    # ISNUMBER("25")=FALSE
``````

If you want to coerce a number that is a text string to a number, you can attempt a mathematical calculation that doesn't change the number. e.g. divide by 1

``````# where Sheet1!B10="25 MW"

=IF(ISNUMBER(Sheet1!B10),
Sheet1!B10,
REGEXREPLACE(Sheet1!B10,
"[^\d]+", "")/1
)

=25    # ISNUMBER(25)=TRUE
``````