How to create a conditional formula with date specifications?

Adrienne Buesa

How do I create a formula that counts the number of rows containing [STATUS] “Green” and have [DATE] = Today and [DATE] = Last Seven Days from Today. I do not want to capture rows that are older than seven days from today.

Paul Newcome

Try something along the lines of...

=COUNTIFS(Status:Status, "Green", Date:Date, AND(@cell <= TODAY(), @cell >= TODAY(-7)))

Adrienne Buesa

I tried the formula but got error #UNPARSEABLE:

=COUNTIFS([RYG Status]:[RYG Status],"Green",[Date]:[Date],AND([Date]<= TODAY(), [Date]>= TODAY(-7)))

I tried another version of the formlula (changed [Date] to [Date]:[Date], but then got error #INVALID OPERATION:

=COUNTIFS([RYG Status]:[RYG Status], "Green", [Date]:[Date], AND([Date]:[Date] <= TODAY(), [Date]:[Date]>=TODAY(-7)))

Any advice?

Paul Newcome

In the first formula... Inside of the AND function, you should be using "@cell" exactly as I have in my previous comment. Not [Date].

Here is the formula I provided updated to reflect the column names you listed above.

=COUNTIFS([RYG Status]:[RYG Status], "Green", Date:Date, AND(@cell <= TODAY(), @cell >= TODAY(-7)))

Try using that exactly as it is.

Adrienne Buesa

Great thanks that solved it!

Paul Newcome

Happy to help. 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.