SUMIFS not recognizing criterion when it is a formula

Hannah H

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?

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.

Thanks,

Hannah

Paul Newcome

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.

Paul Newcome

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.

Hannah H

Perfect! @Paul Newcome that did the trick.

Thank you,

Hannah

Paul Newcome

Happy to help. 👍️