SUMIFS not recognizing criterion when it is a formula


I have written a few simple SUMIFS that use the current month or current year as the criterion. However when I combine the RIGHT function in the cell with the criterion, the formula returns $0 (it should return $200).

If I manually type 21 and set this as the criterion, the SUMIFS works. If I use the same formula and adjust it to look at the current month, the SUMIFS works. I believe the inclusion of the RIGHT function is the problem but I don't know why or how to fix it. Or maybe it's a bug?

Please see attached image.

Formulas used:

Today's Date =TODAY()

Current Month =MONTH([Today's Date]@row)

Current Year =RIGHT([Today's Date]@row, 2)

Rolling Sum =SUMIFS([Nov 2020]@row:[May 2021]@row, $[Nov 2020]$3:$[May 2021]$3, <$[Current Year]$1)

Note: I'm not able to use the date format, so I added a row to label my months as 1, 2, 3, 4, etc. and a row for year labels as 20, 21.




Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!