COUNTIFS formula with date fields is giving me trouble

Options
Mary Ayers
Mary Ayers ✭✭✭✭
edited 08/01/21 in Formulas and Functions

Hello, Formula Gurus,

Every other COUNTIFS formula I have on my sheet is fine. Since this is the only formula referencing date fields, I suspect that is where the problem is.

Formula:

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

Please help! 😐️

Mary

Best Answer

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Options

    Syntactically I see incorrect bracket used after MedschedulerCreated range, should be } instead of ). There is also no need of = sign in the first criteria when comparing the text value as it is assumed that you are comparing the value equal to the text specified.

    Hopefully below should work,

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

    Thank you, @SK . I have fixed both of those errors but am still receiving an #INVALID REF error. Here is my new formula:

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

    Any thoughts?

    Best,

    Mary

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Answer ✓
    Options

    #INVALID REF happens when the name of your referenced sheet/range is invalid. Please make sure those references are valid.

  • Mary Ayers
    Mary Ayers ✭✭✭✭
    Options

    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 -- and here I see you had the answer as well. Thank you for all the assistance in trying to solve this, @SK -Mary

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Options

    Glad it has been resolved in the dream as well as in reality :-)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!