MONTH formula when referencing another sheet doesn't like blanks.

Graham Rimell · 01/04/21

I've got this formula:

=COUNTIFS({Improve system Range 2}, MONTH(@cell) = 1)

It counts how many dates in a specific month (January in this instance). However if I select the entire column, it errors (#INVALID DATA TYPE) as it can't handle blank cells.

Smartsheet by default puts a number of blank cells at the bottom of the grid, so there's no getting away from having blank cells.

Anyone know how I'd modify this formula to make it work so It can deal with blanks? I'd assume I'd need some kind of 'Ignore if <0' but I don't quite know how to do this.

Thanks!

Ayelet Weiner · 01/04/21

I think I figured it out. Try this.

=COUNTIF({Improve system Range 2}, IFERROR(MONTH(@cell), 0) = 1)

Ayelet Weiner · 01/04/21

Hi Graham,

Try below.

=COUNTIF({Improve system Range 2}, IFERROR(MONTH(@cell) = 1, " "))

When you have a chance, take a look at how the IFERROR function works. https://help.smartsheet.com/function/iferror

Graham Rimell · 01/04/21

Unfortunately that doesn't work either, still get #INVALID DATA TYPE. I recall trying IFERROR before when I last tried and couldn't crack it for this formula.

Ayelet Weiner · 01/04/21

I think I figured it out. Try this.

=COUNTIF({Improve system Range 2}, IFERROR(MONTH(@cell), 0) = 1)

Graham Rimell · 01/04/21

Yes! That did it. Many thanks!

Ayelet Weiner · 01/04/21

https://community.smartsheet.com/discussion/comment/273666#Comment_273666

You're very welcome!

MONTH formula when referencing another sheet doesn't like blanks.

Best Answer

Answers

Help Article Resources

Categories