Help! COUNTIFS function with 3 criteria

Kate Allison

Hi! I'm having trouble getting this COUNTIFS function to work, getting the error "incorrect argument set".

What I have: =COUNTIFS([Due Date]20:[Due Date]3443, <=TODAY(-7), [Assigned To]17:[Assigned To]3440, <>"", [Task Completed On Time?]16:[Task Completed On Time?]3440, "Yes")

What I want it to do: Count rows that meet 3 criteria: Due Date is within the last 7 days, have any value in the Assigned To column, and say "yes" in my Tasks Completed On Time column.

Any idea where I'm going wrong? I have a separate, similar formula that works fine, which is: =COUNTIFS([Due Date]20:[Due Date]3443, <=TODAY(-7), [Assigned To]17:[Assigned To]3440, <>""). Basically, I'm trying to add on a 3rd criteria to this one.

Thanks for any help!

Chris Shifflett

=COUNTIFS([Due Date]20:[Due Date]3443, <=TODAY(-7), [Assigned To]17:[Assigned To]3440, <>, "", [Task Completed On Time?]16:[Task Completed On Time?]3440, "Yes")

This work?

Kate Allison

@Chris Shifflett No :( But now it says "unparsable" instead of 'incorrect argument'

Sean Morgan

Hello @Kate Allison ,

I was able to create an Example that should work. I essentially copied your original Formula, and made modifications on my Sheet to make this work.

The Formula I used was: =COUNTIFS([Due Date]1:[Due Date]7, <=TODAY(-7), [Assigned To]1:[Assigned To]7, <>"", [Task Completed On Time]1:[Task Completed On Time]7, "Yes")

Here are the results below:

If my above Formula doesn't work (Please note you'll have you change your row numbers within the Formula), please can you provide a screenshot of your Sheet, whilst hiding any sensitive data, as well as a Screenshot of the Formula error. I'd love to help with this!

Regards

Sean

Kate Allison

@Sean Morgan Thank you for trying to help! I copied your formula into my sheet and changed the row numbers so it reads as below:

=COUNTIFS([Due Date]17:[Due Date]3443, <=TODAY(-7), [Assigned To]17:[Assigned To]3443, <>"", [Task Completed On Time]17:[Task Completed On Time]3443, "Yes")

But I'm still getting the 'unparsable' error. See screenshot below, with sensitive data hidden, and I added in a couple test rows up top with the columns filled in as an example.

The row above it has a similar formula, without the Task Completed On Time portion (see below). So I'm wondering if the issue is with the Task Completed On Time row, which uses a formula to generate a 'yes' or 'no' based on a due date column and a completion date column. I say that because when I tested adding the Tasks Completed on Time portion of my first formula to this one, it didn't work either & came back with the Unparsable error too. Thoughts?

For reference, the Task Completed On Time column uses this formula:

=IF(ISBLANK([Completion Date]240), "", IF([Completion Date]240 <= [Due Date]240, "Yes", "No"))

Sean Morgan

Hello @Kate Allison,

It looks like your "Yes/No" Column is called "Task completed on Time?", where as the Formula states "Task completed on Time". It appears its missing the question mark.

Adding the question mark to the Formula, or removing it from the Column header should resolve this :D

Let me know if you have any questions

Regards

Sean

Kate Allison

@Sean Morgan Oh my goodness, I can't believe I overlooked that! That fixed it - thank you!'

Sean Morgan

@Kate Allison

Never a worry!

Regards

Sean