SUMIF sometimes not summing when criteria is met

Question

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.

Google Sheet

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

Answer 1

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