INVALID REF Issue

I am using the following formula and having intermittent #INVALID REF issues. Every few minutes (most likely when the formula is refreshing) it will show #INVALID REF. If I open the sheet and then refresh the formula fixes itself for a few minutes before erroring out again. Any suggestions?

The first COUNTIFS formula is looking at a sheet that data is being entered into daily. The second COUNTIFS formula is looking at an archive sheet for data entered earlier in the month.

There is a total of 10 references in my sheet (5 looking at each sheet)

I am using the following formula:

=COUNTIFS({Results}, $Reference$4, {Date}, ISDATE(@cell), {Date}, YEAR(@cell) = [01]3, {Date}, MONTH(@cell) = [01]1, {Date}, DAY(@cell) = [01]2) + COUNTIFS({ARCHIVE RESULTS}, $Reference$4, {ARCHIVE DATE}, ISDATE(@cell), {ARCHIVE DATE}, YEAR(@cell) = [01]3, {ARCHIVE DATE}, MONTH(@cell) = [01]1, {ARCHIVE DATE}, DAY(@cell) = [01]2)

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

It sounds like there may be a lot going on in the back-end of the sheet which can cause intermittent issues. You may want to contact support and provide them with each of the sheet ID's to see if they can refresh it on the back end to see if that helps.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.