Task is open and overdue

Pamela Wagner

I know that I'm super close on my formula but it's returning an Incorrect Argument. I have a list of contracts and when they are expiring. I want to know how many: are over due; coming due in 30 days; coming due in 60 days. I am having it look in the "Completed" column and if it is not checked to then go to the "Completion Date" column and let me know those that have a date equal or less than today's date. Starting on the overdue one, I have the following:

=COUNTIF((Completed1:Completed9), =0, AND([Completion Date]1:[Completion Date]9, <=TODAY()))

This is returning the Incorrect Argument so I think that it's just down to a bracket missing somewhere but I've tried all different combinations.

Paul Newcome

My apologies. I meant to change it to a COUNTIFS with the S on the end to allow for multiple sets.

=COUNTIFS(Completed1:Completed9, 0, [Completion Date]1:[Completion Date]9, <=TODAY())

When using the COUNTIFS function, you go range, criteria, range, criteria, etc......

It assumes an AND so that all conditions must be met before it will count.

The reason for the brackets is the column names themselves.

Column names that have a space, number, or special character in them require square brackets whereas column names that do not have any of those three things do not.

Paul Newcome

Try this...

=COUNTIF(Completed1:Completed9, 0, [Completion Date]1:[Completion Date]9, <=TODAY())

Pamela Wagner

Unfortunately it's still saying Incorrect Argument. Why would you not have an AND in there? Shouldn't the argument be IF there is no flag in the Completed column AND the date is before today, count the number?

Also, I'm curious as to why there are no [ ] around Completed but it wants one for Completion Date. Is this due to one word vs a phrase? I know it works without the [ ] but I wonder why.

Paul Newcome

My apologies. I meant to change it to a COUNTIFS with the S on the end to allow for multiple sets.

=COUNTIFS(Completed1:Completed9, 0, [Completion Date]1:[Completion Date]9, <=TODAY())

When using the COUNTIFS function, you go range, criteria, range, criteria, etc......

It assumes an AND so that all conditions must be met before it will count.

The reason for the brackets is the column names themselves.

Column names that have a space, number, or special character in them require square brackets whereas column names that do not have any of those three things do not.

Pamela Wagner

Yes, it works! When I first tried to do it the full string didn't work so I cut it into chucks to see if the individual logic worked and then forgot to change it back to IFS.

Paul Newcome

Happy to help! 👍️