Countifs between 2 dates with status

Within Sheet Summary I can't get this formula to work, it keeps saying invalid value. Any help would be greatly appreciated.

I am attempting to create a dashboard for items I have being completed on time or not using "0" or "1" through other formulas within the sheet. For example, looking for the number of CPAs completed on time in August.

=COUNTIFS([Legal CPA % OT]:[Legal CPA % OT], "1", [Start Date from Roster]:[Start Date from Roster], >=DATE(2025, 8, 1), [Start Date from Roster]:[Start Date from Roster], <=DATE(2025, 8, 31))

Larger question: Is there a better way to be doing this when pulling data over to the dashboard? Currently I would need to do a new one of these every month… and there are multiple items besides CPAs.

Thanks!

Find more posts tagged with

Dashboards

Sheet Summary

Countif

dates

dashboard

Accepted answers

All comments

Braden68

@Paul Newcome are you able to assist? I see your answers all over and have been helpful getting to this point!

Andrée Starå

https://community.smartsheet.com/discussion/141390/countifs-between-2-dates-with-status

Hi,

I hope you're well and safe!

Try something like this.

=COUNTIFS([Legal CPA % OT]:[Legal CPA % OT], 1, [Start Date from Roster]:[Start Date from Roster], >=DATE(2025, 8, 1), [Start Date from Roster]:[Start Date from Roster], <=DATE(2025, 8, 31))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

Braden68

@Andrée Starå that is exactly what I am currently using and is in original post

Andrée Starå

https://community.smartsheet.com/discussion/comment/491072#Comment_491072

It's not the same. I've removed the " " around the 1, which was what stood out to me.

Make sense? Did it work?

✅ Remember! Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

Paul Newcome

I would suggest moving to a separate sheet. In this sheet, I would put a Date type column called "Dates" and then manually enter the first of each month for as long as you want to track.

Then in a text/number column you could use

=COUNTIFS({Source Sheet % Column}, @cell = 1, {Source Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH(Dates@row), IFERROR(YEAR(@cell), 0) = YEAR(Dates@row)))

If you want to flag the "current" month based on today's date, first insert another date type column called "TODAY" then set up an automation triggered daily with a record a date action to record "today's date" every morning at 12:00am in the "TODAY" column.

Then in a checkbox column called "Current Month", you would use

=IF(AND(MONTH(Dates@row) = MONTH(TODAY()), YEAR(Dates@row) = YEAR(TODAY())), 1)

And finally create a "Current Month" report to pull from this metrics sheet filtered to only show the row where "Current Month" is checked.

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.