COUNTIFS the referenced sheet have the value that is equal or less than a cell in the sheet

Hello, I'm trying to make a formula that counts from year to date, but I just want it to count from the month the sheet is refering that is in a cell. I tried this, but didn't work.

COUNTIFS({Referenced Sheet 1}, "", {Referenced Sheet 2}, ="Element", {Referenced sheet 3}, <=@cell)

Thanks

Find more posts tagged with

Accepted answers

All comments

J Tech

Hi @EmilyEchevarria,

It looks like you are trying to use the <= operator with a cell reference to dynamically set the end date for your COUNTIFS formula. To reference the month of the sheet, you can use the MONTH function to extract the month number from the cell reference and then construct a new date using the DATE function with the current year and the extracted month.

Here's an example formula that should work:

=COUNTIFS({Referenced Sheet 1}, "", {Referenced Sheet 2}, "Element", {Referenced sheet 3}, ">=" & DATE(YEAR(TODAY()), MONTH(@cell), 1), {Referenced sheet 3}, "<=" & DATE(YEAR(TODAY()), MONTH(@cell) + 1, 1) - 1)

In this formula, we're using the TODAY() function to get the current date and the YEAR and MONTH functions to extract the year and month from the cell reference. We're then using the DATE function to construct the start and end dates for our COUNTIFS formula, and concatenating them with the operators >= and <=, respectively.

Make sure to replace the cell reference @cell with the cell reference to the cell containing the month you want to count from.

Andrée Starå

Hi @J Tech

I hope you're well and safe!

That formula won't work because it's for Excel and not Smartsheet. For example, you can't use the & in a Smartsheet formula like yours.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

J Tech

Please try

=COUNTIFS({Referenced Sheet 1}, "", {Referenced Sheet 2}, "Element", {Referenced sheet 3}, >=DATE(YEAR(TODAY()), MONTH(@cell), 1), {Referenced sheet 3}, <=DATE(YEAR(TODAY()), MONTH(@cell) + 1, 1) - 1)

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.