Cross-sheet COUNTIFS Formula Not Working as Intended

kelceyg

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

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()))

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()))