Last 7 Days

I need a formula that counts the number of requests that have been submitted within the last seven days if assigned to "John Doe".

Smartsheet's AI generated this formula, however it comes back to me as #UNPARSEABLE.

=COUNTIFS({Filing Submitted Date}:{Filing Submitted Date}, {Filing Submitted Date}@cell>=TODAY(-7), {LRT Assigned To:}:{LRT Assigned To:}, "Drake Detwiler")

Thoughts?

Find more posts tagged with

Formulas

Countif

Accepted answers

KPH

This part in bold doesn't look right.

=COUNTIFS({Filing Submitted Date}:{Filing Submitted Date}, {Filing Submitted Date}@cell>=TODAY(-7), {LRT Assigned To:}:{LRT Assigned To:}, "Drake Detwiler")

Can you try

=COUNTIFS({Filing Submitted Date}:{Filing Submitted Date}, >=TODAY(-7), {LRT Assigned To:}:{LRT Assigned To:}, "Drake Detwiler")

If you really want John Doe, you also need to put that in place of Drake Detwiler

KPH

Sorry, I wasn't paying enough attention to the brackets. The column references are also incorrect. You can either reference a column in another sheet using {reference name} or reference a column in the current sheet using [column name]:[column name]. You cannot use {something}:{something}.

So your formula will be either

=COUNTIFS({Filing Submitted Date}, >=TODAY(-7), {LRT Assigned To:}, "Drake Detwiler")

If it is in different sheet to the data

=COUNTIFS([Filing Submitted Date]:[Filing Submitted Date], >=TODAY(-7), [LRT Assigned To:]:[LRT Assigned To:], "Drake Detwiler")

If it is in the same sheet as the data

All comments

KPH

This part in bold doesn't look right.

=COUNTIFS({Filing Submitted Date}:{Filing Submitted Date}, {Filing Submitted Date}@cell>=TODAY(-7), {LRT Assigned To:}:{LRT Assigned To:}, "Drake Detwiler")

Can you try

=COUNTIFS({Filing Submitted Date}:{Filing Submitted Date}, >=TODAY(-7), {LRT Assigned To:}:{LRT Assigned To:}, "Drake Detwiler")

If you really want John Doe, you also need to put that in place of Drake Detwiler

Drake Detwiler

Looks like that came back as #UNPARSEABLE too, unfortunately. 😔

Also, the name doesn't have to be John Doe. That was just used as an example.

KPH

So your formula will be either

=COUNTIFS({Filing Submitted Date}, >=TODAY(-7), {LRT Assigned To:}, "Drake Detwiler")

If it is in different sheet to the data

=COUNTIFS([Filing Submitted Date]:[Filing Submitted Date], >=TODAY(-7), [LRT Assigned To:]:[LRT Assigned To:], "Drake Detwiler")

If it is in the same sheet as the data

Drake Detwiler

The first one worked! Thank you!

KPH

Wonderful! Thanks for letting me know.

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.