COUNTIFS for last seven days with date field and checkbox

Hi all,

I have a tracking sheet 1) with a checkbox column (named [Doc processed]) to note when a document has been completely reviewed , and 2) a second column (named [Date processed]) that gets an automated date added when that box is ticked.

I need to create a summary field that counts how many docs were completed in the last week, so the countifs should count records where 1) [Doc processed] is ticked, and 2) [Date processed] is in the last 7 days (including today).

There is an added level of complication in that the [Date processed] field will obviously be empty for some uncompleted records, and this seems to be kicking up an error. Actually, it's all just coming back unparseable and my brain is fried - I would love some help.

What I currently have (that appears to be totally wrong) is:

=COUNTIFS([Doc processed]:[Doc processed]; AND([Date processed]@cell >= TODAY(-7), [Date processed]@cell <= TODAY()))

Thanks in advance!

Find more posts tagged with

Accepted answers

Paul Newcome

It is because you have already specified the column within the range. The formula will automatically apply the criteria set to the range immediately before it.

All comments

Paul Newcome

Looks like you have a semi-colon instead of a comma after the range. You also need to get rid of "[Date Processed]@cell" and just use "@cell".

Karen Webber

Thanks Paul, but even after making those changes I get an unparseable.

"You also need to get rid of "[Date Processed]@cell" and just use "@cell"." Re this, my question is how the function relates itself to a specific column if this isn't captured somewhere, as I have more than one column in my sheet that contains a date.

Paul Newcome

It is because you have already specified the column within the range. The formula will automatically apply the criteria set to the range immediately before it.

Karen Webber

Thanks Paul, I see where I was going wrong.

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.