Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    COUNTIFS formula with date fields is giving me trouble

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

    image.png

    Please help! 😐️

    Mary

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    Answers

    • ✭✭✭✭✭✭

      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))
      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      Answer ✓

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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions