SUMIFS not recognizing criterion when it is a formula

✭✭✭✭✭

Hi,

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?

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.

Thanks,

Hannah

Tags:

• ✭✭✭✭✭✭

The problem is that when you use the RIGHT function it is converting it to a text value. Wrap it in a VALUE function to convert it back into a number and see if that works for you.

• ✭✭✭✭✭✭

The problem is that when you use the RIGHT function it is converting it to a text value. Wrap it in a VALUE function to convert it back into a number and see if that works for you.

• ✭✭✭✭✭

Perfect! @Paul Newcome that did the trick.

Thank you,

Hannah

• ✭✭✭✭✭✭

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!