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)



  • Paul Newcome
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!