# COUNTIFS with date parameters

✭✭✭✭

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

• ✭✭✭✭✭✭

• ✭✭✭✭✭✭

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.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭

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))

• ✭✭✭✭

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

Mary

• ✭✭✭✭✭✭

• ✭✭✭✭

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

• ✭✭✭✭✭✭

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?

• ✭✭✭✭

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

• ✭✭✭✭✭✭

Happy to help. 👍️

• ✭✭✭✭✭✭

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

bassam.khalil2009@gmail.com