How do I set up "TODAY" and (-365) days and a NOT completed function for a metric sheet?

Robert Harrison

I am trying to set up a metric sheet to be used in a dashboard that I am creating. I want to show a count of all task that are not marked as completed with a due date of current day and back, up to 365 days, but I am receiving an #INCORRECT ARGUMENT error message.

The function command I am using is:

=COUNTIF((NOT({1. CA-374 Checklist Daily Review Range 2}, ="Completed")), AND({1. CA-374 Checklist Daily Review Range 1}, >=TODAY(0), {1. CA-374 Checklist Daily Review Range 1}, <=TODAY(-365)))

Below is the sheet I want to pull the information from.

David Joyeuse

Hi @Robert Harrison

Incorrect argument means your formula is incorrectly written. Your COUNTIF function lacks the range you want to return, and the AND function isn't properly set as well, as you cannot separate ranges and criteria in each expression.

Last, as you're using 2 different ranges, COUNTIFS is better suited here.

There we go:

=COUNTIFS({1. CA-374 Checklist Daily Review Range 2}, <>"Completed", {1. CA-374 Checklist Daily Review Range 1}, AND(@cell <=TODAY(), @cell>=TODAY(-365)))

Hope it helped!

Robert Harrison

That worked like a charm. THANK YOU!!!!!!

David Joyeuse

Hi @Robert Harrison

Incorrect argument means your formula is incorrectly written. Your COUNTIF function lacks the range you want to return, and the AND function isn't properly set as well, as you cannot separate ranges and criteria in each expression.

Last, as you're using 2 different ranges, COUNTIFS is better suited here.

There we go:

=COUNTIFS({1. CA-374 Checklist Daily Review Range 2}, <>"Completed", {1. CA-374 Checklist Daily Review Range 1}, AND(@cell <=TODAY(), @cell>=TODAY(-365)))

Hope it helped!

Robert Harrison

That worked like a charm. THANK YOU!!!!!!