Last 7 days with multiple conditions

Justin Mauzy

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.

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

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. 👍️