Help with a Simple Formula

I am trying to calculate a formula that I can use in a dashboard graph. I need to show the following:

status of a task (i.e. completed, in progress, etc)
That it is US only (management responsibility)
The end date is within the year 2026

This is what has been generated for me, through SS AI -

COUNTIFS({My Sheet Name here (Aug CDT - Oct 2026) Sub Range 1}, "completed", {My Sheet Name here (Aug CDT - Oct 2026) Sub Range 3}, ">= " + DATE(2026, 1, 1), {My Sheet Name here (Aug CDT - Oct 2026) Sub Range 3}, "<= " + DATE(2026, 12, 31), {My Sheet Name here (Aug CDT - Oct 2026) Sub Range 4}, "market")

I am continuing to get INVALID REF.

Can someone help? I've checked all the references and they match.

Thank you!

Find more posts tagged with

Formulas

Accepted answers

All comments

heyjay

Can you try this one?

=COUNTIFS(

{My Sheet Name here (Aug CDT - Oct 2026) Sub Range 1}, "completed",

{My Sheet Name here (Aug CDT - Oct 2026) Sub Range 3}, >= DATE(2026, 1, 1),

{My Sheet Name here (Aug CDT - Oct 2026) Sub Range 3}, <= DATE(2026, 12, 31),

{My Sheet Name here (Aug CDT - Oct 2026) Sub Range 4}, "market")

Paul Newcome

That particular error comes from not properly setting up your cross sheet references.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Having said that, once you set them up properly, you can use this simplified formula:

=COUNTIFS({My Sheet Name here (Aug CDT - Oct 2026) Sub Range 1}, "completed", {My Sheet Name here (Aug CDT - Oct 2026) Sub Range 3}, IFERROR(YEAR(@cell), 0) = 2026, {My Sheet Name here (Aug CDT - Oct 2026) Sub Range 4}, "market")

Gab_AF125

Thank you Paul, but I don't know what the problem is within the cross reference sheet? I unfortunately inherited it. Is there guidance on what to look for?

Gab_AF125

Thank you, it still doesn't work.

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.