COUNTIFS formula with date fields is giving me trouble

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

Find more posts tagged with

Accepted answers

Sameer Karkhanis

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

All comments

Sameer Karkhanis

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

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

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

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.