How to record monthly changes in sheet?

So I am trying to get a formula to return a count of the number of cells that match but only if another column that records a date is within the last 30 days. I tried with reports but the issue is they don't display right if I try to send updates in any way besides a link (and I need it to run monthly, weekly, etc.) I felt like maybe having the dashboard update monthly based on this would be the best way but I can't figure it out. So I have the project sheet, a secondary metric that pulls in all the rows from the sheet and totals count of certain statuses. I tried to pull it into another sheet and use the formula in that sheet to pull only the sites that ,match the date ranges, but no matter what I do the formula doesn't work. I based it off several other threads in the community but can't seem to get it right. This is the formula I've tried but says "unparseable" will edit out specific information by replacing with asterisks:

=COUNTIFS({******* Project Schedule Range 1}, "Potential", {******* Project Schedule Range 2}, <=(TODAY()), <=(TODAY([-30])

I also tried with the greater/less than the other way in case I was messing it up but it's not working.

Find more posts tagged with

Formulas

Cell linking

Accepted answers

Jeff Reisman

@KC Jones You're almost there, just some syntax issues, and a reversed operator.

You can list the same range twice in a COUNTIFS if you need to, in order to forego the potential syntax issues of AND. I'll show you the proper syntax both ways though:

=COUNTIFS({******* Project Schedule Range 1}, "Potential", {******* Project Schedule Range 2}, <=TODAY(), {******* Project Schedule Range 2}, >= TODAY(-30))

With AND:

=COUNTIFS({******* Project Schedule Range 1}, "Potential", {******* Project Schedule Range 2}, AND(@cell <=TODAY(), @cell >= TODAY(-30)))

KC Jones

@Jeff Reisman It works, thanks!

All comments

KC Jones

Oh forgot to mention I tried with AND after the first criterion and still didn't get it.

Jeff Reisman

@KC Jones You're almost there, just some syntax issues, and a reversed operator.

You can list the same range twice in a COUNTIFS if you need to, in order to forego the potential syntax issues of AND. I'll show you the proper syntax both ways though:

=COUNTIFS({******* Project Schedule Range 1}, "Potential", {******* Project Schedule Range 2}, <=TODAY(), {******* Project Schedule Range 2}, >= TODAY(-30))

With AND:

=COUNTIFS({******* Project Schedule Range 1}, "Potential", {******* Project Schedule Range 2}, AND(@cell <=TODAY(), @cell >= TODAY(-30)))

KC Jones

@Jeff Reisman It works, thanks!

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.