What's wong with this formula?

=COUNTIFS({Master Project List Range 1}, "2 - Construction", {Master Project List Range 2}, <= EOMONTH [Month]@row , 0, {Master Project List Range 3}, >= [Month]@row )

I can't understand why I'm still getting an error message that this formula is unparseable.

I'm trying to create a table that counts how many projects are in progress each month over the course of the next 5 years (we have larger construction projects). I'm referencing the phase row to grab the count of projects marked as 2 - Construction, and them I'm referencing the start and finish date columns.

Does anyone have any advice?

Find more posts tagged with

Accepted answers

All comments

nicksnav

@Hillary

Smartsheet does not yet have an EOMONTH function, but you should be able to directly reference month rows and compare. Also, are you able to remove the ", 0, " character? Is this trying to find an exact match?

If Month@row is being referenced twice the formula below worked for me.

=COUNTIFS({Master Project List Range 1}, "2 - Construction", {Master Project List Range 2}, <=Month@row , {Master Project List Range 3}, >=Month@row )

You could simplifiy the cross sheet reference with a staus column based on the start and end date columns on the source sheet. That way you'd only have: =COUNTIF({Master Project List Range 1}, "2 - Construction", {Status}, "In progress"). This would keep the dates out of our cross reference also but isn't needed. Let me know if the formula above works for you.

Hillary

@nicksnav your correction to my formula was the key. I cannot thank you enough for helping get this done. This was the last piece of the puzzle to finish our construction master project schedule dashboard.

nicksnav

@Hillary Happy to hear! Congrats!🤩

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.