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.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!