0

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.

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.