Does SUMIF not work if criteria is a text result of a formula?


I have dates in the Feature Added column, 02/15/2022, 02/05/2022 etc.

I use a formula in my Feature Month column to convert those dates to mmdddd

=IFERROR(RIGHT("00" + MONTH([Feature Added]@row), 2) + "" + YEAR([Feature Added]@row), "")

So my Feature Month column contains 022022, 022022 etc

I have an Effort column. It contains numbers 1 through 9.

So now I want a Total Effort Month, to add up the effort each month, and this formula does not work:

=SUMIF([Feature Month]:[Feature Month], [Feature Month]@row, Effort:Effort)

But if I change my Feature Month column to:

=VALUE(IFERROR(RIGHT("00" + MONTH([Feature Added]@row), 2) + "" + YEAR([Feature Added]@row), ""))

The SUMIF works, but my numbers are no longer 022022, they become 22022 because VALUE strips the leading zero.

I do not want to use a helper column for the SUMIF criteria source, but I will if I have to.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!