COUNTIFS #INVALID REF appear after a period of time

STEPHEN ALLEN

Hello,

Able to get the countifs to total up some information in a different sheet.  But then after a period of time, some of the cells using the countifs display #INVALID REF when it worked when I created my sheet

screen shot shows a portion of my sheet,  all cells use countifs and all cells did count what I wanted it too.  What is triggering the cell to change to #INVALID REF

 

 

Paul Newcome

What is the actual formula you are using? Is it possible that something the formula is referencing has changed?

STEPHEN ALLEN

What sort of changes in the reference sheet might cause an #INVALID REF?  The EAS reference sheet has 45 rows and ~20 columns.   The reference sheet has a fairly extensive number of people that have access to the sheet.  I have Editor-cannot share.

 

My sheet with the countifs go against 9 different reference sheets, and I use the countifs in 6 columns  (so 9x6 cointifs statements)

 

Total Scopes Column/EAS Cell     

             =COUNTIFS({Bundle EAS Range 1}, "CompanyName")

Total Completed Column/EAS Cell 

=COUNTIFS({Bundle EAS Range 1}, "CompanyName", {Bundle EAS Range 2}, "Complete")

 

Paul Newcome

The invalid reference error comes from the formula referencing invalid data types such as trying to sum text fields. I would start by opening the formulas and double checking the range. When you open the range up you can look through it to make sure there are no cells containing the incorrect data type.

 

I find this page helpful in troubleshooting formulas.

 

https://help.smartsheet.com/articles/2476176-formula-error-messages?_ga=2.179990624.555919786.1553514900-1302373248.1552411124#invalidref

 

STEPHEN ALLEN

Thanks for reply Paul,

I do not believe that it has to do with data in the reference cells changing,  It is a curious thing in that rows that had the #INVALID REF now have count information and other rows that had the count data now has #INVALID REF.  So just throughout the day, count value will show up and some cells change to #INVALID REF.

For one of cells with the #INVALID REF.  I deleted  the formula   =COUNTIFS({Bundle LTE Cradlepoint Move Range 1}, "ConpanyName")

then built/created the forulma in the same cell again which showed  =COUNTIFS({Bundle LTE Cradlepoint Move Range 1}, "ConpanyName")   and the count appeared,

Any thoughts

Paul Newcome

Do you have a lot going on in the sheet? A lot of cross sheet references, cell linking, conditional formatting, complex formulas, multiple formulas feeding off of other formulas? 

 

Thinking back, I remember having this problem before too, and it was because I had so much going on at one time that the sheet couldn't keep up.

STEPHEN ALLEN

Paul: I was hoping that was not the case for this issue,  I am not the owner of the reference sheet but I know a high number of people have access to the same reference sheet and reports and other sheets gather data to/from the sheet.

 

So if I re-create the same countifs formula and get the count information, like I do, is there another way to refresh my sheet or something else I can do, other than re-creating the countifs formulas to get the counts to work? 

Paul Newcome

I actually haven't run into this issue in quite a while now. It was definitely before they switched over to using unique URL's for each sheet. Previously I would close the sheet, refresh my browser, then re-open the sheet. I have not run into it since the new update, but maybe try refreshing your browser page and/or following the listed steps above.

STEPHEN ALLEN

Many thanks for all your suggestions and input Paul.  I recreated the formulas over the weekend and so far, displaying the counts,  Fingers crossed they hold and I do not get the INVALD REF again

 

Paul Newcome

Happy to help! 

 

*Fingers Crossed*

wpoitras@cscsw.com

Hi Paul

I have this code giving issues. I believe it has to do with the dates

=COUNTIFS({Generic - Parent Row}, 0, {Generic - NA}, 0, {Generic - Status}, <>"Blue", {Generic - % Complete}, <>1, {Generic - Task Name}, <>" ", {Generic - Task State}, <>"On Hold", {Generic - Finish Date}, <$Date$1)

I get invalid ref I am looking for Start day delays and Past due dates. Possibility to connect?

Wayne

Paul Newcome

@wpoitras@cscsw.com I responded on your other post.