Last 7 days with multiple conditions

I have an external sheet named Sheet 1. I want to look at Column 1 to see if it is in the last 7 days and then look at Column 2 to see if it has "Open" in the cell. If it meets both conditions, I want it to count it.

The 1st part of the formula works by itself:

=COUNTIFS({Sheet Range 1}, AND (@cell >= TODAY(-7), @cell <= TODAY()))

I am sure this part of the formula is what is wrong:

AND({Sheet 1 Range 2}, "Open", @cell))

Here is the entire formula:

=COUNTIFS({Sheet Range 1}, AND (@cell >= TODAY(-7), @cell <= TODAY()) AND({Sheet 1 Range 2}, "Open", @cell))

Thanks in advance.

Find more posts tagged with

Accepted answers

Paul Newcome

Sorry about that. I missed the extra space after the AND function.

=COUNTIFS({[Kevin] Service Support KPI - Q2 Range 1}, AND(@cell >= TODAY(-7), @cell <= TODAY()), {[Kevin] Service Support KPI - Q2 Range 2}, @cell = "Open")

All comments

Paul Newcome

Try this...

=COUNTIFS({Sheet Range 1}, AND (@cell >= TODAY(-7), @cell <= TODAY()), {Sheet 1 Range 2}, @cell = "Open")

Justin Mauzy

I tried that and it didn't work, it is showing #UNPARSEABLE.

Paul Newcome

Can you copy/paste directly from the sheet to here the exact formula that is giving the error message?

Justin Mauzy

Here is the exact formula:

=COUNTIFS({[Kevin] Service Support KPI - Q2 Range 1}, AND (@cell >= TODAY(-7), @cell <= TODAY()), {[Kevin] Service Support KPI - Q2 Range 2}, @cell = "Open")

Thank you,

Paul Newcome

Sorry about that. I missed the extra space after the AND function.

=COUNTIFS({[Kevin] Service Support KPI - Q2 Range 1}, AND(@cell >= TODAY(-7), @cell <= TODAY()), {[Kevin] Service Support KPI - Q2 Range 2}, @cell = "Open")

Justin Mauzy

It worked. Thank you for the quick response!

Paul Newcome

Happy to help. 👍️

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.