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

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!

Find more posts tagged with

Accepted answers

Ayelet Weiner

I think I figured it out. Try this.

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

All comments

Ayelet Weiner

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

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

I think I figured it out. Try this.

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

Graham Rimell

Yes! That did it. Many thanks!

Ayelet Weiner

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

You're very welcome!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.