Cross-sheet COUNTIFS Formula Not Working as Intended

I am using a helper sheet to calculate projects that are Past Due, Due this Week, and Pending Review per Requestor Email (this email comes via a form).

The formula I am having issues with is the the one I am trying to create to count the number of projects that are due this week. I need to formula to count the rows that match the requestor email, the task is "Project Complete," and the due date is in the next 7 days.

The gist of the formula I have written is: =COUNTIFS({source sheet requestor email}, Email@row, {source sheet task column}, "Project Complete", {source sheet task due column}, < =TODAY(7), {source sheet task due column}, >= Today())

However, this is not bringing back accurate data. See screenshots below:

Helper sheet overview:

Screenshot of the formula:

Brochure Source Sheet:

Can someone please advise what may be going wrong with my formula? Thank you in advance

Find more posts tagged with

Sheets

Formulas

Support

Solution Center

Accepted answers

Melissa Yamada

Hello @kelceyg ,

If you're using the "Task Due" column on your source sheet to determine the "next 7 days" criteria, try using the AND function instead.

Try this:

=COUNTIFS({source sheet requestor email}, Email@row, {source sheet task column}, "Project Complete", {source sheet task due column}, AND(@cell< =TODAY(7), @cell >= Today()))

All comments

Melissa Yamada

Hello @kelceyg ,

If you're using the "Task Due" column on your source sheet to determine the "next 7 days" criteria, try using the AND function instead.

Try this:

=COUNTIFS({source sheet requestor email}, Email@row, {source sheet task column}, "Project Complete", {source sheet task due column}, AND(@cell< =TODAY(7), @cell >= Today()))

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.