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.
Answers
-
Try wrapping everything in VALUE within the formula:
=SUMIF([Feature Month]:[Feature Month], VALUE(@cell) = VALUE([Feature Month]@row), Effort:Effort)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 150 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!