COUNTIFS with date parameters

Mary Ayers

Hello. I'm stumped with what should be a simple COUNTIFS formula. I need a count of records that meet all of this criteria in my "Medscheduler" Smartsheet:

1) AcademicYear = 2021-22 August 23, 2021-August 21, 2022

2) Date <= 03/27/22

3) Created <= 07/16/21

My formula is returning an #INVALID OPERATION error message:

=COUNTIFS({MedschedulerAcademicYear} = "2021-22 August 23, 2021-August 21, 2022", {MedschedulerDate} <= DATE(2022, 3, 27), {MedschedulerCreated} <= DATE(2021, 7, 16))

Any help would be greatly appreciated!

Mary

Paul Newcome

@Mary Ayers Did you try adding those commas to your formula?

Bassam Khalil

Hi @Mary Ayers 

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Paul Newcome

Try this. It looks like you are just missing some commas in between your ranges and criteria.

=COUNTIFS({MedschedulerAcademicYear}, = "2021-22 August 23, 2021-August 21, 2022", {MedschedulerDate}, <= DATE(2022, 3, 27), {MedschedulerCreated}, <= DATE(2021, 7, 16))

Mary Ayers

Screen Shot 2021-07-26 at 7.46.01 AM.png

Hi @Bassam Khalil , I have attached a screenshot. Thanks!

Mary

Paul Newcome

@Mary Ayers Did you try adding those commas to your formula?

Mary Ayers

@Paul Newcome Yes, I did. That did not solve the problem. Thanks!

Paul Newcome

That should be working. Hmm... Let's give this a shot...

=COUNTIFS({MedschedulerAcademicYear}, @cell = "2021-22 August 23, 2021-August 21, 2022", {MedschedulerDate}, @cell <= DATE(2022, 3, 27), {MedschedulerCreated}, DATEONLY(@cell) <= DATE(2021, 7, 16))

It may also have to do with the commas inside of your "specific text" string although I have tested that specific portion with no issue.

Do you have any blanks in your {MedschedulerDate} range? Is that error present in any of the three ranges?

Mary Ayers

Ugh... It had been two years since I wrote the original formulas and I had forgotten that I'd need to create a Sheet Reference for the new date fields I was trying to search on. It came to me in my sleep last night. Thank you for all the assistance in trying to solve this, @Paul Newcome and @Bassam Khalil. -Mary

Paul Newcome

Happy to help. 👍️

Bassam Khalil

@Mary Ayers

You are welcome, and happy dreams it's help :)